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 bug to 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
  2.  
  3. import MySQLdb
  4. import MySQLdb.converters
  5. from xmlrpclib import DateTime
  6.  
  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)
  13.  
  14. # get the conversion table
  15. conv_dict = MySQLdb.converters.conversions
  16. conv_dict[DateTime] = DateTime2string
  17.  
  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!

Creative Commons Attribution-ShareAlike

Comments

#1 Anonymous Coward

Thanks. This was quite useful in helping me understand how MySQLdb handles type conversions when inserting or updating values into the db.

#2 Anonymous Coward2

Can this be used to change the conversion from NULL to None, into something line NULL to emptystring ?

#3 Sander Marechal (http://www.jejik.com)

Yes, I believe so.

#4 Anonymous Coward3

Of course, doing it the other way round is impossible. For example, it's possible to automatically convert uuid.UUID's to a varchar, but it's impossible to convert a varchar automatically back into a uuid.UUID.

#5 Sander Marechal (http://www.jejik.com)

@Anonymous Coward3: It's not impossible. Just override the reverse converter for VARCHAR to recognise the UUID format and return an UUID instance whet it matches.

Comments have been retired for this article.