51行代码，自制Txt转MySQL软件！

一、前言

大家好，我是老表，今天早上看B站，发现首页给我推了前不久关注的一个up主（@是我_是我_就是我，为了方便下文中以 小是 代称）视频，于是我就打开看了，于是就有了接下来的故事～

小是 想要实现的是老师给的一个任务：读取txt文件，然后存储到mysql，正好前两天帮读者写了一个 excel to sqlserver的软件，另外最后 小是 说了目前java版本的两个问题：

• 只能读取string类型数据（这个没太理解，可能是读取文件格式或者文件字段类型）
• 不支持动态修改读取文件和数据库配置（写一个gui或者简单点直接写的终端逻辑程序也可以）

我一想，我可以呀，然后先提供了思路，占一个坑位，接下来就是我吭哧吭哧的实现代码了。

二、开始动手动脑

本项目所有源码+环境+测试文件都已经开源啦，大家不想看代码实现过程的可以直接跳到下一部分直接食用方法。

2.0 环境准备

我这里使用的是：

• python 3.10
• 第三方包和对应版本：

pandas==1.3.5
PyMySQL==1.0.2
SQLAlchemy==1.4.30
PySimpleGUI==4.56.0

方便项目环境管理，我一般使用pipenv创建管理虚拟环境，如果你也感兴趣，可以看下我之前写的pipenv基本使用教程。

pipenv install   # 创建虚拟环境
pipenv shell    # 进入虚拟环境

pip install pandas PyMySQL SQLAlchemy PySimpleGUI  # 虚拟环境下安装需要的包

exit   # 退出虚拟环境，直接关闭cmd也可

2.1 数据读取

查看示例数据发现有2种分隔符，空格和制表符\t，所以我们读取数据的时候也需要指定两种分隔符，另外这个文件没有表头，所以方便数据处理和存储，最好加上表头，和数据库字段名一致。

如下代码：

import pandas as pd
'''
read_csv参数解释：
1、要读取文件路径
2、sep 指定分隔符，读取数据，使用|可以添加多种分隔符
3、header=None 没有表头 默认第一行为表头
4、engine 设置程序引擎
'''
data = pd.read_csv('./resources/ctd2020-09-27.txt', sep=' |\t',header=None, engine='python')
data

不难看出，这样直接读取会有两列nan，这是因为还有两个空格分隔的，没关系，我们删除下整列都为nan的列即可，数据读取无误后，我们在添加上表头，实现代码如下：

# 读取文件
def get_txt_data(filepath):
    columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N']
    data = pd.read_csv(filepath, sep=' |\t',header=None,engine='python')
    # 删除数据全nan的列 （如果确实有这种列，后面可以再加上，不影响）
    data.dropna(axis=1, how='all', inplace=True) 
    # 指定列名
    data.columns = columns
    return data

get_txt_data('./resources/ctd2020-09-27.txt')

2.2 数据处理

因为 小是 没有指明会有什么数据处理（除上面读取数据障碍外），所以这里我们就简单的删除下重复列，下次分享的excel to sqlserver 会涉及一些数据处理（如：按指定字段去重、日期格式转变等）

# 数据处理
def process_data(data):
    # 不包含要处理的列，则直接简单去重后、存入数据库
    data.drop_duplicates(inplace=True)
    return data 

2.3 数据存储

因为我们要将数据存储到mysql，所以存储之前，我们需要先连接上数据库，这里我使用的是sqlalchemy+pymysql链接mysql数据库，代码如下：

# 链接数据库
def link_mysql(user, password, database):
    # create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库"，其他参数)
    engine = create_engine(f'mysql+pymysql://{user}:{password}@localhost:3306/{database}?charset=utf8')
    return engine

然后使用pandas的to_sql函数可以很简单且快速将Dataframe格式数据存储到数据库中，感兴趣的可以看下我之前写的Python数据存储读取，6千字搞定各种方法，里面有对比直接使用pymysql和使用pandas的to_sql存储数据的速率差别，描述不一定准确，欢迎阅读指正。

# 存储数据
def data_to_sql(data, user='root', password='Zjh!1997', database='sql_study', table='ctd'):
    engine = link_mysql(user, password, database)
    
    # 调用pandas 的 to_sql 存储数据
    t1 = time.time()  # 时间戳 单位秒
    print('数据插入开始时间：{0}'.format(t1))
    # 第一个参数：表名
    # 第二个参数：数据库连接引擎
    # 第三个参数：是否存储索引
    # 第四个参数：如果表存在 就追加数据
    data.to_sql(table, engine, index=False, if_exists='append')
    t2 = time.time()  # 时间戳 单位秒
    print('数据插入结束时间：{0}'.format(t2))
    print('成功插入数据%d条，'%len(data), '耗费时间：%.5f秒。'%(t2-t1))

最后我们写一个汇总函数即可，就是在这里将上面的逻辑串联起来：

# 文本文件存储到mysql
def txt_to_sql(filepath, user='root', password='Zjh!1997', database=


    
'sql_study', table='ctd'):
    # 读取文件
    data = get_txt_data(filepath)
    # 数据处理
    data = process_data(data)
    # 数据存储
    data_to_sql(data, user, password, database, table)

2.4 调用函数测试

filepath = './resources/ctd2020-09-27.txt'
# 只指定文件路径，其他参数使用默认值 方便测试
txt_to_sql(filepath)

然后还可以写个数据库读取函数进一步测试数据是否真的存储到了数据库中：

# 从数据库读取数据
def read_mysql(user='root', password='Zjh!1997', database='sql_study', table='ctd'):
    engine = link_mysql(user, password, database)
    # 读取的sql语句
    sql = f'select * from {table} limit 3'
    # 第一个参数：查询sql语句
    # 第二个参数：engine，数据库连接引擎
    pd_read_sql = pd.read_sql(sql, engine)
    return pd_read_sql

