sqlite3.OperationalError: near "?": syntax error in python -- using 'IN' operator

Question

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

Solution 1

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

Solution 2

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.

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.

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.