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..
- Worship at a Bus Stop - 2008
- The Weekend - 2007
- Perl and Python - 2007
- I'm back! - 2004
August 7th, 2007 at 22:39
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.
August 11th, 2007 at 21:38
[...] has been using Python with MySQL, beware it is MySQL as you know it but there is one more task you must commit [...]
March 6th, 2008 at 22:16
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.