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)

Answer 3

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself. The detect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.

As described before, SQLite supports only a limited set of types natively.

To use other Python types with SQLite, you must adapt them to one of the sqlite3 module’s supported types for SQLite: one of NoneType, int, float, str, bytes.

https://docs.python.org/3.6/library/sqlite3.html#using-adapters-to-store-additional-python-types-in-sqlite-databases