Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

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

MySQLdb-0.9.2
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2003-11-07
Matthew Bogosian
No

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).

Discussion

  • Andy Dustman
    Andy Dustman
    2003-11-16

    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
    Andy Dustman
    2003-12-13

    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.