Using a List as a Named Placeholder in Python-sqlite

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).

For example:

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)

(Thanks to Jon Nelson of the Pycurious Blog for the idea (we work together at Renesys))