select from sqlite table where rowid in list using python sqlite3 — DB-API 2.0

Question

The following works:

>>> cursor.execute("select * from sqlitetable where rowid in (2,3);")

The following doesn't:

>>> cursor.execute("select * from sqlitetable where rowid in (?) ", [[2,3]] )
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Is there a way to pass in a python list without having to format it into a string first ?

Answer 1

Unfortunately not. Each value must be given its own parameter mark (?). Since the argument list can (presumably) have arbitrary length, you must use string formating to build the correct number of parameter marks. Happily, that isn't so hard:

args=[2,3]
sql="select * from sqlitetable where rowid in ({seq})".format(
    seq=','.join(['?']*len(args)))

cursor.execute(sql, args)

Answer 2

In Python 3.6 you can also build queries with the f strings:

args=[2, 3]
query = f"SELECT * FROM sqlitetable WHERE rowid in ({','.join(['?']*len(args))})"
cursor.execute(query, args)