使用MySQLdb执行“SELECT ... WHERE ... IN ...”

Question

我在用Python执行一些SQL时遇到了问题，尽管在mysql命令行中相似的SQL可以正常工作。

这个表看起来是这样的：

mysql> SELECT * FROM foo;
+-------+-----+
| fooid | bar |
+-------+-----+
|     1 | A   | 
|     2 | B   | 
|     3 | C   | 
|     4 | D   | 
+-------+-----+
4 rows in set (0.00 sec)

我可以在mysql命令行中毫无问题地执行以下SQL查询：

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行：

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代码，以选择那些fooid，其中bar的值在('A','C')中？

顺便说一下，我注意到如果我把bar和fooid的位置调换，我可以成功选择那些bar，其中fooid的值在(1,3)中。我不明白为什么下面的查询可以工作，而上面的却不行。

sql='SELECT bar FROM foo WHERE fooid IN %s'
args=[[1,3]]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# (('A',), ('C',))

为了更清楚，这就是foo表是如何创建的：

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启用通用查询日志时，我看到

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周围加了太多的引号。

多亏了@Amber的评论，我更明白了哪里出错了。MySQLdb把参数['A','C']转换成了("'A'","'C'")。

有没有办法使用IN SQL语法来进行参数化查询？还是必须手动构建SQL字符串？

Answer 1

如果你的查询中有其他参数，除了IN列表之外，那么下面这个对JG回答的扩展可能会对你有帮助。

ids = [1, 5, 7, 213]
sql = "select * from person where type=%s and id in (%s)"
in_ids = ', '.join(map(lambda x: '%s', ids))
sql = sql % ('%s', in_ids)
params = []
params.append(type)
params.extend(ids)
cursor.execute(sql, tuple(params))

也就是说，把所有的参数放在一个线性数组中，然后把它作为一个元组传递给执行方法。

Answer 2

很遗憾，你需要手动构建查询参数，因为据我所知，没有内置的 bind 方法可以把一个 list 绑定到 IN 条件，就像 Hibernate 的 setParameterList() 一样。不过，你可以用下面的方法实现相同的效果：

Python 3:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(list(map(lambda x: '%s', args)))
sql = sql % in_p
cursor.execute(sql, args)

Python 2:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(map(lambda x: '%s', args))
sql = sql % in_p
cursor.execute(sql, args)

Answer 3

这里有一个类似的解决方案，我觉得在构建SQL中的%s字符串列表时更有效：

直接使用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注入的问题。

注意，数据（list_of_ids）是直接作为参数传给mysql的驱动程序的，而不是放在查询文本里，所以不会有注入的风险。你可以在字符串中保留任何字符，不需要去掉或加引号。