Python and MySQL autocommit

Solution:

If your MySQL database is using the InnoDB engine, commit your changes after database transactions:
cursor.connection.commit()

Or just turn on autocommit to automatically commit after every database transaction:
cursor.connection.autocommit(True)

Details:

So I was using python's MySQLdb module to edit a mysql database, and I noticed that even though python was telling me my modifications were taking place, I wasn't seeing any changes. in addition, when I would log onto the database from something other than python and try to make changes, I would get this error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Well apparently, the mysqldb module now disables mysql autocommit by default. so now, when I'm done with my transactions to the database, I need to commit my changes by calling the commit() function of the connection object. the connection object is what's returned when you call the MySQLdb.connect() function. I haven't really been using that object in my code other than to create the cursor object, which is what I normally use:

db = MySQLdb.connect(...)
cursor = db.cursor()

But the cursor can access the connection object, which I can then use to commit the changes:

cursor.connection.commit()

The strange thing with all of this is that I just started noticing this problem, even though according to the MySQLdb module authors, this functionality (disabling autocommit by default) has been in place since version 1.2.0 of the module. but when I look at the package versions of python-mysqldb for Ubuntu (what I'm currently using and have been for a while), it looks like it's been past version 1.2.0 for the last several years:
http://packages.ubuntu.com/search?keywords=python-mysqldb&searchon=names&suite=all&section=all

Maybe this "feature" has just recently made it into Ubuntu's package for this module. or maybe I'm missing something else here. at any rate, at least I know what's going on.

You can see here for more information:
http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away

Edit:
Okay, so apparently what happened is in my other code where I was modifying mysql databases, they used the default engine (MyISAM). the database I was having problems with was using the InnoDB engine, which is a transactional storage engine. this explains why I just now saw this issue.

More information here:
http://stackoverflow.com/questions/1617637/pythons-mysqldb-not-getting-updated-row

As well as an alternate solution: instead of committing after every transaction, I can turn autocommit on when I'm working with databases using the InnoDB engine, so they'll function just like the rest:

cursor.connection.autocommit(True)