pymysql封装

原创已于 2025-12-10 09:50:44 修改 · 1.7k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #mysql #python

于 2023-04-14 18:20:59 首次发布

数据库同时被 2 个专栏收录

65 篇文章

订阅专栏

Python3

30 篇文章

订阅专栏

pymysql封装

安装PyMySQL库

本机环境：

Python 3.7.0
MySQL 5.7

使用Python来操作MySQL，需要用到 PyMySQL库

# 安装PyMySQL库
pip install PyMySQL

我这里安装的版本是 0.9.3。

操作	关键方法	备注
连接数据库	pymysql.connect(…)	返回 Connection 对象
执行单条 SQL	cursor.execute(sql, params)	防注入，需 commit
批量插入	cursor.executemany(sql, seq)	一次插入多行，效率高
获取结果	fetchone / fetchall	可指定 DictCursor 返回字典
事务控制	conn.begin / commit / rollback	保证数据一致性

基本操作-增删改查

Python操作MySQL的步骤如下：

建立数据库连接,创建数据库连接对象。
通过 cursor() 创建游标对象（pymysql通过游标来执行sql和获取结果）
使用 execute() 执行sql
获取结果（查询）/提交事务（增删改）
关闭游标
关闭连接

**注意：**用Python进行增删改查操作之前，最好先检查下sql语句是否正确，确保没有错误之后，再放到Python代码中。

查询操作

import pymysql

# 查询操作
def select_db(select_sql):
    """查询"""

    # 建立数据库连接，db是数据库连接对象
    # root:数据库密码@1.2.3.4:3306/ebook_management_system?charset=utf8', pool_recycle=3600
    db = pymysql.connect(
        host="1.2.3.4",
        port=3306,
        user="root",
        passwd="数据库密码",
        db="ebook_management_system"
    )

    # 通过 cursor() 创建游标对象，并让查询结果以字典格式输出
    cur = db.cursor(cursor=pymysql.cursors.DictCursor)

    # 使用 execute() 执行sql
    cur.execute(select_sql)

    # 使用 fetchall() 获取所有查询结果
    data = cur.fetchall()

    # 关闭游标
    cur.close()

    # 关闭数据库连接
    db.close()
    # 返回查询数据
    return data


if __name__ == '__main__':
    select_sql = 'SELECT * FROM user WHERE username="张三"'
    print(select_db(select_sql))

在上面，创建游标时使用了 cursor=pymysql.cursors.DictCursor ，目的是为了让查询结果以 列表嵌套字典 的格式输出，以方便后续操作。如果不使用该参数，那么返回的查询结果将是 元组嵌套元组 的形式。

查询返回成字典

# 导入 PyMySQL 库，用于连接和操作 MySQL 数据库
import pymysql
# 从 pymysql.cursors 模块导入 DictCursor，用于让查询结果以字典形式返回
from pymysql.cursors import DictCursor

# 建立与 MySQL 数据库的连接
# 使用 DictCursor 作为游标类，使查询结果以字段名作为键的字典格式返回（而非默认的元组）
conn = pymysql.connect(
    host='127.0.0.1',          # 数据库服务器地址（本地）
    port=3306,                 # MySQL 默认端口
    user='root',               # 数据库用户名
    password='your_password',  # 数据库密码（请替换为实际密码）
    database='test_db',        # 要连接的数据库名称
    charset='utf8mb4',         # 字符集，支持完整的 UTF-8（包括 emoji）
    # 查询结果返回成字典（默认是元组）
    # 关键配置：指定游标类型为 DictCursor，返回字典格式结果
    cursorclass=DictCursor     
)

# 使用 try-finally 确保数据库连接在任何情况下都能被正确关闭，避免资源泄漏
try:
    # 使用上下文管理器（with 语句）自动管理游标生命周期
    with conn.cursor() as cursor:
        # 执行 SQL 查询语句，从 dts_fail_instances 表中选取关键字段
        # 查询字段：实例ID、任务名称、失败时间
        cursor.execute("SELECT instance_id, task_name, fail_time FROM dts_fail_instances")
        
        # 获取所有查询结果，返回值为字典组成的列表
        # 每个字典的键为字段名（如 'instance_id'），值为对应行的数据
        rows = cursor.fetchall()
        
        # 遍历查询结果，逐行打印
        for row in rows:
            # 通过字段名（字典键）访问数据，提升代码可读性和可维护性
            print(
                row['instance_id'],   # 打印 DTS 实例 ID
                row['task_name'],     # 打印任务名称
                row['fail_time']      # 打印失败时间
            )
finally:
    # 无论是否发生异常，都确保关闭数据库连接
    conn.close()

更新操作

import pymysql

def update_db(update_sql):
    """更新"""

    # 建立数据库连接
    db = pymysql.connect(
        host="1.2.3.4",
        port=3306,
        user="root",
        passwd="数据库密码",
        db="ebook_management_system"
    )

    # 通过 cursor() 创建游标对象
    cur = db.cursor()
    try:

        # 使用 execute() 执行sql
        cur.execute(update_sql)

        # 提交事务
        db.commit()
    # 如果执行出错，捕获异常
    except Exception as e:
        print("操作出现错误：{}".format(e))

        # 回滚所有更改
        db.rollback()
    # 无论是否成功提交事务，都关闭游标和数据库
    finally:
        # 关闭游标
        cur.close()
        # 关闭数据库连接
        db.close()

