"SELECT ... WHERE ... IN" with unknown number of parameters

Question

I'm attempting to perform a query in the form of...

SELECT col2 FROM tab WHERE col1 IN (val1, val2, val3...)

...where the values are stored in a Python list/tuple of arbitrary length. I can't seem to find a "clean" way to do it.

>>> db = connect(":memory:")
>>> db.execute("CREATE TABLE tab (col1 INTEGER, col2 TEXT)")
>>> db.execute("INSERT INTO tab VALUES(1,'one')")
>>> db.execute("INSERT INTO tab VALUES(2,'two')")
>>> db.execute("INSERT INTO tab VALUES(3,'three')")
>>> db.execute("INSERT INTO tab VALUES(4,'four')")
>>> db.execute("INSERT INTO tab VALUES(5,'five')")
>>> db.commit()

# Expected result
>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (1,3,4)").fetchall()
[(u'one',), (u'three',), (u'four',)]

>>> vals = (1,3,4)

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", vals).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (vals,)).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (?)", (','.join(str(val) for val in vals),)).fetchall()
[]

>>> 

Now I can do the following, which (I think... please correct me if I'm wrong) retains the security of the built-in parameter substitution, but it's still a bit ugly:

>>> db.execute("SELECT col2 FROM tab WHERE col1 IN (" + ",".join("?"*len(vals)) + ")", vals).fetchall()
[(u'one',), (u'three',), (u'four',)]
>>> 

Is that my best option, or is there a nicer way around this?

Answer 1

That's your best option without using additional libraries. I certainly have advocated just that technique in the past, more than once in fact.

You could also switch to using SQLAlchemy, which generates SQL for you, but that requires you to climb its learning curve and rewrite most of your application.

Answer 2

A simple and clean solution is:

vals = [1,3,4]
cursor.execute('SELECT col2 FROM tab WHERE col1 IN {}'.format(str(tuple(vals))