Python-mysql: when to explicitly rollback a transaction

Question

Suppose, I have a modifying statement:

cursor = conn.cursor()
# some code
affected_rows1 = cursor.execute(update_statement1, params1)
# some code
conn.commit()
cursor.close()

Should I wrap the block of code with a try ... except and explicitly rollback a transaction when an exception is raised, and which MySQLdb exceptions should I catch to rollback? I used to catch any StandardError in this case, but now I have a hesitation that the block of code would even need an explicit rollback at all.

The following example is slightly more difficult, and I understand that it does require an explicit rollback if the first update statement succeeded. Still, which exceptions should I catch in this case:

cursor = conn.cursor()
# some code
affected_rows1 = cursor.execute(update_statement1, params1)
# some code
affected_rows2 = cursor.execute(update_statement2, params2)
#some code
conn.commit()
cursor.close()

Answer 1

This link shows the various types of Errors that you can catch. MySQLdb.Error is the standard base class from which all other MySQL Errors are derived.

I usually use MySQLdb.Error because it lets you focus on errors relating to MySQLdb itself. By contrast StandardError will catch almost all the exceptions (not something you want if you want better debugging capability). Plus the use of MySQLdb.Error allows you to display the exact error message (MySQL error number and all) so that you can debug it faster.

Coming to the first part of the question, in case of database statements it is (usually) necessary to rollback transactions (if they are supported) in case of error.

The methodology that I follow is to wrap each execute statement in a try except clause (catching MySQLdb.Error) and using rollback if there is an an error before printing the error message and exiting.

However, there is a catch. In MySQLdb the changes that you make to DB are not actually written to the database until you explicilty call commit. So, logically, rollback is not necessary.

As an example,

conn = MySQLdb.connection(db=, host=, passwd=, user=)
cur = conn.cursor()
#Say you have a table X with one entry id = 1 and total = 50
cur.execute("update X set total = 70 where id = 1")
#Actual DB has not yet changed
cur.execute("update X set total = 80 where id = 1")
#Actual DB has still not changed

If you exit the program without commiting, the value in DB will still be 50 because you never called commit().

This is how you would ideally do it:

conn = MySQLdb.connection(db=, host=, passwd=, user=)
cur = conn.cursor()
#Say you have a table X with one entry id = 1 and total = 50
try:
    cur.execute("update X set total = 70 where id = 1")
except MySQLdb.Error,e:
    print e[0], e[1]
    conn.rollback()
    cur.close()
    conn.close()
    #print lengthy error description!!
    sys.exit(2)
    #Note: Value in table is still 50
#If you do conn.commit() here, value becomes 70 in table too!!
try:
    cur.execute("update X set total = 80 where id = 1")
except MySQLdb.Error,e:
    print e[0], e[1]
    conn.rollback()
    cur.close()
    conn.close()
    #print lengthy error description!!
    sys.exit(2)
    #Value in DB will be 
    #a) 50 if you didn't commit anywhere
    #b) 70 if you committed after first execute statement
conn.commit()
#Now value in DB is 80!!
cur.close()
conn.close()