调用函数，查看存储情况，没问题。

到这里，程序功能部分就算完成啦，接下来就是写个gui界面，让用户使用更方便，比如：文件路径 数据库参数输入等。

2.5 写一个GUI

这里我们使用的是PySimpleGUI包，如名字所言，真的很简单，其底层是Python自带的tkinter。

• 导入相关包

# 写个GUI
import PySimpleGUI as sg
# 调用数据存储函数
from txt_to_sql import txt_to_sql  

• 编写GUI布局

# 设置GUI布局 
# 相关参数 key：接收输入数据的key target：对应数据显示到指定的target
# default_text：设置输入框默认值
layout = [
    [sg.Text('读取指定文件内容，处理后存入指定数据库表中～')],
    [sg.FileBrowse('点击选取文件', key='filepath', target='file'), sg.Text(key='file')],
    [sg.Text('登录用户名'), sg.InputText(key='user', default_text='root', )],
    [sg.Text('登录密码'), sg.InputText(key='password', default_text='Zjh!1997')],
    [sg.Text('数据库名称'), sg.InputText(key='database', default_text='sql_study')],
    [sg.Text('存储的表名'), sg.InputText(key='table', default_text='ctd')],

    [sg.Button('开始处理'), sg.Button('退出')]
]

• 创建程序窗口、业务逻辑

    
# 创建窗口程序
window = sg.Window('Txt To MySQL', layout, default_element_size=(100,))
while True:
    event, values = window.read()  # 获取数据
    # print(event)
    if event=='开始处理':
        # 将输入数据传入数据处理程序
        txt_to_sql(values['filepath'], values['user'], values['password'], values['database'], values['table'])
    else:
        # event in (None, '退出'):  # 点击退出 关闭程序
        break
window.close()

• 显示效果

在布局部分，layout为一个列表数据，列表内每个元素也为列表，表示一列，常使用的的布局模块有：Text（本文显示）、InputText（输入框）、Button（普通按钮）、FileBrowse（单个文件选择）；

在创建窗口程序部分，主要是设置窗口默认大小default_element_size，设置宽度就行，高度会根据布局控件自适应，另外获取输入值也非常简单，直接read即可，返回的是字典，数据处理来更方便。

到这里，我们就完成所有程序的开发啦，后面是直接使用程序的方法。

三、直接食用方法

两个代码文件，去除空格和注释，还有51行代码，嘿嘿～

3.1 功能

• GUI界面，支持选择指定文件、输入数据库用户名 密码 数据库名称 表名。
• 读取指定文件，数据处理后，存入指定的数据库表中，如果表不存在就直接创建一个新表存储数据；否则直接添加数据到数据表中。

3.2 使用方法

下载本项目代码：https://github.com/XksA-me/txt-to-mysql

解压后打开文件：python-Jonny，本文件内包含了所有python代码+测试数据+环境+windows bat运行文件，其他文件为@ 用Java写的txt to mysql方法和相关配置文件，

原项目地址：https://github.com/schatz0-0/txt-to-mysql 原项目B站视频分享地址：https://www.bilibili.com/video/BV12b4y1J7pD

接续介绍如何使用python版本，首先我们需要解压我提供的python环境包，直接解压即可，无需二次安装。上面截图中相关文件解释：

├── Pipfile  虚拟环境配置文件（不用管）
├── Pipfile.lock  虚拟环境依赖包关系（不用管）
├── __pycache__  （不用管）
│   └── txt_to_sql.cpython-310.pyc （不用管）
├── python-Jonny-tJ_VXFMA.7z （虚拟环境压缩包，需要直接解压）
├── requirements.txt （本项目需要的第三Python包，都已经安装到给的虚拟环境了）
├── resources  （测试数据）
│   └── ctd2020-09-27.txt
├── start.bat  （windwos下可直接运行文件，启动项目）
├── txt_to_sql.py  （Python代码文件，包含数据读取 处理 存储）
└── txt_to_sql_gui.py  （Python代码文件，包含gui界面，在里面调用txt_to_sql.py文件，所以只用运行本文件即可）

虚拟环境解压好后，我们需要根据自己本地目录情况，修改下start.bat文件，内容如下：

@echo off
C:
cd C:\Users\Administrator\Desktop\python-Jonny
C:\Users\Administrator\Desktop\python-Jonny\python-Jonny-tJ_VXFMA\Scripts\python txt_to_sql_gui.py

exit

这块不是很懂，现学现卖，上面大概意思就是：进入c盘项目目录下，然后利用虚拟环境python可执行文件 运行我能的代码，最后exit退出程序。

大家需要修改的就是里面涉及到的文件目录，和自己本地一致即可，我是在云服务器上写的就放在c盘（只有一个盘），大家可以选择放到其他盘，方便管理。

修改好后，直接点击start.bat即可运行项目，会弹出一个黑框（cmd）,和一个gui程序界面，黑框里会显示程序执行输出的日志（就是程序里的print或者报错信息），gui里我们需要先点击按钮选择存储的文件，然后输入数据库相关信息，设置了默认值，然后点击开始处理 按钮即可运行程序、存储数据，点击退出按钮关闭程序。

四、可以拓展

• 目前只支持txt，而且数据格式为指定类型（空格或者制表符\t分隔的），有时间，大家有需要，可以拓展支持多种格式文件，加一个文件后缀识别即可

• 界面简陋，上午看到up@是我_是我_就是我发的视频，就想到用python写也很方便，时间仓促，界面比较一般，不过工具嘛，最开始能实现功能比较重要。

本项目有很多不足和可以改进的地方，欢迎大家进行学习交流～