MySQL Cookbook, 2nd Edition

Using Transactions in Python Programs

Problem

You want to perform a transaction in a DB-API script.

Solution

Use the standard DB-API transaction support mechanism.

Discussion

The Python DB-API abstraction provides transaction processing control through connection object methods. The DB-API specification indicates that database connections should begin with auto-commit mode disabled. Therefore, when you open a connection to the database server, MySQLdb disables auto-commit mode, which implicitly begins a transaction. End each transaction with either commit() or rollback(). The commit() call occurs within a try statement, and the rollback() occurs within the except clause to cancel the transaction if an error occurs:

try:
  cursor = conn.cursor ()
  # move some money from one person to the other
  cursor.execute ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")
  cursor.execute ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")
  cursor.close ()
  conn.commit()
except MySQLdb.Error, e:
  print "Transaction failed, rolling back. Error was:"
  print e.args
  try:  # empty exception handler in case rollback fails
    conn.rollback ()
  except:
    pass