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