import excel files to mysql

author:zxb

工作每个work经常会导入一到几十张不等的excel表，表少可以用navicat import，但是表多了这样效率会非常慢。
小工具实现方式：将excel保存到一个文件夹下，逐个读取文件夹下excel，读取前三行选择一行作为列名，判断每列最大长度，然后建表，批量插入列名后的数据。
最新实验结果:
数据大小:9 cols x 1033854 rows
导表开始时间:2018-02-09 17:13:03.366167
导表结束时间:2018-02-09 17:16:15.192139

二、用到的库

import pymysql #连接mysql数据库 
import easygui #GUI界面 
import xlrd #读取excel 
import os 
import re 
import datetime

三、预备知识

str.replace(old, new[, max]) 把字符串中的 old（旧字符串）替换成 new(新字符串)，如果指定第三个参数max，则替换不超过 max 次

四、初始化类

class Exceltomysql:

# initialize
    def __init__(self):
        # enterbox() 为用户提供一个最简单的输入框，返回值为用户输入的字符串。
        self.db = easygui.enterbox(msg="Enter Database")  # 选择插入的数据库
        self.conn = pymysql.connect(host="localhost", user='root', passwd='123456', database=self.db, charset='utf8')
        self.cur = self.conn.cursor()
        self.path = ""  # 初始化文件夹路径
        self.column_row = 0  # 初始化列名所在行数

五、主函数

def main(self):
        start_time = datetime.datetime.now()  # 导入前时间
        files = self.getFiles()  # 获取需要导入的excel文件列表
        for file in files:  # 循环导入文件
            # 用文件名做表名
            table_name = "test_" + file.replace(' ', '_').replace('.xlsx', '').replace('.xls','').replace('.XLSX','')
            file = self.path + '\' + file
            col_len, table, nrows, col_names = self.readExcel(file)  # 读取excel数据，并返回列名：列长度，表数据，行数，列名
            self.createTable(table_name, col_len)  # 用表名和列名：列长度建表
            self.insertTable(table_name, table, nrows, col_names)  # 插入表
        end_time = datetime.datetime.now()  # 导入完成后时间
        print(start_time)
        print(end_time)

六、功能函数

    # 返回需要导入的execl文件列表
    def getFiles(self):
        # 函数用于提供一个对话框，返回用户选择的目录名，该目录名是带有完整的路径的
        # 选择Cancel的话，返回值默认为None
        self.path = easygui.diropenbox(msg="choose excel files directory"
                                       , title="directory"
                                       , default="E:pythonExcelToMysql")
        print(os.listdir(self.path))
        files = os.listdir(self.path)
        file_l = []
        for file in files:
            # os.path.isdir(file)判断是否是目录
            if not os.path.isdir(file) and re.fullmatch(r"^[a-zA-Z0-9].*?.(xls|xlsx|XLSX)$",file):#判断是excel文件才导入
                file_l.append(file)
        return file_l

    # read excel
    # 返回工作表的：
    # 1、每一列的字符串最大长度，存放在字典中
    # 2、工作表对象 3、工作表的行数 4、工作表的属性列表
    def readExcel(self, file):
        data = xlrd.open_workbook(file)
        table = data.sheets()[0]  # 工作表对象
        nrows = table.nrows  # 行数
        ncols = table.ncols  # 列数
        msg = "1:" + str(table.row_values(0)) + "
2:" + str(table.row_values(1)) + "
3:" + str(table.row_values(2))#显示前三行
        idx = int(easygui.enterbox(msg=msg, title="Choose column row number:"))
        if idx:
            self.column_row = idx
            col_names = table.row_values(idx-1)  # 属性列表
            for i in range(len(col_names)):
                col_names[i] = col_names[i].strip()  # 清除列名前后空格
            col_len = {}
            max_len = []
            # 获取每一列的字符串最大长度
            for i in range(len(col_names)):
                cols = table.col_values(i)
                col_lens = []
                for col in cols:
                    col_lens.append(len(str(col)))
                max_len = max(col_lens)
                col_len[cols[0].strip()] = max_len

            return col_len, table, nrows, col_names

    # create table
    def createTable(self, table_name, col_len):
        print(table_name)
        sql = "drop table if exists %s" % table_name
        self.cur.execute(sql)
        self.conn.commit()
        sql = "create table %s(" % table_name  # 拼接建表sql
        for key in col_len:
            col_name = key
            print(col_name)
            max_length = col_len[key]
            type = "varchar(255)"
            if max_length > 255:  # 如果最大长度大于255，改用text类型
                type = "text"
            sql = sql + "`%s` %s default null,"%(col_name,type)

        sql = sql[:-1] + ") default charset=utf8"
        easygui.msgbox(msg="%s" % sql, title="create table")
        self.cur.execute(sql)
        self.conn.commit()

    def insertTable(self, table_name, table, nrows, col_names):
        cols = "`,`".join(col_names)
        list = []  # 获取所有要插入的行数据
        for rownum in range(self.column_row, nrows):
            row = table.row_values(rownum)
            list.append(row)
        l = len(col_names)
        v = "%s,"*l  # 拼接插表sql
        v = v[:-1]
        print(v)
        sql = "insert into %s(%s) values(" % (table_name, "`"+cols+"`")
        sql = sql + "%s)" % v
        print(sql)
        self.cur.executemany(sql, list)  # 批量插入
        self.conn.commit()

调用：

run = Exceltomysql()
run.main()