Catching update errors on MySQLdb

Question

I have a function that updates a MySQL table from a CSV file. The MySQL table contains the client account number -- this is what I use to compare with the CSV file. At some point, some of the queries will fail because the account number being compared from the CSV file has not been added yet.

How do I get the records from the CSV file that failed during the update process? I wanted to store these records in a separate file and then re-read the file at a later time until all records have been successfully updated.

Below is the function that updates the DB.

def updateDatabase(records, options):
    """Update database"""
    import re # Regular expression library
    import MySQLdb

    # establish DB connection
    try:
         db = MySQLdb.connect(host="localhost", user="root", passwd="", db="demo")
    except MySQLdb.Error, e:
         print "Error %d: %s" % (e.args[0], e.args[1])
         sys.exit (1)
    # create cursor
    cursor = db.cursor()
    # tell MySQLdb to turn off auto-commit
    db.autocommit(False) 

    # inform the user that this could take a while
    if len(records) > 499:
     print 'This process can take a while.'

    print 'Updating the database now...'
    # this is the actual loop
    maxrecords = len(records)
    for record in records:
     account_no, ag_1to15, ag_16to30, ag_31to60, ag_61to90, ag_91to120, beyond_120, total, status, credit_limit = record
     if re.match('1000', account_no):
      query = """UPDATE sys_accountscf SET cf_581 = %s, cf_583 = %s, cf_574 = %s, cf_575 = %s, cf_576 = %s, cf_577 = %s, cf_579 = %s, cf_585 = '%s', cf_558 = %s WHERE cf_538 = %s"""
     else:
      query = """UPDATE sys_accountscf SET cf_580 = %s, cf_582 = %s, cf_568 = %s, cf_569 = %s, cf_571 = %s, cf_572 = %s, cf_578 = %s, cf_584 = '%s', cf_555 = %s WHERE cf_535 = %s"""
     cursor.execute(query % (ag_1to15, ag_16to30, ag_31to60, ag_61to90, ag_91to120, beyond_120, total, status, credit_limit, account_no))
    # commit all changes and close database connection  
    try:
     db.commit()
    except:
     db.rollback()
    cursor.close()
    db.close()

Answer 1

An update query returns the number of rows affected. Checking the Cursor.rowcount after you made am execute will give that number. If it is not 1, that that update row failed.