我的职能是,从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()