MySQLdb gives me a pain

Consider the following Python code:

import MySQLdb
conn = MySQLdb.connect(...)
cursor = conn.cursor()
sql = "create temporary table t1 (a datetime, b integer)"
cursor.execute(sql)
sql = "insert into t1 (a,b) values (ifnull(%s,now()),%s)"
args = [(None,0),]
cursor.executemany(sql, args)
cursor.close()

If you run it against MySQLdb 1.2.4, you get this error:

TypeError: not all arguments converted during string formatting

…even though there’s nothing wrong with the insert query. Any of the following changes will make the error go away:

  • Replace now() with ‘2013-01-01’;
  • Reverse the field order, e.g.: insert into t1 (b,a) …;
  • Use an earlier version of MySQLdb (I tried 1.2.1b4).

I found a comment on the ever-helpful StackOverflow that says this is a bug in MySQLdb  (unfixed, despite the developer’s claims to the contrary), and even offers the specific regex that’s broken.

(I’d say using a regex to parse sql, when all you really need to do is find & replace instances of “%s”, is the real breakage….)

The end result of all this is that I get to waste my time looking at every single instance of executemany in my code, to make sure my queries won’t confuse the easily-bewildered MySQLdb.

One thought on “MySQLdb gives me a pain

  1. Pat Post author

    Fortunately, I’ve been fairly restrained in my use of executemany, and only one query needed reworking.

Comments are closed.