Using a List as a Named Placeholder in Python-sqlitePosted: February 11, 2009
Sometimes as part of a sqlite query, I want to use a list or other iterable as an argument. Most commonly, this is useful for “IN” queries. The sqlite (or possibly the python-sqlite wrapper), can’t properly interpret this sort of named place holder (cf. using lists as sqlite arguments).
SELECT * FROM table WHERE id IN (:arglist)
This won’t work in sqlite. A workaround is given below. Use the function “sql_in_to_org” change this construct to from an “IN” to an “OR”, and get a dict of the arguments.
def sql_in_to_or(sqlvar, args, name="arg" ): ''' convert: sqlvar IN (args) to: sqlvar=:arg0 OR sqlvar=:arg1 .... returns "new query", dict of args To use in execute: Q = 'select * from table where (%(where)s) and time > :ts' iq, iq_args = sql_in_to_or( "table.id", range(5), name='id') placeholders = dict(ts=12323425) conn.execute(Q % dict(where=iq), placeholders.update(iq_args)) ''' q_parts = list() q_args = dict() for (ii, arg) in enumerate(args): argname = "%s%i" % (name, ii) q_parts.append( "%s=:%s" % (sqlvar,argname) ) q_args[argname] = arg query = " OR ".join( q_parts ) return (query, q_args)