sqlite3.OperationalError: 附近"?": 语法错误，使用'IN'运算符

Question

代码：

print 'SELECT %s FROM %s WHERE %s %s %s' % (q_select, q_table, q_where, q_where_operator, q_value)
rows = cursor.execute('SELECT %s FROM %s WHERE %s %s ?' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall()

结果：

SELECT ticket FROM my_table WHERE issue_key IN ('APSEC-2261')
Traceback (most recent call last):
  ...
  File "code.py", line 1319, in validate
    to_validate = db_query(q_select = 'ticket', q_table = 'my_table', q_where = 'issue_key', q_where_operator = 'IN', q_value = incident_query_list)
  File "code.py", line 1834, in db_query
    rows = cursor.execute('SELECT %s FROM %s WHERE %s %s ?' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall()
sqlite3.OperationalError: near "?": syntax error

当我在Firefox的SQLite管理器中直接对SQLite文件执行相同的查询时，我得到了正确的响应，没有错误：

SELECT ticket FROM my_table WHERE issue_key IN ('APSEC-2261')
179908

更新：

尝试不使用 %s 替换，仍然收到相同的错误。

>>> test = cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN ?', ('APSEC-2261',)).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

更新 2：

尝试不使用 ? 进行数据库API的参数替换，还是同样的错误。

>>> t = ('APSEC-2261',)
>>> cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN ?', t)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

更新 3：

为什么 IN 操作符被引用为表名？

>>> cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN \'APSEC-2261\'') 
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: APSEC-2261

更新 4：

解决了奇怪的表名问题。

>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (\'APSEC-2261\')')
<sqlite3.Cursor object at 0x1723570>
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (\'APSEC-2261\')').fetchall()
[(u'179708',)]

更新 5：

由于声望不足100，暂时无法写出自己的解决方案。问题在于，当你使用 IN 操作符时，必须在括号中使用 ?。

>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', ('APSEC-2261',))
<sqlite3.Cursor object at 0x1723570>
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', ('APSEC-2261',)).fetchall()
[(u'179708',)]

因此，我的 db_query 方法必须修改为以下内容

rows = cursor.execute('SELECT %s FROM %s WHERE %s %s (?)' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall()

Answer 1

这里有几个问题。首先，正如你发现的，括号是IN语句的一部分，必须要加上。

其次，只要q_value里面有一个值，你的命令就能正常工作。但是IN ()其实是用来处理多个用逗号分隔的值的（如果只是一个值，你可以直接用=，不需要用IN ()）。如果你试图把一个用逗号分隔的值列表放到q_value这个单一参数里，它是行不通的。SQLite会把整个逗号分隔的列表当作一个单一的值来匹配。

在这种情况下，你需要构建一个用逗号分隔的问号列表，并通过字符串格式化把它插入到你的SQL语句中。然后，你还需要创建一个Python的值列表，把这个列表传进去，以便每个问号对应一个值。

Answer 2

问题在于，当使用 IN 操作符时，必须把 ? 放在括号里，也就是 (?)。

>>> t = ('APSEC-2261',)
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', t)
<sqlite3.Cursor object at 0x1723570>
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', t).fetchall()
[(u'179708',)]
>>> # Show off how to check IN against multiple values.
...
>>> t = ('APSEC-2261','APSEC-2262')
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (%s)' % (('?, ' * len(t))[:-2]), t)
<sqlite3.Cursor object at 0x1723570>
>>> cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (%s)' % (('?, ' * len(t))[:-2]), t).fetchall()
[(u'179708',), (u'180208',), (u'180240',), (u'180245',), (u'180248',), (u'180334',), (u'180341',), (u'180365',), (u'180375',)]

我对 db_query 的调用已经修改，以便可以使用多个 ? 来表示 q_value：

q_value_tuple = ()
for i in incidents:
    q_value_tuple += (i,)
tickets = db_query(q_select = 'remediation_ticket', q_table = 'remediation', q_where = 'issue_key', q_where_operator = 'IN', q_value = q_value_tuple)

另外，我的 db_query 方法也必须修改成下面这样：

rows = cursor.execute('SELECT %s FROM %s WHERE %s %s (%s)' % (q_select, q_table, q_where, q_where_operator, ('?, ' * len(q_value))[:-2]), q_value).fetchall()