mysql插入sql语句的优化，python图形化展现

3月8日

昨晚闲着没事，看同事往表里插数，40w条insert，插一条commit一个，用了很久，感觉有点儿冗余就拿python缝合怪了一个工具，原理很简单，用tkinker做图形化展示传参，接参后使用pymysql连数据库，读取预制好的文件，放入并拼接一条长sql并commit。然后用py2exe打二进制可执行文件，没有pyinstaller因为我没装上。。。我试了44w条简单varchar，用了3-7s，公司里的水管机用了3m，不过也还行吧哈哈哈。测试代码：

# author jinyunlong
# createtime 2022/3/8 19:32
# 职业   ICBC锅炉房保安
import os
from tkinter import *
import pymysql
import datetime
import logging

logging.basicConfig(filename='sqlinsert.log',format='[%(asctime)s-%(filename)s-%(levelname)s:%(message)s]', level = logging.DEBUG,filemode='a',datefmt='%Y-%m-%d%I:%M:%S %p')

root = Tk()                          #定义root 窗口
root.title("mysql插入sql性能优化_by麦乐鸡贼金龙鱼")           #定义窗口名字
root.geometry('500x500')     #定义窗口大小

# Label标签  <Label>标签来定义表单控制间的关系，当用户选择该标签时，浏览器会自动将焦点转到和标签相关的表单控件上。

l1=Label(root, text='连接数据库地址:')
l1.pack()    #固定窗口位置，将该控件放到窗口上
# 创建输入框
url_text = StringVar()
url_text.set("127.0.0.1")
url = Entry(root, textvariable = url_text,width=60)    #输入连接url
url.pack()     #将输入框部署到窗口里

l2=Label(root, text='用户名')
l2.pack()
username_text = StringVar()
username_text.set("yourname")
username=Entry(root,textvariable = username_text,width=60)     #输入用户名
username.pack()

l3=Label(root, text='密码')
l3.pack()    #固定窗口位置，将该控件放到窗口上
passwd_text = StringVar()
passwd_text.set("yourpasswd")
passwd=Entry(root,textvariable = passwd_text,width=60)     #输入密码
passwd.pack()

l4=Label(root,text='数据库名')
l4.pack()
db_text=StringVar()
db_text.set("yourdb")
db=Entry(root,textvariable=db_text,width=60)  #输入库名
db.pack()

l5=Label(root,text='插表名')
l5.pack()
table_text=StringVar()
table_text.set("yourtable")
table=Entry(root,textvariable=table_text,width=60)  #输入表名
table.pack()

# l7=Label(root,text='插入字段数量')
# l7.pack()
# count_text=StringVar()
# count_text.set("3")
# count=Entry(root,textvariable=count_text,width=60)  #输入字段数量
# count.pack()

# l6=Label(root,text='读文件名')
# l6.pack()
# file_text=StringVar()
# file_text.set("1.txt")
# file=Entry(root,textvariable=file_text,width=60)  #输入要读文件名
# file.pack()

def import_package():
    url = url_text.get()  # 获取url_text 输入框中输入的值
    username = username_text.get()  # 获取username_text 输入框中输入的值
    passwd = passwd_text.get()  # 获取passwd_text 输入框中输入的值
    db = db_text.get()  # 获取db_text 输入框中输入的值
    table = table_text.get()  # 获取table_text 输入框中输入的值
    # file = file_text.get()  # 获取file_text 输入框中输入的值
    # count = count_text.get()  # 获取count_text 输入框中输入的值

    # print("数据库连接url：%s 用户名：%s 密码：%s 数据库：%s " % (url, username, passwd, db))  # 调试代码时看看输出的值是不是正确的，这句可以不要
    logging.info("数据库连接url：%s 用户名：%s 密码：%s 数据库：%s " % (url, username, passwd, db))
    # 核心代码
    clientDB = pymysql.connect(url, username, passwd, db)
    cursor = clientDB.cursor()

    fileDir = 'file'
    tableName = table

    # 记录执行前时间
    start_time = datetime.datetime.now()
    logging.info("开始时间：%s" % (start_time))
    logging.info("插入数据")
    # print("开始时间：", start_time)
    # print("插入数据")

    for list1 in os.listdir(fileDir):
        # with open(file=fileDir + '/'+file, mode='r') as f:
        with open(file=fileDir + '/' + list1, mode='r') as f:
            strSQL = ''
            for line in f:
                item1 = list(line.split(","))[0]
                item2 = list(line.split(","))[1]
                item3 = list(line.split(","))[2].split('\n')[0]
                strSQL += "('{}','{}','{}'),".format(item1, item2, item3)  # 字符串拼接组装各行记录
            sql = strSQL[:-1]  # 去除最后一个逗号获得所有行记录的拼接
            sql = "insert into "+tableName+" values " + sql  # 完整的sql语句
            # print(sql)

            # sql = "INSERT INTO `t_users` VALUES (777, 'adssdw', 'kkkk');"
        # print(sql)
        try:
            cursor.execute(sql)
            # print(sql)
            clientDB.commit()
            # print("事务提交成功,插入完成")
            logging.info("事务提交成功,插入完成")
        except:
            clientDB.rollback()
            # print("处理失败,可能有重复条目,已回滚")
            logging.info("处理失败,可能有重复条目,已回滚")
            cursor.close()
            clientDB.close()
    cursor.close()
    clientDB.close()
    # 记录执行完成时间
    end_time = datetime.datetime.now()
    # print("结束时间：", end_time)
    logging.info("结束时间：%s" % (end_time))
    # 计算时间差
    time_d = end_time - start_time
    # print("插入3字段简单varchar_44万条时间差为{}".format(time_d))
    logging.info("插入3字段简单varchar_44万条时间差为{}".format(time_d))


