python导入xls数据到db--优化版
import sys from orator import DatabaseManager import xlrd dbconfig = { 'mysql': { 'driver': 'mysql', 'host': "", 'database': "", 'user': "", 'password': "", 'port': 3306 } } db = DatabaseManager(dbconfig) def loadConfig(): return { "file": ["f:\\11.xlsx"], "startRow": 5, "table": "yy_oph2", "cleanBeforeImport": 1, "comitcount": 100, "map": { "r": "ophno" }, "defaultValueMap": { "userid": 1 } } def chang26to10(value): s = value.upper() re = 0 for x in s: re *= 26 re += ord(x) - ord('A') + 1 return re if __name__ == '__main__': importConfig = loadConfig() # 获取要导入的表名 tableName = importConfig["table"] comitcount = importConfig["comitcount"] startRow = importConfig["startRow"] cleanBeforeImport = importConfig["cleanBeforeImport"] defaultValueMap = importConfig["defaultValueMap"] if cleanBeforeImport == 1: print("确定要清空%s表数据请输入yes?" % (tableName)) flag = sys.stdin.readline().strip() if flag.upper() == "YES": db.table(tableName).truncate() kv = importConfig["map"] defaultkv = importConfig["defaultValueMap"] # 支持多文件导入 for fileidx in range(len(importConfig["file"])): xls = xlrd.open_workbook(importConfig["file"][fileidx]) sheetcount = len(xls.sheets()) # 多sheet导入 for sidx in range(sheetcount): sheet = xls.sheets()[sidx] startIdx = startRow vlist = [] for i in range(startIdx - 1, sheet.nrows): print("处理第%s行" % i) dict = {} for k, v in kv.items(): dict[v] = str(sheet.cell_value(i, chang26to10(k) - 1)).replace("'", "''") for k, v in defaultkv.items(): dict[k] = v vlist.append(dict) # 每50行提交一次 if len(vlist) == comitcount: db.table(tableName).insert(vlist) vlist.clear() continue if len(vlist) > 0: db.table(tableName).insert(vlist) vlist.clear() print("处理完成")