Tuesday 23 October 2012

MySQLdb Gotcha!!

I pushed a relatively minor upgrade to my openmolar program today to find it broken on the debian squeeze boxes I use in the surgeries here.

"What happened?", you may ask.

Consider this (python) code.
import MySQLdb
db = MySQLdb.connect(db="openmolar", user="academy", passwd="XXXXXX")
cursor = db.cursor()

query = '''INSERT INTO notes (ndate, serialno, note)
           VALUES (DATE(NOW()), %s, %s)'''

values = ((1, "hello"), (1, "world"))

cursor.executemany(query, values)
this code works with python-mysqldb 1.2.3 (on debian wheezy), but not python-mysqldb 1.2.2 (debian squeeze). the error I get is
TypeError: not all arguments converted during string formatting
it seems that being able to include MySQL functions (ie. the DATE(NOW()) bit) in the values tuple is only recently supported. what is surprising, however, is that this can be done with single executions.. eg.
cursor.execute(query, (1, "hello"))
Obviously, this is not consistent behaviour, and has been fixed in the current release, but it does demonstrate a hazard for developers who target various gnu/linux distros. (yes, I know I should have a test suite... ) FOLLOW-UP - I looked into the python-mysqldb sources, and found the culprit to be an improved regex lookup introduced in cursors.py

No comments: