How to enable transactions with Mysql and Python

Sometimes we may require updating several rows of data with different queries and make sure every query execute correctly to keep the database consistency. Here is a simple solution to enable such transactions with Mysql and Python. To do this, we can start a transaction and commit the changes to the table. The data will be written to the table only after the commit statement is executed. If there is an error in between, we can rollback, so that all the inconsistencies are cleared.

To enable transactions, make sure you have set Innodb as the database engine for all the tables you require to support the transaction. The default MyIsam engine will not support transactions. You can do this by altering the required table with the SQL:

ALTER TABLE  ENGINE = InnoDB;

In python, with MySQLdb driver, you can turn off the autocommit and use rollback or commit to the connection.
Example:

import MySQLdb
#Start a connection
db= MySQLdb.connect(host="dbhost", user="dbuser" , passwd="dbpass", db="dbname")
db.autocommit(False)
cursor = db.cursor()
try:
 cursor.execute("Your SQL")
 cursor.execute("Another sql")
 db.commit()
except:
 db.rollback()