在执行更新操作sql时，需要使用事务处理，即使用创建的连接进行提交事务 db.commit() ，使当前更改生效。当执行sql语句出现错误时，还应该支持事务回滚操作 db.rollback() 。

插入数据操作

在执行新增操作sql时，需要更新操作 一样，使用事务来处理。

# 插入数据
def insert_db(insert_sql):
    """插入"""
    # 建立数据库连接
    db = pymysql.connect(
        host="1.2.3.4",
        port=3306,
        user="root",
        passwd="数据库密码",
        db="ebook_management_system"
    )

    # 通过 cursor() 创建游标对象
    cur = db.cursor()
    try:
        # 使用 execute() 执行sql
        cur.execute(insert_sql)

        # 提交事务
        db.commit()

    except Exception as e:
        print("操作出现错误：{}".format(e))
        # 回滚所有更改
        db.rollback()
    finally:
        # 关闭游标
        cur.close()
        # 关闭数据库连接
        db.close()

删除操作

在执行删除操作sql时，需要像 更新操作、插入操作 一样，使用事务来处理。

def delete_db(delete_sql):
    """删除"""
    # 建立数据库连接
    db = pymysql.connect(
        host="1.2.3.4",
        port=3306,
        user="root",
        passwd="数据库密码",
        db="ebook_management_system"
    )

    # 通过 cursor() 创建游标对象
    cur = db.cursor()
    try:
        # 使用 execute() 执行sql
        cur.execute(delete_sql)
        # 提交事务
        db.commit()
    except Exception as e:
        print("操作出现错误：{}".format(e))
        # 回滚所有更改
        db.rollback()
    finally:
        # 关闭游标
        cur.close()
        # 关闭数据库连接
        db.close()

类封装–简单数据库操作

从上面的代码中，我们可以发现存在不少问题，比如增删改操作的代码除了执行的sql不同外，其他代码都是一样的；同时每个操作时都写了一次建立数据库连接的代码，也就是说每次操作都会建立和删除连接的操作。

针对上面的问题，我们完全可以对上面的增删改查操作进行简单的封装，把Python操作MySQL的代码封装到一个类中，只在创建对象实例的时候建立数据库连接，只在对象删除时关闭连接。

代码示例：

参考：https://www.cnblogs.com/wintest/p/12152687.html

只有两个方法：

查询数据方法
变更数据方法
- 包含增删改.

import pymysql
from pymysql.cursors import DictCursor


class MysqlDb:
    def __init__(self, host: str, port: int, user: str, passwd: str, db: str):
        """
        建立数据库连接
        :param host: 传入数据库主机地址
        :param port: 传入数据库端口
        :param user: 传入数据库用户名
        :param passwd: 传入数据库登陆密码
        :param db: 传入数据库名称
        """

        # 建立数据库连接
        self.conn = pymysql.connect(
            host=host,
            port=port,
            user=user,
            passwd=passwd,
            db=db
        )
        # 以字典形式返回结果的游标
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def __del__(self):  # 对象资源被释放时触发，在对象即将被删除时的最后操作
        # 关闭游标
        self.cur.close()
        # 关闭数据库连接
        self.conn.close()

    def select_db(self, sql: str):
        """
        查询操作
        :param sql: 传入查询sql语句
        :return: 返回查询结果
        """

        # 使用 execute() 执行sql
        self.cur.execute(sql)

        # 使用 fetchall() 获取查询结果
        data = self.cur.fetchall()
        # 返回查询数据
        return data

    def change_db(self, sql: str):
        """
        变更数据操作----更新/插入/删除
        :param sql: 传入数据库语句
        :return: 没有返回值
        """

        try:
            # 使用 execute() 执行sql
            self.cur.execute(sql)
            # 提交事务
            self.conn.commit()
        except Exception as e:
            print("操作出现错误：{}".format(e))
            # 回滚所有更改
            self.conn.rollback()


if __name__ == '__main__':
    sql_obj = MysqlDb('localhost', 3306, 'root', 'sql123456', 'new_db')
    # 定义查询sql语句
    select_sql = 'SELECT * FROM user WHERE username="张三2"'
    # 定义更新数据sql语句
    update_sql = 'UPDATE user SET username = "张三2" WHERE id = 1'
    # 定义插入数据sql语句
    insert_sql = 'INSERT INTO user(id, username, password) VALUES(11, "王五", "333333")'
    # 定义删除数据sql语句
    delete_sql = 'DELETE FROM user WHERE id = 11'
    # 接收查询结果
    query_result = sql_obj.select_db(select_sql)
    print(query_result)
    # 执行更新数据sql
    sql_obj.change_db(update_sql)
    # 执行插入数据sql
    sql_obj.change_db(insert_sql)
    # 执行删除数据sql
    sql_obj.change_db(delete_sql)