sqlite3。OperationalError:附近”?: python中的语法错误——使用“in”运算符

Code:

代码:

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

Result:

结果:

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

When I perform the exact query directly on the SQLite file in Firefox's SQLite Manager, I receive a proper response without an error:

当我直接在Firefox的SQLite管理器的SQLite文件上执行查询时，我收到了正确的响应，没有错误:

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

Update:

更新:

Trying without the %s substitutions, and still receiving the same error.

尝试不使用%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

Update 2:

更新2:

Trying without ? DB-API’s parameter substitution, still the same error.

在没有?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

Update 3:

更新3:

Why is the IN operator being referenced as the table_name?

为什么IN操作符被引用为table_name?

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

Update 4:

更新4:

Fixed the strange table_name issue.

修正了奇怪的表名问题。

>>> 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',)]

Update 5:

更新5:

Cannot write my own solution yet due to less than 100 reputation. The problem is when you use the IN operator, you must have the ? in parentheses.

我还不能写我自己的解决方案，因为我的名声不太好。问题是当你使用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',)]

Therefore, my db_query method must be modified to the following

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

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

2 个解决方案

#1

So, there are several issues. First off, as you discovered, the parentheses are part of the syntax of the IN clause. You must include them.

有几个问题。首先，正如您所发现的，括号是IN子句语法的一部分。你必须包括他们。

Secondly, your command will work fine as long as q_value contains a single value. But IN () is really for use with multiple, comma-separated values (for the simply case, you might as well use = instead of IN ()). If you try to pass a comma-separated list of values in the single parameter q_value, it won't work. SQLite will treat the entire comma-separated list as a single value to match against.

其次，只要q_value包含一个值，您的命令就可以正常工作。但是IN()实际上是用于多个逗号分隔的值(对于简单的情况，您最好使用=而不是IN()))。如果您试图在单个参数q_value中传递一个逗号分隔的值列表，那么它将不起作用。SQLite将整个逗号分隔的列表视为要匹配的单个值。

In this case, you must build a list of comma-separated question marks and insert that into your SQL with string formatting. Then, you must create a Python list of values, and pass that list to supply one value per question mark.

在这种情况下，必须构建一个逗号分隔的问号列表，并将其插入到SQL中，并使用字符串格式。然后，您必须创建一个值的Python列表，并将该列表传递给每个问号提供一个值。

#2

The problem is when one uses the IN operator, they must have the ? in parentheses, (?).

问题是当使用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',)]

My call to db_query has been modified to allow for multiple ?s of q_value:

我对db_query的调用被修改为允许多个q_value ?s:

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)

Also, my db_query method must be modified to the following:

另外，我的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()

#1