2

Background: Not much documentation on MySQLdb Connector

Maybe I'm looking in the wrong places, but there's not much documentation about Python's MySQLdb family of connectors. Perhaps PEP249 is meant to do the job. Oracle's MySQL/Python connector seems to have much better docs, but at the moment I'm working with mysqlclient (the 3.x version of MySQLdb, which wraps around the C connector).

Named Parameters in MySQLdb: working for single values

After much searching, I stumbled upon beautiful syntax for binding named parameters, so long as they are a single value. For instance (made-up query to simplify the case):

query = """
        SELECT... 
        WHERE 
             name = %(name)s AND
             gender = %(gender)s
        """
parameters = {'name': name, 'gender': gender}
cursor.execute(query, parameters)

This properly escapes the parameters. Terrific.

Named Parameters in MySQLdb: how to use iterables?

Now I'd like to use a set, list or tuple to build queries with IN. Something like:

query = """
        SELECT... 
        WHERE 
        gender = %(gender)s AND
        name IN %(nameset)s
        """

I found a similar question here but that query doesn't use named parameters (the placeholder is named, but not the iterable).

What am I missing? Would someone know the magic syntax to make this work?

I see in the MySQLdb code that paramstyle is set to format rather than pyformat, but pyformat does work for single values.

To clarify,

  • I am not interested in an answer that just builds a string like ('sophie', 'jane', 'chloe') and concatenates it to the query. I need bound parameters to guarantee proper escaping.
  • I am also not interested in concatenating a join that uses db.escape_string(), although I may end up going that route if nothing else works.

What I'm really after is a clean idiom that binds named iterable parameters, if there is one.

Community
  • 1
  • 1
zx81
  • 41,100
  • 9
  • 89
  • 105

2 Answers2

0

Don't love answering my own question, but it's been a day...

Having looked inside the MySQLdb code, it looks like I won't get my wish. The quoting function will always add one set of quotes too many.

This is where I've ended up (the fallback option I had mentioned):

idset = ('Chloe', 'Zoe', "Noe';drip dautobus")

quoted_ids = [mdb.escape_string(identifier).decode('utf-8') for identifier in idset]
sql_idset = "('" + "', '".join(quoted_ids) + "')"

query = """
        SELECT ...
            FROM ...
            JOIN ...
            WHERE
              someid = %(someid)s AND
              namae IN """ + sql_idset

parameters = {'someid': someid}
cursor.execute(query, parameters)

Only one of the parameters is bound. The set in the IN clause is pre-quoted. Not my favorite thing to have to do, but at least each value is run through the MySQLdb quoting function in order to quote any potentially harmful stuff.

The decode is there because escape_string prepares a byte string, but the query being built with the bound parameter someid is a string. Maybe there is an easier way. If you find one, let me know.

zx81
  • 41,100
  • 9
  • 89
  • 105
0

I don't know how to express it specifically in MySQLdb but it just works out of the box with the MySQL connector library (version: 1.4.4, MySQL 5.7.x) as suggested in this other answer:

cursor.execute('SELECT * FROM test WHERE id in %(l)s', {'l': (1,2,3)})

If MySQLdb messes it up then I suggest acquiring direct cursor somehow.

Győző Papp
  • 148
  • 1
  • 5