sqlite3.OperationalError:接近“?":python中的语法错误——使用“IN"运算符 [英] sqlite3.OperationalError: near "?": syntax error in python -- using 'IN' operator

问题描述

代码:

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')回溯(最近一次调用最后一次):...文件code.py"，第 1319 行，在验证中to_validate = db_query(q_select = 'ticket', q_table = 'my_table', q_where = 'issue_key', q_where_operator = 'IN', q_value = event_query_list)db_query 中的文件code.py"，第 1834 行rows = cursor.execute('SELECT %s FROM %s WHERE %s %s ?' % (q_select, q_table, q_where, q_where_operator), (q_value,)).fetchall()sqlite3.OperationalError:接近?":语法错误

当我在 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()回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中sqlite3.OperationalError:接近?":语法错误

更新 2:

尝试不用? DB-API的参数替换，还是一样的错误.

<预><代码>>>>t = ('APSEC-2261',)>>>cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN ?', t)回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中sqlite3.OperationalError:接近?":语法错误

更新 3:

为什么 IN 运算符被引用为 table_name?

<预><代码>>>>cursor.execute('SELECT ticket FROM my_table WHERE issue_key IN \'APSEC-2261\'')回溯(最近一次调用最后一次):文件<stdin>"，第 1 行，在 <module> 中sqlite3.OperationalError:没有这样的表:APSEC-2261

更新 4:

修复了奇怪的 table_name 问题.

<预><代码>>>>cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (\'APSEC-2261\')')<sqlite3.Cursor 对象在 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 对象在 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()

解决方案

问题是当使用 IN 运算符时，必须在括号中包含 ?，<代码>(?).

<预><代码>>>>t = ('APSEC-2261',)>>>cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', t)<sqlite3.Cursor 对象在 0x1723570>>>>cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (?)', t).fetchall()[(u'179708',)]>>># 展示如何根据多个值检查 IN....>>>t = ('APSEC-2261','APSEC-2262')>>>cursor.execute('SELECT remediation_ticket FROM remediation WHERE issue_key IN (%s)' % (('?, ' * len(t))[:-2]), t)<sqlite3.Cursor 对象在 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 = ()对于 i 在事件中:q_value_tuple += (i,)票= 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()

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:

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

Update:

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

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

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

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

Why is the IN operator being referenced as the 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:

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:

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.

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

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

解决方案

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

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

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:

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

这篇关于sqlite3.OperationalError:接近“?":python中的语法错误——使用“IN"运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！