9. Python对MySQL数据库的操作

1.MySQL操作安装包——pymysql

除了使用图形化工具以外，我们也可以使用编程语言来执行SQL从而操作数据库。在Python中，使用第三方库：pymysql 来完成对MySQL数据库的操作。

安装：pip install pymysql

获取链接对象

• from pymysql import Connection 导包
• Connection(主机,端口,账户,密码) 即可得到链接对象
• 链接对象.close() 关闭和MySQL数据库的连接

执行SQL查询

• 通过连接对象调用==cursor()==方法，得到游标对象
• ==游标对象.execute()==执行SQL语句
• ==游标对象.fetchall()==得到全部的查询结果封装入元组内

"""
演示Python pymysql库的基础操作
"""
from pymysql import Connection

# 构建到MySQL数据库的链接（关键字传参）
conn = Connection(
    host="localhost",   # 主机名（IP）
    port=3306,          # 端口
    user="root",        # 账户
    password="a1234567",  # 密码
)
# 查看MySQL软件信息（判断是否连接成功）
print(conn.get_server_info())
# 执行非查询性质SQL
cursor = conn.cursor()      # 获取到游标对象
# 选择数据库
conn.select_db("test")
# 执行sql（Python中可以不写分号；）
cursor.execute("select * from user")
# 获取查询结果
results: tuple = cursor.fetchall()
for r in results:
    print(r)
# 关闭链接
conn.close()

执行数据插入

"""
演示使用pymysql库进行数据插入的操作
"""
from pymysql import Connection

# 构建到MySQL数据库的链接
conn = Connection(
    host="localhost",   # 主机名（IP）
    port=3306,          # 端口
    user="root",        # 账户
    password="123456",  # 密码
    autocommit=True     # 设置自动提交（执行插入语句的前提）
)

# print(conn.get_server_info())
# 执行非查询性质SQL
cursor = conn.cursor()      # 获取到游标对象
# 选择数据库
conn.select_db("world")
# 执行sql
cursor.execute("insert into student values(10002, '林俊节', 31, '男')")
# # 通过commit确认
# conn.commit()
# 关闭链接
conn.close()

2.综合案例——读取文件，写入数据库

数据定义类（data_define)

"""
数据定义的类
"""
class Record:
    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_define)

"""
和文件相关的类定义
"""
import json

from data_define import Record

# 先定义一个抽象类用来做顶层设计，确定有哪些功能需要实现
class FileReader:

    def read_data(self) -> list[Record]:
        """读取文件的数据，读到的每一条数据都转换为Record对象，将它们都封装到list内返回即可"""
        pass
class TextFileReader(FileReader):

    def __init__(self, path):
        self.path = path            # 定义成员变量记录文件的路径

    # 复写（实现抽象方法）父类的方法
    def read_data(self) -> list[Record]:
        f = open(self.path, "r", encoding="UTF-8")

        record_list: list[Record] = []
        for line in f.readlines():
            line = line.strip()     # 消除读取到的每一行数据中的\n
            data_list = line.split(",")
            record = Record(data_list[0], data_list[1], int(data_list[2]), data_list[3])
            record_list.append(record)

        f.close()
        return record_list
class JsonFileReader(FileReader):

    def __init__(self, path):
        self.path = path            # 定义成员变量记录文件的路径

    def read_data(self) -> list[Record]:
        f = open(self.path, "r", encoding="UTF-8")

        record_list: list[Record] = []
        for line in f.readlines():
            data_dict = json.loads(line)
            record = Record(data_dict["date"], data_dict["order_id"], int(data_dict["money"]), data_dict["province"])
            record_list.append(record)

        f.close()
        return record_list

if __name__ == '__main__':
    text_file_reader = TextFileReader("D:/TyporaNote/note/资料/销售数据/2011年1月销售数据.txt")
    json_file_reader = JsonFileReader("D:/TyporaNote/note/资料/销售数据/2011年2月销售数据JSON.txt")
    list1 = text_file_reader.read_data()
    list2 = json_file_reader.read_data()

    for l in list1:
        print(l)

    for l in list2:
        print(l)

主函数（main）

"""
SQL 综合案例，读取文件，写入MySQL数据库中
"""
from file_define import TextFileReader, JsonFileReader
from data_define import Record
from pymysql import Connection

text_file_reader = TextFileReader("D:/2011年1月销售数据.txt")
json_file_reader = JsonFileReader("D:/2011年2月销售数据JSON.txt")

jan_data: list[Record] = text_file_reader.read_data()
feb_data: list[Record] = json_file_reader.read_data()
# 将2个月份的数据合并为1个list来存储
all_data: list[Record] = jan_data + feb_data

# 构建MySQL链接对象
conn = Connection(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    autocommit=True
)
# 获得游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db("root")
# 组织SQL语句
for record in all_data:
    sql = f"insert into orders(order_date, order_id, money, province) " \
          f"values('{record.date}', '{record.order_id}', {record.money}, '{record.province}')"
    # 执行SQL语句
    cursor.execute(sql)

# 关闭MySQL链接对象
conn.close()