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

Larry Lustig · Accepted Answer · 2011-09-16 20:10:17Z

3

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.

answered Sep 16, 2011 at 20:10

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

paragbaxi Over a year ago

Larry, thanks! I have modified my answer to allow for multiple q_values.

paragbaxi Over a year ago

To be exact, I found that I had to create a Python tuple of values, and not a list.

Larry Lustig Over a year ago

I beg your pardon. I had remembered a list.

paragbaxi · Accepted Answer · 2011-09-16 21:00:46Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related