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("处理完成")