As part of my master’s project, I’m implementing a very simple monitoring system. Logs are continuously stored in a mySQL database, and one can get an overview of the system by viewing a website. This means one can monitor the system wherever you are, and there are no software costs. Important for my sponsors.

My database needs to get data from a serial connection, and having successfully established a connection from Python in a previous project, I figured I’d stick with this, meaning the only difficulty would be communicating with the mySQL database from python. A bit of research revealed this should be quite simple, as there’s a very convenient MySQLdb library to aid communication.

FYI: I’m running Windows 7 64bit. Python 2.7.3 32bit. MySQLdb-python 1.2.3.

A little while later I had it all up and running and successfully executed the test examples with a simple:

cur.execute(“SELECT VERSION()”)

This revealed I was running

Database version : 5.5.24-log

All fine and dandy. I then setup a simple Insert query, which although it didn’t result in any error messages, failed to successfully update the database. Executing the exact same query from phpmyadmin resulted in the record appearing in the database. This was several months ago and I struggled for a few hours each day, making small changes to the code which might possibly affect the way it’s executed. I also shifted through pages of appropriate Google searches, all to no avail.

I gave up on the problem for a while, and today decided to give it another go. I tried it on a different computer, installing everything from scratch. Yet still I encountered the same problem. After some more time spent sifting through stackoverflow problem pages I came across someone with a problem kinda like mine. The solution to the problem was that the database was set to not auto commit exectued queries. Fine, I check mySQL, but autocommit = 1, so I assume that’s not the problem. Sifting through a few more pages and someone links to an FAQ mentioning the same commit problem. I add the line

connection.commit()

to my python code, and voilà, it works. Hours of frustration, hidden away in an FAQ.