将参数传递给DB.execute以用于WHERE IN... INT列表

Question

在Python的数据库API规范中，你可以给execute()方法传递参数。我的SQL语句中有一个WHERE IN子句，我一直用一个元组来填充这个IN部分。例如：

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

但是当我遇到只有一个元素的元组时，执行就会失败。

编程错误：错误：在“)”附近的语法错误
第13行： WHERE id IN (3,)

我该如何让这个元组在子句中正常工作呢？

Answer 1

这可能不是你问的那个问题的直接答案，但我觉得它可能能解决你遇到的问题。

Python的数据库API似乎没有提供一种安全地传递元组作为参数的方法。bernie给出的答案是使用Python的%运算符进行替换，但这种方法不太安全。

不过，你可能不需要将元组作为参数传递，特别是当你想要的元组是另一个SQL查询的结果时（就像你对Daniel提到的那样）。相反，你可以使用SQL子查询。

举个例子，如果你想在IN子句中使用的ID集合是通过SELECT id FROM other_table WHERE use=true查询得到的：

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE use=true)"
db.execute(stmt)

而且这也可以用参数化的方式（安全的方法）来处理。如果你想选择的ID是具有特定parent_id的那些：

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE parent_id=%s)"
params = (parent_id,)
db.execute(stmt, params)

Answer 2

这个错误是因为在数字3后面多了一个逗号。对于单个数值来说，把这个逗号去掉就可以了。

params = ((3), ... )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

Answer 3

编辑：如果你认为这个回答绕过了内置的SQL注入攻击保护，那你就错了；请仔细看看。

使用pg8000进行测试（这是一个与PostgreSQL数据库引擎兼容的纯Python接口，符合DB-API 2.0标准）：

这是传递多个参数给“IN”条件的推荐方法。

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

完整示例：

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))