Automagically convert Python objects to MySQL

in http://www.jejik.com/articles/2007/04/automagically_convert_python_objects_to_mysql/

Automagically convert Python objects to MySQL

by Sander Marechal

I recently ran into an annoying little snag when using MySQL. Apparently MySQL cannot handle ISO 8601 datetime values such as used by XML-RPC that use the format YYYYMMDDTHH:MM:SS. MySQL will simply convert such values to 0000-00-00 00:00:00 with a warning. I have reported this bugto MySQL but I was still faced with the unpleasant prospect of converting all my XML-RPC dateTime values to something MySQL could understand.

I searched for a way to do this automatically and I came across a way tucked inside the MySQLdb Python module. MySQLdb contains a map of conversion functions and datatypes between MySQL's column types and Python's datatypes. It is possible to extend this list and pass it to the connect() method and have any datatype supported that you want. If you don't care for the details and just want to see the code, just skip to the end.

In MySQLdb.converters.py you can find a map called conversions that simply maps Python datatypes to a converter function and MySQL column types to Python types. Type conversion happens with the use of this map whenever you qyery the database for results or whenever you insert Python variables in SQL queries using the DB API's format. If a conversion for a Python type to a MySQL type cannot be found, MySQLdb simply tries to call that object's __str__() method and tries to use that result instead.

Now you can see where my problem came in: the DateTime type used in xmlrpclib is not in MySQLdb's conversion map, and the DateTime's __str__()method returned a format of YYYYMMDDTHH:MM:SS.

To convert your own datatype automatically to something MySQL can handle, you need to define a function that will accept as arguments the object you want to convert and a dictionary. You don't need the dictionary usually but it's a required argument nonetheless. And here's the code that shows how I did this for xmlrpclib's DateTime:

1. #!/usr/bin/env python
3. import MySQLdb
4. import MySQLdb.converters
5. from xmlrpclib import DateTime
7. # define the DateTime conversion
8. def DateTime2string(object, dict):
9.     iso = object.__str__()
10.     sql = iso[0:4] + '-' + iso[4:6] + '-' + iso[6:]
11.     # properly escape the value when returning
12.     return MySQLdb.converters.Thing2Literal(sql, dict)
14. # get the conversion table
15. conv_dict = MySQLdb.converters.conversions
16. conv_dict[DateTime] = DateTime2string
18. # connect to the server with the new conversion table
19. db = MySQLdb.connect(
20.     host='localhost',
21.     user='username',
22.     passwd='password',
23.     db='database',
24.     conv=conv_dict
25. )

And that's all there is to it! You can convert any kind of object to a proper MySQL string representation this way. Happy coding!