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. Invoke begin( ) to begin a transaction and either commit( ) or rollback( ) to end it. The begin( ) and commit( ) calls go into a try block, and the rollback( ) goes into the corresponding except block to cancel the transaction if an error occurs:

try:
    conn.begin ( )
    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

Get MySQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.