Binding a list of strings to an IN clause

Question

Possible Duplicate:
python list in sql query as parameter

Consider this (using apsw here):

s = ["A", "B", "C"]
c.execute("SELECT foo.y FROM foo WHERE foo.x in (?)", (s, ))

This doesn't work, because a binding parameter cannot be a list. I want to bind a list of strings to ?. I know how to build the appropriate query-string manually, but I wonder if there is a way to do this with bindings.

Maybe http://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter will help. — gimel, Jul 09 '10 at 12:44
@gimel: Thank you, I didn't see that one. @KennyTM: That raises `too many bindings`. — Björn Pollex, Jul 09 '10 at 12:48
You'll need the correct number of ? in your query string, as many as elements in the list. — Mad Scientist, Jul 09 '10 at 12:49

score 1 · Answer 1 · answered Jul 10 '10 at 13:08

1

Going with the multiple question marks idea by Fabian, how about

c.execute("SELECT foo.y FROM foo WHERE foo.x in (%s)" % ', '.join('?' * len(s)), s)

answered Jul 10 '10 at 13:08

Marius Gedminas

10,676
3
38
38

I should've checked gimel's link before answering. It's the same answer. – Marius Gedminas Jul 10 '10 at 13:10

score 0 · Answer 2 · answered Jul 09 '10 at 12:41

I had this problem around 4 years ago, and then I found out it was impossible to bind lists to sql (i was using mssql server and ODBC provider, but also considered direct sql calls)
In my case i was just building the queries manually and it was efficient. In case you have a very long list of values you will have to create another table, populate it in runtime and join with it in your sql.

Binding a list of strings to an IN clause

2 Answers2