Recipe 15.7. 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