执行“SELECT ... WHERE ... IN ..."使用 MySQL 数据库 [英] Executing "SELECT ... WHERE ... IN ..." using MySQLdb
问题描述
我在 Python 中执行某些 SQL 时遇到问题，尽管类似的 SQL 在 mysql 命令行中运行良好.
I'm having a problem executing some SQL from within Python, despite similar SQL working fine from the mysql command-line.
表格如下所示:
mysql> SELECT * FROM foo;
+-------+-----+
| fooid | bar |
+-------+-----+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+-------+-----+
4 rows in set (0.00 sec)
我可以从 mysql 命令行执行以下 SQL 查询，没有任何问题:
I can execute the following SQL query from the mysql command-line, without a problem:
mysql> SELECT fooid FROM foo WHERE bar IN ('A','C');
SELECT fooid FROM foo WHERE bar IN ('A','C');
+-------+
| fooid |
+-------+
| 1 |
| 3 |
+-------+
2 rows in set (0.00 sec)
但是，当我尝试在 Python 中执行相同操作时，我没有得到任何行，而我期望得到 2 行:
However, when I try to do the same from within Python, I get no rows, while I expected 2 rows:
import MySQLdb
import config
connection=MySQLdb.connect(
host=config.HOST,user=config.USER,passwd=config.PASS,db='test')
cursor=connection.cursor()
sql='SELECT fooid FROM foo WHERE bar IN %s'
args=[['A','C']]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# ()
所以问题是:应该如何修改python代码以选择bar在('A','C')中的那些?fooids
So the question is: how should the python code be modified to select those fooids where bar is in ('A','C')?
顺便说一句，我注意到如果我切换bar和fooid的角色，我可以得到代码来选择那些bars 其中 fooid 在 (1,3) 中成功.我不明白为什么一个这样的查询(下面)有效，而另一个(上面)却没有.
By the way, I noticed that if I switch the roles of bar and fooid, I can get the code to select those bars where fooid is in (1,3) successfully. I don't understand why one such query (below) works, while the other one (above) doesn't.
sql='SELECT bar FROM foo WHERE fooid IN %s'
args=[[1,3]]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# (('A',), ('C',))
为了绝对清楚，这就是 foo 表的创建方式:
And just to be absolutely clear, this is how the foo table was created:
mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `foo` (
`fooid` int(11) NOT NULL AUTO_INCREMENT,
`bar` varchar(10) NOT NULL,
PRIMARY KEY (`fooid`));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT into foo (bar) values ('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
<小时>
编辑:当我使用 mysqld -l/tmp/myquery.log 启用一般查询日志时看到了
Edit: When I enable the general query log with mysqld -l /tmp/myquery.log
I see
mysqld, Version: 5.1.37-1ubuntu5.5-log ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
110101 11:45:41 1 Connect unutbu@localhost on test
1 Query set autocommit=0
1 Query SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'")
1 Query SELECT bar FROM foo WHERE fooid IN ('1', '3')
1 Quit
确实，A 和 C 周围的引号似乎太多了.
Indeed, it looks like too many quotes are being placed around A and C.
感谢@Amber 的评论，我更了解出了什么问题.MySQLdb 将参数化的参数 ['A','C'] 转换为 ("'A'","'C'").
Thanks to @Amber's comment, I understand better what is going wrong. MySQLdb converts the parametrized argument ['A','C'] to ("'A'","'C'").
有没有办法使用 IN SQL 语法进行参数化查询?还是必须手动构造 SQL 字符串?
Is there a way to make a parametrized query using the IN SQL syntax? Or must one manually construct the SQL string?
推荐答案
这里有一个类似的解决方案 我认为在 SQL 中构建 %s 字符串列表更有效:
Here is a similar solution which I think is more efficient in building up the list of %s strings in the SQL:
直接使用list_of_ids:
format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
tuple(list_of_ids))
这样你就不用自己引用了，也避免了各种sql注入.
That way you avoid having to quote yourself, and avoid all kinds of sql injection.
请注意，数据 (list_of_ids) 作为参数(不在查询文本中)直接进入 mysql 的驱动程序，因此没有注入.您可以在字符串中保留您想要的任何字符，无需删除或引用字符.
Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.
这篇关于执行“SELECT ... WHERE ... IN ..."使用 MySQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
