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. ...
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
Get-WmiObject -Class Win32_OperatingSystem | Select-Object -Property Caption
python –version
pip install pandas==2.1.0 -i https://mirrors.aliyun.com/pypi/simple/
pip install pymysql==1.1.0 -i https://mirrors.aliyun.com/pypi/simple/
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)
mysql --version
mysql -h 127.0.0.1 -P 3306 -u root -p"123456"
1.3 數據準備
運行以下python代碼準備模擬數據用於測試。
import os
if not os.path.exists('./datas'):
os.mkdir('./datas')
text = '''+-------------+--------------+--------------+-------------+
| customer_id | name | visited_on | amount |
+-------------+--------------+--------------+-------------+
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
+-------------+--------------+--------------+-------------+'''
with open('./datas/customer.txt','w',encoding='utf-8') as file:
file.write(text)
運行前：
代碼運行前如圖，只有一個datas.py的文件。
代碼運行後如圖，在運行前的基礎上生成了一個datas的文件夾以及一個存有用來測試的模擬數據文件，也可直接從mysql端複製粘貼並手動創建即可，值得註意的是不能有任何空行。
2 mysql端配置
2.1 連接mysql
在powershell終端使用命令連接mysql資料庫，註意這裡先不指定需要連接的資料庫名。
mysql -h 127.0.0.1 -P 3306 -u root -p"123456"
2.2 確保當前資料庫為空
查看當前所在的資料庫，確保該值為空。
select database();
2.3 查看此時變數character_set_connection對應的編碼值
在確保database()的值為空的前提下查看character_set_connection對應的編碼值。
show variables where Variable_name = 'character_set_connection';
變數character_set_connection對應的編碼值為gbk，後面的配置需要用到此參數。
2.4 創建資料庫
在mysql中創建一個名為mydatabase的資料庫預設編碼為UTF8供pymysql連接。
DROP DATABASE IF EXISTS mydatabase;
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對應的編碼值一致。
import re
import pandas as pd
from sqlalchemy import create_engine
# 準備數據
def ready_datas(tablename:str,date_times:[str]=''):
# 處理文本數據
with open("./datas/{}.txt".format(tablename), "r", encoding="utf-8") as file:
text = file.read()
text = re.sub(r"[ ]*[\|][ ]*", ",", text)
text = text.split('\n')[1]+'\n'+'\n'.join(text.split('\n')[3:-1])
text = text.replace(",\n,", "\n")[1:-1]
# 轉換為csv文件
with open("./datas/{}.csv".format(tablename), "w", encoding="utf-8") as file:
file.write(text)
# 轉換為datafram數據
df = pd.read_csv("./datas/{}.csv".format(tablename),encoding='utf-8')
print('dataframe {}:success'.format(tablename))
# 數據覆蓋寫入mysql
if date_times != '':
for date_time in date_times:
df = df.astype({date_time:"datetime64[ns]"})
# df[date_time] = pd.to_datetime(df[date_time])
# print(df.dtypes)
engine = create_engine('mysql+pymysql://root:123456@localhost/mydatabase?charset=GBK')
df.to_sql(name='{}'.format(tablename),index=None,con=engine,if_exists='replace')
print('table {}:success'.format(tablename))
return df
customer = ready_datas('customer',date_times=['visited_on'])
代碼運行前如圖所示，datas文件夾中僅有customer.txt文件。
代碼運行後如圖所示，datas文件夾下生成一個customer.csv的文件。
4 結果驗證
打開mysql並連接創建好的資料庫mydatabase。
mysql -h 127.0.0.1 -P 3306 -u root -p"123456" mydatabase;
使用mysql的dql語句查看生成的數據表customer的數據與表格文件customer.csv的數據是否一致。
select * from customer limit 3;
查看結果表明數據確實一致。
