Python and MySQLdb

Apologies for making two techy posts in one day, but I’ve just come across a problem which I think it might be good to post up in case it helps anyone else.

As I mentioned in the last post, I’ve been using MySQL-Python to connect to MySQL using … you guessed it, Python! Anyway. Long story short, I was trying to do an INSERT statement on a table which contains a DATE type. MySQLdb can parameterise database queries, so I thought this wouldn’t be a problem. What I was doing was something like the following:

cursor.execute("INSERT INTO table (date_column) VALUES (%s)", (date_val,));

Where date_val was a variable which contained a struct_time. Unfortunately, this didn’t seem to work: I kept getting the error message “Data truncated for column date_column' at row 1“.

Eventually I found out that in order to use certain data types in queries, you have to convert them to the relevant MySQLdb type. In this case, as follows:

mysqldb_date = MySQLdb.DateFromTicks(time.mktime(date_val))

[thanks to this page — at the bottom]

Once you’ve done that, you can use it in the query like before. Just a little ‘gotcha’ which I fell foul of, although it can be solved by reading the documentation it’s not crystal clear and I thought posting it here might help someone else 🙂

One other ‘gotcha’ with MySQLdb is that it disables auto-commit. In fact, I think the Python Database API Specification says that you should disable auto-commit, so it’s not the fault of MySQLdb — anyway, it just means that whenever you update data in the database, you have to call commit()! Not a problem, just something to watch out for if you find that your updates don’t seem to be hitting the database.


3 responses to “Python and MySQLdb”

  1. Yeah, good point that man. I have used sqlite with Python quite a bit and it took me a good part of an evening to figure out why my tables were not working before I found commit().

    I personally would rather have automatic commits, but maybe in a really large system, like Google or whatever, it might make a difference.

  2. […] has been using Python with MySQL, beware it is MySQL as you know it but there is one more task you must commit […]

  3. teichholtz avatar

    The commit() omission from many examples and other forums is surprising to me. I lost an entire work day trying to figure out why my deletes were not sticking.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related posts

Like this? Subscribe to my Substack.