python实现通用excel导入到mysql

{
    "file": "OrderDetail-2020-06-03.xls",
    "startRow": 1,
    "table": "tk_bill",
    "comitcount":50,
    "map": {
        "trade_id": "o",
        "tk_status": "p",
        "item_title": "h",
        "alipay_total_price": "r",
        "tk_paid_time": "c",
        "pub_share_pre_fee": "ad",
        "subsidy_fee": "w",
        "tk_settletime": "d"
    }
}
  1. file:要处理的表格文件
  2. startRow:从第几行开始导入
  3. table:导入到哪个表
  4. comitcount:多少笔一提交( 由于数据库对sql长度是有限制的,不建议设置太大)
  5. map:表字段与excel列的映射关系
import math
import xlrd
import json
from Db import Db

db = Db()


def loadConfig():
    ‘‘‘
    加载配置
    :return:
    ‘‘‘
    with open("map.conf") as f:
        return json.load(f)


def chang26to10(value):
    ‘‘‘
    26进制转10进制
    ‘‘‘
    transfArray = {A: 1, B: 2, C: 3, D: 4, E: 5, F: 6, G: 7, H: 8, I: 9, G: 10, K: 11, L: 12,
                   M: 13,
                   N: 14, O: 15, P: 16, Q: 17, R: 18, S: 19, T: 20, U: 21, V: 22, W: 23, X: 24,
                   Y: 25, Z: 26};
    sum = 0
    length = len(value)
    for idx in range(length):
        char = value[idx]
        if char.upper() not in transfArray:
            raise Exception("无效字符:<%s>" % char)
        v = transfArray[char.upper()]
        tem = v * math.pow(26, length - idx - 1)
        sum = tem + sum
    return int(sum)


if __name__ == __main__:
    importConfig = loadConfig()
    # 获取要导入的表名
    tableName = importConfig["table"]
    comitcount = importConfig["comitcount"]
    startRow = importConfig["startRow"]
    cols = []
    colsidx = []
    for k, v in importConfig["map"].items():
        cols.append(k)
        colsidx.append(v)
    sqlpre = "insert into %s(%s) values " % (tableName, ",".join(cols))
    xls = xlrd.open_workbook(importConfig["file"])
    sheet = xls.sheets()[0]
    startIdx = 0
    startIdx = startRow
    vlist = []
    for i in range(startIdx, sheet.nrows):
        print("处理第%s行" % i)
        temlist = [];
        for cidx in colsidx:
            s = "‘%s‘" % sheet.cell_value(i, chang26to10(cidx) - 1).replace("", "‘‘")
            temlist.append(s)
        vlist.append("(%s)" % ",".join(temlist))
        # 每50行提交一次
        if len(vlist) == comitcount:
            sql = sqlpre + ",".join(vlist)
            db.execSql(sql)
            vlist.clear()
            continue
    if len(vlist) > 0:
        sql = sqlpre + ",".join(vlist)
        db.execSql(sql)
        vlist.clear()
    print("处理完成")

 为了保证数据导入成功,建表时字段建议用varchar。(不然会有各种类型、非空之类的错误)

python实现通用excel导入到mysql

上一篇:.NET --- 页面刷新(html 和 js两种方式)


下一篇:(3)优化TCP编写客服端和服务端(上传图片)