sqlite3.operational错误：靠近“？”：python中的语法错误--使用“in”op

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:

在没有?DB-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_查询方法必须修改为

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_值包含一个值，您的命令就可以正常工作。但是IN ()实际上是用于多个逗号分隔的值（对于简单的情况，您最好使用=，而不是IN ()）。如果试图在单个参数q_值中传递逗号分隔的值列表，它将不起作用。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',)]

我对数据库查询的调用已被修改为允许多个q值的?：

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)

此外，我的数据库查询方法必须修改为以下内容：

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()