Python学习日志----实现数据插入mysql数据库表

通过读取文件数据获取数据，插入mysql，实现前请安装Pymysql包：

创建表SQL

CREATE TABLE orders(
order_date DATE,
order_id VARCHAR(255),money INT,
province VARCHAR(10)
);

代码如下：
data_define.py

"""
数据定义类
"""


class OrderRecord:

    def __init__(self, date, order_id, money, province):
        self.date = date            # 订单日期
        self.order_id = order_id    # 订单id
        self.money = money          # 订单金额
        self.province = province    # 订单省份

    def __str__(self):
        return f"{self.date}, {self.order_id}, {self.money}, {self.province}"

file_read.py

"""
文件相关操作
"""
import json

from data_define import OrderRecord


class FileOperation:

    def file_read(self) -> list[OrderRecord]:
        pass


class TxtFileReader(FileOperation):

    def __init__(self, path):
        self.path = path

    def file_read(self) -> list[OrderRecord]:
        f = open(self.path, "r", encoding="utf-8")
        order_record_list: [OrderRecord] = []
        for line in f.readlines():
            line = line.strip().split(",")
            order_record_list.append(OrderRecord(line[0], line[1], int(line[2]), line[3]))
        f.close()
        return order_record_list


class JsonFileReader(FileOperation):

    def __init__(self, path):
        self.path = path

    def file_read(self) -> list[OrderRecord]:
        f = open(self.path, "r", encoding="utf-8")
        order_record_list: [OrderRecord] = []
        for line in f.readlines():
            data_dict = json.loads(line)
            order_record_list.append(OrderRecord(data_dict["date"], data_dict["order_id"], int(data_dict["money"]), data_dict["province"]))
        f.close()
        return order_record_list


if __name__ == '__main__':
    txt_file = TxtFileReader("D:/一月份数据.txt")
    list1 = txt_file.file_read()

    json_file = JsonFileReader("D:/二月份数据.txt")
    list2 = json_file.file_read()

    for l in list1:
        print(l)

    for l in list2:
        print(l)

main.py

from pymysql import Connection
from file_read import *
from data_define import *


txt_file_reader = TxtFileReader("D:/一月份数据.txt")
json_file_reader = JsonFileReader("D:/二月份数据.txt")

all_data_list: [OrderRecord] = txt_file_reader.file_read() + json_file_reader.file_read()

# 数据写入数据库
conn = Connection(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    autocommit=True
)

curses = conn.cursor()  #获取到游标对象
conn.select_db("python_test")
for record in all_data_list:
    sql = f"insert into orders(order_date, order_id, money, province) values ('{record.date}', '{record.order_id}', '{record.money}', '{record.province}')"
    curses.execute(sql)
conn.close()

一月份数据：

