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( "", 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))


2 Comments on “Using a List as a Named Placeholder in Python-sqlite”

  1. code43 says:

    For a more generalized solution to your problem, check out this python module:
    where a subquery can be easy customized.

    The module is instructive in the way it unifies the standard batteries: sqlite3 (as of Python v2.5), zlib (for compression), and cPickle (for securely serializing objects).

    If your Python program requires data persistance, then y_serial is a module which should be worth importing. Objects are warehoused in a database file in very compressed form. Steps for insertion, organization by annotation, and finally retrieval are amazingly simple.

    Hope this is helpful…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s