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

Andy Dustman
MySQLdb (285)
Matthew 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
    Andy Dustman

    Logged In: YES

    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
    Andy Dustman

    Logged In: YES

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

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

    db.literal ensures the the proper quoting is applied.

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