PyMySQL 基本操作指南

原创

Heaven645

发布于 2024-08-11 23:42:22

1311

发布于 2024-08-11 23:42:22

文章被收录于专栏：Python学习

前言

在现代应用开发中，与数据库的高效交互是关键的一环。对于使用 Python 语言的开发者来说，PyMySQL 是一个非常实用的工具，它提供了一个简洁且功能强大的接口，用于连接和操作 MySQL 数据库。本章详细讲解了 PyMySQL 的基本操作步骤。

本篇文章参考：黑马程序员

一、基本操作步骤

PyMySQL 是一个用于 Python 的第三方库，它提供了一个简洁且功能强大的接口用于连接和操作 MySQL 数据库。

基本操作步骤如下：

①安装库

电脑输入Win+R打开运行窗口，在运行窗口输入“cmd”，点击“确定”

输入“pip install pymysql”

② 连接数据库

# 导包
from pymysql import Connection 
# 创建数据库连接  
con=Connection(  
    host='localhost',        # 数据库主机地址
    port=3306,          # 端口,默认3306  
    user='username',         # 数据库用户名  
    password='password',     # 数据库密码  
)

③创建游标

cursor = connection.cursor()

游标是一个数据库对象，用于逐行检索查询结果。在处理大量数据时，游标允许逐行或按块检索数据，从而避免一次性加载所有数据。这种方法有助于减少内存消耗并提高性能。作为数据库交互的核心，游标使开发者能够方便地执行 SQL 操作、从结果集中提取数据、管理事务，并确保资源的合理使用。

与数据库的交互：游标提供了与数据库交互的接口，允许我们执行不同类型的 SQL 语句（如查询、插入、更新、删除等）。它像一个指针，指向数据库结果集的当前位置，帮助我们从数据库中获取数据或执行修改。
执行 SQL 语句：通过游标的execute()方法，我们可以执行 SELECT 数据查询语句以及INSERT、UPDATE、DELETE 等数据操作语句。
获取查询结果：执行完查询后，游标提供了fetchall()方法获取所有结果。该方法返回一个包含所有结果行的列表，每一行是一个元组。
迭代遍历结果集：游标返回的结果集可以方便地进行迭代遍历。例如，可以在循环中逐行处理数据。这样可以逐条访问查询结果，而不必一次性加载所有数据，从而节省内存并提高效率。
管理数据库事务：事务可确保一系列数据库操作要么全部成功，要么全部失败，保持数据的一致性。游标用于执行这些操作并利用连接对象的commit()和 rollback()方法管理事务的提交或回滚。

④执行SQL语句

a. 执行查询语句：

