rollback ?

http://mysql-python.sourceforge.net/MySQLdb.html#connection-objects

Connection Objects

Connection objects are returned by the connect() function.

commit()

If the database and the tables support transactions, this commits the current transaction; otherwise this method successfully does nothing.

rollback()

If the database and tables support transactions, this rolls back (cancels) the current transaction; otherwise a NotSupportedError is raised.

cursor([cursorclass])

MySQL does not support cursors; however, cursors are easily emulated. You can supply an alternative cursor class as an optional parameter. If this is not present, it defaults to the value given when creating the connection object, or the standard Cursorclass. Also see the additional supplied cursor classes in the usage section.

There are many more methods defined on the connection object which are MySQL-specific. For more information on them, consult the internal documentation using pydoc.






2 http://stackoverflow.com/questions/9802102/python-mysql-when-to-explicitly-rollback-a-transaction

IMHO, you should rollback transactions if you continue to use the same connection. Else everything before the error will get commit when you finish the transactions. For the exception to catch, I always useMySQLdb.Error but I'm not sure that's correct.

x

This link shows the various types of Errors that you can catch. MySQLdb.Error is the standard base class from which all other 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 no. 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()

http://mysql-python.sourceforge.net/MySQLdb-1.2.2/private/_mysql_exceptions-module.html