Sqlite statement syntax error

Question

What's wrong with this SQL statement? I'm getting a SQLError: near "?": syntax error.

'select all counts from table as table where offset in ?'

The ? has a binding of numbers with a list in it: (1,2,4).

• Varriount: Looking at the way the '' are formatted, I'm guessing this is in python.
• Paul Sonier: Are you sure your binding is getting bound?

Answer 1

Just guessing that the language you're using is Python...
No matter the language the principle is the same:
You need to dynamically create the appropriate number of placeholders.

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('create table test (id int)')
<sqlite3.Cursor object at 0x011A96A0>
>>> c.executemany('insert into test values (?)', [(1,),(2,),(4,)])
<sqlite3.Cursor object at 0x011A96A0>
>>> ids = (1,2,4)
>>> query = 'select * from test where id in (%s)' % ','.join('?'*len(ids))
>>> query
'select * from test where id in (?,?,?)'
>>> c.execute(query, ids).fetchall()
[(1,), (2,), (4,)]

Answer 2

i think you want 'select count(*) from table where offset in ?'

Answer 3

Can you bind an in-list to a single parameter placeholder like that? You might consider this alternative: create a temporary table, insert the in-list values into the temp table, and then do an inner join between your table and the temp table on the relevant column. Overall, cleaner and more maintainable than building the query statement string with the (?,?,?,?) substring having the requisite number of question marks.