将mysql的输出文本写回mysql

海盗眼

1 准备工作

1.1 环境准备

操作系统：Microsoft Windows 10 专业工作站版
软件版本：Python 3.9.6
第三方包：
pip install pandas2.1.0
pip install pymysql1.1.0
pip install sqlalchemy==2.0.23

1. Get-WmiObject -Class Win32_OperatingSystem | Select-Object -Property Caption
2. python –version
3. pip install pandas==2.1.0 -i https://mirrors.aliyun.com/pypi/simple/
4. pip install pymysql==1.1.0 -i https://mirrors.aliyun.com/pypi/simple/
5. pip install sqlalchemy==2.0.23 -i https://mirrors.aliyun.com/pypi/simple/

复制代码

1.2 数据库准备

数据库版本：Ver 8.1.0 for Win64 on x86_64 (MySQL Community Server - GPL)

1. mysql --version
2. mysql -h 127.0.0.1 -P 3306 -u root -p"123456"

复制代码

1.3 数据准备

运行以下python代码准备模拟数据用于测试。

1. import os
2. if not os.path.exists('./datas'):
3.     os.mkdir('./datas')
4. text = '''+-------------+--------------+--------------+-------------+
5. | customer_id | name         | visited_on   | amount      |
6. +-------------+--------------+--------------+-------------+
7. | 1           | Jhon         | 2019-01-01   | 100         |
8. | 2           | Daniel       | 2019-01-02   | 110         |
9. | 3           | Jade         | 2019-01-03   | 120         |
10. | 4           | Khaled       | 2019-01-04   | 130         |
11. | 5           | Winston      | 2019-01-05   | 110         |
12. | 6           | Elvis        | 2019-01-06   | 140         |
13. | 7           | Anna         | 2019-01-07   | 150         |
14. | 8           | Maria        | 2019-01-08   | 80          |
15. | 9           | Jaze         | 2019-01-09   | 110         |
16. | 1           | Jhon         | 2019-01-10   | 130         |
17. | 3           | Jade         | 2019-01-10   | 150         |
18. +-------------+--------------+--------------+-------------+'''
19. with open('./datas/customer.txt','w',encoding='utf-8') as file:
20. file.write(text)

复制代码

运行前：
代码运行前如图，只有一个datas.py的文件。

代码运行后如图，在运行前的基础上生成了一个datas的文件夹以及一个存有用来测试的模拟数据文件，也可直接从mysql端复制粘贴并手动创建即可，值得注意的是不能有任何空行。

2 mysql端配置

2.1 连接mysql

在powershell终端使用命令连接mysql数据库，注意这里先不指定需要连接的数据库名。

1. mysql -h 127.0.0.1 -P 3306 -u root -p"123456"

复制代码

2.2 确保当前数据库为空

查看当前所在的数据库，确保该值为空。

1. select database();

复制代码

2.3 查看此时变量character_set_connection对应的编码值

在确保database()的值为空的前提下查看character_set_connection对应的编码值。

1. show variables where Variable_name = 'character_set_connection';

复制代码

变量character_set_connection对应的编码值为gbk，后面的配置需要用到此参数。
2.4 创建数据库

在mysql中创建一个名为mydatabase的数据库默认编码为UTF8供pymysql连接。

1. DROP DATABASE IF EXISTS mydatabase;
2. CREATE DATABASE IF NOT EXISTS mydatabase DEFAULT CHARSET UTF8;

复制代码

3 python端配置

3.1 python代码思路

① 先用正则表达式对测试文本数据customer.txt清洗；
② 将清洗的结果保存为customer.csv；
③ Pandas读取customer.csv文件得到数据帧df；
④ 创建mysql数据引擎并将数据帧df写入到数据库mydatabase中保存为表customer
3.2 python代码源码

函数参数：
① tablename：文本文件名→mysql数据表名
② date_times：数据中需要转换为日期类型的数据对应的字段名列表。
注意事项：
确保26行的charset值与2.3看到的变量character_set_connection对应的编码值一致。

1. import re
3. import pandas as pd
4. from sqlalchemy import create_engine
6. # 准备数据
7. def ready_datas(tablename:str,date_times:[str]=''):
8.     # 处理文本数据
9.     with open("./datas/{}.txt".format(tablename), "r", encoding="utf-8") as file:
10.         text = file.read()
11.     text = re.sub(r"[ ]*[\|][ ]*", ",", text)
12.     text = text.split('\n')[1]+'\n'+'\n'.join(text.split('\n')[3:-1])
13.     text = text.replace(",\n,", "\n")[1:-1]
14.     # 转换为csv文件
15.     with open("./datas/{}.csv".format(tablename), "w", encoding="utf-8") as file:
16.         file.write(text)
17.     # 转换为datafram数据
18.     df = pd.read_csv("./datas/{}.csv".format(tablename),encoding='utf-8')
19.     print('dataframe {}:success'.format(tablename))
20.     # 数据覆盖写入mysql
21.     if date_times != '':
22.         for date_time in date_times:
23.             df = df.astype({date_time:"datetime64[ns]"})
24.             # df[date_time] = pd.to_datetime(df[date_time])
25.     # print(df.dtypes)
26.     engine = create_engine('mysql+pymysql://root:123456@localhost/mydatabase?charset=GBK')
27.     df.to_sql(name='{}'.format(tablename),index=None,con=engine,if_exists='replace')
28.     print('table {}:success'.format(tablename))
29.     return df
30. customer = ready_datas('customer',date_times=['visited_on'])

复制代码

代码运行前如图所示，datas文件夹中仅有customer.txt文件。

代码运行后如图所示，datas文件夹下生成一个customer.csv的文件。

4 结果验证

打开mysql并连接创建好的数据库mydatabase。

1. mysql -h 127.0.0.1 -P 3306 -u root -p"123456" mydatabase;

复制代码

使用mysql的dql语句查看生成的数据表customer的数据与表格文件customer.csv的数据是否一致。

1. select * from customer limit 3;

复制代码

查看结果表明数据确实一致。

来源:https://www.cnblogs.com/mlcode/p/pymysql.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！