#71 can't submit sequence as value for query variable

[Matt Bogosian]

I'd like to do the following:

query = 'SELECT * FROM my_table WHERE id IN %(id_list)s'
vars = { 'id_list': [1, 2, 3] }
curs.execute(query, vars)

and have it resolve as the following query:

SELECT * FROM my_table WHERE id IN (1, 2, 3)

However, there are two problems:
1.) there doesn't seem to be a decent % code to
represent a sequence
2.) cursor.execute() turns everything into a string, so
what actually ends up being created is:

SELECT * FROM my_table WHERE id IN '[1, 2, 3]'

Am I missing something? Is there a real way to do this,
or do I have to hack together something like:

query = 'SELECT * FROM my_table WHERE id IN
(%(id_list[0])s, %(id_list[1])s, %(id_list[2])s)'
vars = { 'id_list[0]': 1, 'id_list[1]': 2,
'id_list[2]': 3 }

This seems kind of dumb, since I would have to
dynamically generate the value for query depending on
the number of items in my sequence (which kind of
defeats the purpose of having a query constant and just
submitting the variables).

[Andy Dustman]

Logged In: YES
user_id=71372

Always use %s for the placeholder.

In this case, you may have to directly substitute in your
parameters using % so that you can avoid SQL quoting
mechanisms. Converting to a tuple might simplify matters.

[Andy Dustman]

Logged In: YES
user_id=71372

There's not a realistic way of doing this with the DB API's
db.execute() method. Basically you need to do something like
this:

vars = [1, 2, 3]
query = 'SELECT * FROM my_table WHERE id in (%s)' % \ ','.join(map(db.literal, vars))
curs.execute(query)

db.literal ensures the the proper quoting is applied.

Again, this is not just a MySQLdb problem, but a limitation
of DB API.