# 执行查询性质SQL
cursor.execute("select * from table_name)

# 获取所有结果
result=cursor.fetchall()
#  迭代遍历结果集
for r in result:
    print(r)

b. 执行插入、更新、删除等非查询语句：

pymysql 在执行数据插入或其它产生数据更改的SQL语句时，默认需要通过commit() 确认这种更改行为，否则更改不生效。

# 插入数据  
cursor.execute("INSERT INTO table_name (column1, column2) VALUES (value1, value2)")  

# 提交更改  
connection.commit()

如果不想手动commit确认，可以在构建链接对象时设置自动commit的属性。

# 导包
from pymysql import Connection 
# 创建数据库连接  
con=Connection(  
    host='localhost',        # 数据库主机地址
    port=3306,               # 端口,默认3306  
    user='username',         # 数据库用户名  
    password='password',     # 数据库密码
    autocommit=True          # 设置自动提交
)

⑤关闭游标和连接

cursor.close()      # 关闭游标  
connection.close()  # 关闭连接

二、综合案例分析

【例题1】

假设“sjms”数据库中有一个“student”表，“student”表存储了如下内容：

# 导入pymysql库中的Connection类，用于建立与MySQL数据库的连接
from pymysql import Connection

# 构建MySQL数据库的连接
con=Connection(
    host="localhost",   # 主机名（IP）
    port=3306,          # 端口,默认3306
    user="root",        # 用户名（输入自己设置的用户名，我设置的账户名是root）
    password="123456"    # 密码（输入自己设置的密码，我设置的密码是123456）
)
# get_server_info()方法将打印出连接的MySQL服务器的版本信息，用于确认连接是否成功以及获取数据库的相关信息
print(con.get_server_info())

cursor=con.cursor()   # 获取游标对象
con.select_db("sjms") # 选择数据库

# 执行查询性质SQL
cursor.execute("select * from student")

# 游标对象使用fetchall()方法，得到的是全部的查询结果，返回的是元组
result=cursor.fetchall()
for r in result:
    print(r)
# 关闭连接
con.close()

输出结果：

【例题2】

某公司有两份数据文件：“2011年1月销售数据.txt”和“2011年2月销售数据JSON.txt”。前者是普通文本文件，使用逗号分隔数据记录，内容依次为日期、订单编号、销售额和销售省份；后者则是JSON格式的数据，内容依次为日期、订单编号、销售额和销售省份。现在需要对这两份数据进行分析和处理，要求是使用面向对象的编程思想来读取和处理数据，计算每日的销售额，并利用Pyecharts库以柱状图的形式展示结果。

文件部分数据如下：

要求使用面向对象的编程思想来读取和处理数据，并将数据写入MySQL。

【实现步骤】

①封装数据对象

"""
数据定义类
Record类用于封装销售数据中每一条记录
"""
class Record:
    # 该构造方法接受日期、订单编号、销售额和省份，并将它们存储为类的实例变量
    def __init__(self,date,order_id,money,province):
        self.date = date            # 订单日期
        self.order_id = order_id    # 订单编号
        self.money = money          # 订单金额
        self.province = province    # 销售省份
    # 字符串表示法，方便打印Record对象的内容，返回一个格式化的字符串
    def __str__(self):
        return f"{self.date},{self.order_id},{self.money},{self.province}"

【分析】

Record 类的主要作用是将销售数据的每一条记录封装为一个对象，提供了一个清晰的结构来存储和访问这些数据。通过该类，可以方便地创建多个销售记录对象，并在需要时以易读的格式输出它们。在整个分析过程中，Record 类将被用于读取和存储来自两个数据文件的销售记录。

②读取数据

"""
文件相关的类定义
"""

# 导入 json 模块，用于处理JSON文件
import json
# 导入 Record 类，以便在其他类中创建记录对象
from data_define import Record

# 定义一个抽象类，用于顶层设计，定义了文件读取的接口
class FileReader:
    # 抽象方法，强制子类实现相应的读取方法，并返回一个包含Record对象的列表
    def read_data(self)->list[Record]:
        pass

# 子类TextFileReader继承父类FileReader，用于从文本文件中读取数据
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来存储Record对象
        record_list:list[Record]=[]
        # 循环读取每一行数据
        for line in f.readlines():
            line=line.strip()   # 消除读取到的每一行数据中的 \n
            data_list=line.split(",")
            # print(data_list)
            # 输出内容：['2011-01-01', '4b34218c-9f37-4e66-b33e-327ecd5fb897', '1689', '湖南省']……
            record=Record(data_list[0],data_list[1],int(data_list[2]),data_list[3])
            # print(record)
            # 输出内容：2011-01-01,4b34218c-9f37-4e66-b33e-327ecd5fb897,1689,湖南省
            record_list.append(record)

        f.close()
        return record_list


# 子类JsonFileReader继承自父类FileReader，用于从JSON文件中读取数据
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():
            # 将json数据转换为Python字典
            data_dict=json.loads(line)
            # 从字典中提取相应的字段
            record=Record(data_dict["date"],data_dict["order_id"],data_dict["money"],data_dict["province"])
            print(record)
            record_list.append(record)

        f.close()
        return record_list

【分析】

这段代码实现了一个简单的文本文件读取器，用于读取不同格式的文件（文本和 JSON），并将文件中的每一行数据转换为 Python 对象，便于在后续程序中管理和操作这些数据。

③构建数据库链接

a. 新建一个数据库，数据库名称为“py_sql”

b. 基于数据结构建表，建表语句如下：

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

c. 编写详细代码

# 导入必要的库和之前定义的类
from file_define import FileReader,TextFileReader,JsonFileReader
from data_define import Record
from pymysql import Connection

# 创建TextFileReader和JsonFileReader的实例，读取数据到列表中
text_file_reader=TextFileReader("D:/2011年1月销售数据.txt")
jan_data:list[Record]=text_file_reader.read_data()

json_file_reader=JsonFileReader("D:/2011年2月销售数据JSON.txt")
feb_data:list[Record]=json_file_reader.read_data()

# 将两个月份的数据合并为一个list来存储
all_data:list[Record]=jan_data+feb_data

# 构建MySQL数据库的连接
con=Connection(
    host="localhost",   # 主机名（IP）
    port=3306,          # 端口,默认3306
    user="root",        # 用户名（输入自己设置的用户名，我设置的账户名是root）
    password="18414",   # 密码（输入自己设置的密码，我设置的密码是123456）
    autocommit=True     # 设置自动提交
)
# 获取游标对象
cursor=con.cursor()
# 选择数据库
con.select_db("py_sql")
# 组织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}')")
    # print(sql)
    # 输出结果：insert into orders(order_date,order_id,money,province) values('2011-01-01','4b34218c-9f37-4e66-b33e-327ecd5fb897','1689,'湖南省')……

    # 执行sql语句
    cursor.execute(sql)
# 关闭连接
con.close()

【分析】

这段代码读取了两个不同格式的销售数据文件（一个文本文件和一个JSON文件），合并了数据并将其插入到 py_sql 数据库的 orders 表中。

运行结果：