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 ?
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)
args should always be a list or tuple of arguments. The arguments may be strings, but args itself never.
IN for a list?
SQLITE_LIMIT_VARIABLE_NUMBER limit (sqlite.org/c3ref/…) which is by default set to 999. I.e. you cannot have more than 999 ? in the query
dbcon.getlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER). I seem to get 250k by default, which matches the value coming back from the PRAGMAIn 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)
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.
I needed to include more than one IN clause with a query along with other named parameters and came up with a helper method that would let me do:
params = {'mn': 5, 'mx': 15}
in_a = sql_in([1,2,3], params, prefix='a')
in_b = sql_in([5,6,7], params, prefix='b')
query = (
"SELECT rowid, name FROM tbl "
"WHERE value BETWEEN :mn AND :mx"
f" AND (alpha IN {in_a} OR beta IN {in_b})"
)
dbcon.execute(query, params)
Which uses this helper method:
def sql_in(values, params, *, prefix='in'):
"""Generate an IN clause for SQLite using named placeholders.
Given values=[1,2,3], will return:
'(:in0,:in1,:in2)'
after doing:
params.update({'in0':1,'in1':2,'in2':3})
If you're using this multiple times with a single dbcon.execute(),
you need to give each one a distinct prefix, e.g.:
params = {}
in_a = sql_in([1,2,3], params, prefix='a')
in_b = sql_in([5,6,7], params, prefix='b')
dbcon.execute(f'SELECT * FROM tbl WHERE a IN {in_a} OR b IN {in_b}', params)
"""
def inner():
yield '('
delim = ':'
for i, val in enumerate(values):
key = f'{prefix}{i}'
assert key not in params
params[key] = val
yield delim
yield key
delim = ',:'
yield ')'
return ''.join(inner())
