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
Get MySQL Cookbook, 2nd Edition 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.