phillsacre.me.uk

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.

On this day..

• Catching Up - 2010
• Worship at a Bus Stop - 2008
• The Weekend - 2007
• Perl and Python - 2007
• I'm back! - 2004