python - 捕获 MySQLdb 上的更新错误

Question

我有一个函数可以从 CSV 文件更新 MySQL 表。 MySQL 表包含客户帐号——我用它来与 CSV 文件进行比较。有时，某些查询会失败，因为尚未添加与 CSV 文件进行比较的帐号。

如何从更新过程中失败的 CSV 文件中获取记录？我想将这些记录存储在一个单独的文件中，然后稍后重新读取该文件，直到所有记录均已成功更新。

下面是更新数据库的函数。

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

更新查询返回受影响的行数。执行 am 后检查 Cursor.rowcount 将给出该数字。如果不是 1，则该更新行失败。