python update mysql 2

#!/usr/bin/env python # -*- coding: utf-8 -*- import xlwt import xlrd import xlutils from xlutils.copy import copy import MySQLdb import os os.chdir("E:\work") def lancome15_read_excel(): workbook = xlrd.open_workbook(r'E:\work\k-means_result.csv') sheet1 = workbook.sheet_by_index(0) # sheet索引从0开始 #rows = sheet1.row_values(0) #行 cols1 = sheet1.col_values(0) cols2 = sheet1.col_values(21) #列 d = {} for i in range(len(cols1)): d[cols1[i]]=cols2[i] return d def lancome15_addclass(): conn=MySQLdb.connect("localhost","root","111111","work") cursor=conn.cursor() d = lancome15_read_excel() print "do it" for key in d: sql = "UPDATE lancome15_user_online SET class='%s' WHERE uid = '%s'" % (d[key], key) #print sql cursor.execute(sql) conn.commit() if __name__ == "__main__": print "Begin" lancome15_read_excel() lancome15_addclass() print "Over"

根据excel表得到一个字典，然后在MySQL的表里根据字典的键值更新对应的class。在此过程中update语句较慢，新建索引后速度加快，然后把conn.commit()放到for循环外会快很多。