2011-01-01,4b34218c-9f37-4e66-b33e-327ecd5fb897,1689,湖南省
2011-01-01,5b6a6417-9a16-4243-9704-255719074bff,2353,河北省
2011-01-01,ae240260-68a9-4e59-b4c9-206be4c08a8d,2565,湖北省
2011-01-01,c833e851-880f-4e05-9de5-b547f5ffc5e1,2877,山东省
2011-01-01,dd27e822-884c-4d20-a309-986f6a90e2b9,947,安微省
2011-01-01,8e43e3c5-44bf-4219-8a59-f512607aeefe,815,河北省
2011-01-01,b6882f5f-fb10-4210-9e45-288dd2239594,1363,广东省
2011-01-01,fd5056a8-8223-4d02-9988-04e1b41a57e8,2149,江苏省
2011-01-01,d022df35-3cOe-4753-bccb-37e125a5922b,1739,福建省
2011-01-01,a480686a-77ff-497e-9e32-Of6d9ba3eadd,2999,江苏省
2011-01-02,4b34218c-9f37-4e66-b33e-327ecd5fb897,1689,湖南省
2011-01-02,5b6a6417-9a16-4243-9704-255719074bff,4204,河北省
2011-01-02,ae240260-68a9-4e59-b4c9-206be4c08a8d,2565,湖北省
2011-01-02,c833e851-880f-4e05-9de5-b547f5ffc5e1,2877,山东省
2011-01-02,dd27e822-884c-4d20-a309-986f6a90e2b9,7527,安微省
2011-01-02,8e43e3c5-44bf-4219-8a59-f512607aeefe,815,河北省
2011-01-02,b6882f5f-fb10-4210-9e45-288dd2239594,8253,广东省
2011-01-02,fd5056a8-8223-4d02-9988-04e1b41a57e8,2782,江苏省
2011-01-02,d022df35-3cOe-4753-bccb-37e125a5922b,1739,福建省
2011-01-02,a480686a-77ff-497e-9e32-Of6d9ba3eadd,2999,江苏省
2011-01-03,4b34218c-9f37-4e66-b33e-327ecd5fb897,5343,湖南省
2011-01-03,5b6a6417-9a16-4243-9704-255719074bff,1012,河北省
2011-01-03,ae240260-68a9-4e59-b4c9-206be4c08a8d,5534,湖北省
2011-01-03,c833e851-880f-4e05-9de5-b547f5ffc5e1,8634,山东省
2011-01-03,dd27e822-884c-4d20-a309-986f6a90e2b9,947,安微省
2011-01-03,8e43e3c5-44bf-4219-8a59-f512607aeefe,454,河北省
2011-01-03,b6882f5f-fb10-4210-9e45-288dd2239594,752,广东省
2011-01-03,fd5056a8-8223-4d02-9988-04e1b41a57e8,752,江苏省
2011-01-03,d022df35-3cOe-4753-bccb-37e125a5922b,1739,福建省
2011-01-03,a480686a-77ff-497e-9e32-Of6d9ba3eadd,2999,江苏省
2011-01-04,4b34218c-9f37-4e66-b33e-327ecd5fb897,4452,湖南省
2011-01-04,5b6a6417-9a16-4243-9704-255719074bff,752,河北省
2011-01-04,ae240260-68a9-4e59-b4c9-206be4c08a8d,7575,湖北省
2011-01-04,c833e851-880f-4e05-9de5-b547f5ffc5e1,2967,山东省
2011-01-04,dd27e822-884c-4d20-a309-986f6a90e2b9,9477,安微省
2011-01-04,8e43e3c5-44bf-4219-8a59-f512607aeefe,8158,河北省
2011-01-04,b6882f5f-fb10-4210-9e45-288dd2239594,1263,广东省
2011-01-04,fd5056a8-8223-4d02-9988-04e1b41a57e8,2148,江苏省
2011-01-04,d022df35-3cOe-4753-bccb-37e125a5922b,8639,福建省
2011-01-04,a480686a-77ff-497e-9e32-Of6d9ba3eadd,2399,江苏省

二月份数据：

{"date": "2011-02-01", "order_id": "caf99222-53d6-427b-925d-3187fc71a86a", "money": 1805, "province":"江西省"}
{"date": "2011-02-01", "order_id": "3dea6f83-a9b2-4197-ba9f-2b25704c530b", "money": 2547, "province":"广东省"}
{"date": "2011-02-01", "order_id": "93cf7a56-3f90-4df9-af76-de7233c1dddb ", "money": 1216, "province": "福建省"}
{"date": "2011-02-01", "order_id": "0042323a-e555-4d64-a70b-81380ca3aae7" , "money": 1193,"province":"四川省"}
{"date": "2011-02-01", "order_id": "Ofbe1745-ac65-48f4-985b-b71875fcfbf7", "money":30,        "province":"云南省"}
{"date": "2011-02-01", "order_id": "6ee5af28-f6ce-42e3-96b6-96f3844c5fda" , "money": 2770,   "province":"江西省"}
{"date": "2011-02-01", "order_id": "2f629b33-62a5-44c1-8a0f-b983e811fb40" , "money": 2291, "province": "福建省"}
{"date": "2011-02-01", "order_id": "773d1259-f81d-413d-81cb-b5f7cOd8f97c" , "money": 453,  "province":"河南省"}
{"date": "2011-02-01", "order_id": "2eda7fc0-f3e8-4064-8427-b674c381cb80","money":2267,     "province": "江苏省"}
{"date": "2011-02-01", "order_id": "41d9f2ca-88b8-4bd7-b64b-c5cd9ba54186", "money": 2321, "province":"贵州省"}