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.
Leave a Reply