def createbutton():
    # 创建按钮控件
    package_translation = Button(root, text="开始插,绝不允许暗箱操作", width=30, height=2,
                                 command=import_package)  # text是显示在按钮上的名字，command是传入这个按钮要触发的函数名
    package_translation.pack(side='top')  # side是定义按钮的位置，top表示挨着顶部
    root.mainloop()  # ，最后调用mainloop（）将标签显示在屏幕，进入等待状态

if __name__ == '__main__':
    createbutton()

一些问题：

1、该打包exe可执行文件只能放在纯英文路径下，后续准备把用py2exe打包换成pyinstaller打，不知pyinstall会有这问题不

2、现在只在代码里写死了传3个字段，增加字段的话还需改代码重新打包比较麻烦，后续修改(我想写成窗口传来字段数就有几个item可插，但我不会写哈哈哈哈，DDDD)，问题很大，体验极差

3、exe没ico，不是问题

4、插入数据结束后没提示，回来看下tkinter有无自带方法可弹出提示，大概没啥问题

5、不支持插汉字，小问题

6、公司机器处理个2m的文件就卡，这可咋优化，我自己机器很流畅，看来我是不会遇到10m文件这种场景了，，尽量拆小吧，现在写的是遍历目录下文件，多几条长SQL也不错。看来是不会遇到下图问题

参考文章：

1、python大数据实践之一：导入大批数据文件到mysql

2、python连接mysql—读取配置文件中数据库连接信息

3、python提高mysql数据库插入数据效率

4、python logging模块打印log到指定文件

5、如何使用tkinter实现python传参的图形化封装

提高mysql数据库插入数据效率建议：

1、insert的时候尽量多条一起插，不要单条插。这样可以减少日志量，降低日志刷盘数据量和频率，效率提高很多。
2、在事务中进行插入。也就是每次部分commit，否则每条insert commit 创建事务的消耗也是不小的。
3、 数据插入的时候保持有序。比如Innodb用的是B+树索引，对B+树的插入如果是在索引中间就会需要树节点分裂合并，这也会有一定的消耗。

3月15日

工作之余得闲(饮茶)把上面2、4、5问题都优化了，一个一个看：

问题2，改代码如下：

    # 开启事务
    clientDB.begin()
    for list1 in os.listdir(fileDir):
        # with open(file=fileDir + '/'+file, mode='r') as f:
        result=[]
        with open(file=fileDir + '/' + list1, mode='r',encoding="utf-8") as f:
            for line in f:
                result.append(tuple(line.strip('\n').split(',')))
            # print(result)
            # 计算一行有多少value值需要用字符串占位
            s_count = len(result[0]) * "%s,"
            logging.info("此次需要插入"+str(len(result[0]))+"字段数据")
            sql = "insert into " +tableName+" values (" + s_count[:-1] + ")"
        try:
            cursor.executemany(sql,result)
            clientDB.commit()
            # print("事务提交成功,插入完成")
            logging.info("事务提交成功,插入完成")
            tkinter.messagebox.showinfo(title='成了',message='事务提交成功,插入完成')
        except:
            clientDB.rollback()
            # print("处理失败,可能有重复条目,已回滚")
            logging.info("处理失败,已回滚")
            tkinter.messagebox.showinfo(title='失败了',message='处理失败,已回滚')
            clientDB.rollback()
            cursor.close()
            clientDB.close()
    cursor.close()
    clientDB.close()
    # 记录执行完成时间
    end_time = datetime.datetime.now()
    # print("结束时间：", end_time)
    logging.info("结束时间：%s" % (end_time))
    # 计算时间差
    time_d = end_time - start_time
    # print("插入3字段简单varchar_44万条时间差为{}".format(time_d))
    logging.info("插入起止时间差为{}".format(time_d))

将每行读的内容以元组的形式放列表里了，然后计算了一下首行字段数，修改了下插入方式，使用executemany()方法一次性插入MySQL多条数据：

cursor.executemany(sql,result)

问题4，加入tkinter自带方法即可：

tkinter.messagebox.showinfo(title='成了',message='事务提交成功,插入完成')

问题5，open文件时加入：

encoding="utf-8"