Sunday, January 25, 2009

Python & MySQL

I have been doing some work with Python & MySQL lately and had need to use the executemany feature. This is used to batch SQL commands like for an insert.

The queries that I have were using named parameters for convenience, and I was passing a list of dictionaries to the executemany function. I expected this to work fine, as it does for other drivers (like sqlite3). However in version 1.2.2.final.0 I got the following error.

incomplete format error on executemany with mapped variables

It appears that this is a bug in the MySQL Python driver. This is a link to the bug note.

Fortunately there is a patch that can be implemented. You just have to edit one of the Python files in the driver and update the regular expression used to parse the query for the named parameters.

One additional note. If you decide to recompile and are using Mac OS Leopard, then you will need to make a couple of additional modifications. Here are the steps that I took, based on the post here.

1.) Download the x86 TAR file of MySQL from their website. (Only if you do not have it already)
2.) Extract the TAR file somewhere. (You do not need to install MySQL if you do not want to)
3.) Download the MySQLdb Driver file and extract it.
3.) If you do not install MySQL, in the MySQLdb driver edit the setup_posix.py file. Look for line 26, it should be like this mysql_config.path = . Where that is pointing to the locaton of the mysql_config script. Set this to the full path of that file, it should be in the bin directory of the MySQL files that you extracted.
4.) Then edit the file _mysql.c (as in the linked post). Deleting the following lines.

#ifndef uint
#define uint unsigned int
#endif

5.) Run the normal 'python setup.py build'. This should work without error. Make sure you delete the build and / or dist directories if they currently exist. This will cause a fresh build.
6.) Then run the normal 'python setup.py install'. This will install everything.
7.) At this point there is one last thing that needs to be done. Copy the file libmysqlclient_r.16.dylib from the lib directory of MySQL to the /usr/local/mysql/lib directory. I had to do this because there was currently a different version (libmysqlclient_r.15.dylib) of the file in that directory.

No comments: