前言

  PyMySQL是一个用于Python编程语言的纯Python接口，用于连接和操作MySQL数据库。它可以帮助测试人员在Python应用程序中轻松地执行各种数据库操作，如查询数据、插入、更新和删除数据等。

一、安装模块

pip install PyMySQL

二、操作MySQL数据库

• 1、连接数据库

import pymysql

# 建立数据库连接
connection = pymysql.connect(
    host='192.168.100.28',  # 数据库主机名
    port=3306,               # 数据库端口号，默认为3306
    user='root',             # 数据库用户名
    passwd='123456',         # 数据库密码
    db='test',               # 数据库名称
    charset='utf8'           # 字符编码
)

# 创建游标对象
cursor = connection.cursor()

• 2、执行SELECT查询

# 执行SELECT查询
sql = """SELECT * FROM `device_info`"""
cursor.execute(sql)

# 返回数据库查询的第一条信息，用元组显示
results = cursor.fetchone()
print(results)

# 返回数据库查询的所有信息，用元组显示
# results = cursor.fetchall()
# for row in results:
#     print(row)

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

• 3、执行新增操作（修改删除同理）

try:
    # 新增操作
    sql = """INSERT INTO `device_info`(`ID`, `DeviceID`) VALUES (6, 8)"""
    cursor.execute(sql)

    # COMMIT命令用于把事务所做的修改保存到数据库
    connection.commit()

except Exception as e:
    print("错误信息:", str(e))
    # 发生错误时回滚
    connection.rollback()

finally:
    cursor.close()
    connection.close()

三、封装

• 封装

import pymysql


class MysqlTool:
    def __init__(self):
        """mysql 连接初始化"""
        self.host = '192.168.100.28'
        self.port = 3306
        self.user = 'root'
        self.password = '123456'
        self.db = 'student'
        self.charset = 'utf8'
        self.mysql_conn = None

    def __enter__(self):
        """打开数据库连接"""
        self.mysql_conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            passwd=self.password,
            db=self.db,
            charset=self.charset
        )
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """关闭数据库连接"""
        if self.mysql_conn:
            self.mysql_conn.close()
            self.mysql_conn = None

    def execute(self, sql: str, args: tuple = None, commit: bool = False) -> any:
        """执行 SQL 语句"""
        try:
            with self.mysql_conn.cursor() as cursor:
                cursor.execute(sql, args)
                if commit:
                    self.mysql_conn.commit()
                    print(f"执行 SQL 语句：{sql}，参数：{args}，数据提交成功")
                else:
                    result = cursor.fetchall()
                    print(f"执行 SQL 语句：{sql}，参数：{args}，查询到的数据为：{result}")
                    return result
        except Exception as e:
            print(f"执行 SQL 语句出错：{e}")
            self.mysql_conn.rollback()
            raise e

• 调用

from mysql_tool import MysqlTool


if __name__ == '__main__':
    with MysqlTool() as db:
        # 查询所有数据
        sql = 'SELECT * FROM student'
        result = db.execute(sql)
        print(f"查询到的数据为：{result}")

        # 带条件的查询
        sql = "SELECT * FROM student WHERE age > %s"
        args = (18,)
        result = db.execute(sql, args)
        print(f"年龄大于18岁的学生：{result}")

        # 单条插入
        sql = "INSERT INTO student(name, age) VALUES (%s, %s)"
        args = ('张三', 22)
        db.execute(sql, args, commit=True)

        # 多条插入
        sql = "INSERT INTO student(name, age) VALUES (%s, %s)"
        args_list = [('李四', 20), ('王五', 21), ('赵六', 23)]
        for args in args_list:
            db.execute(sql, args, commit=True)

        # 修改数据
        sql = "UPDATE student SET age=%s WHERE name=%s"
        args = (23, '张三')
        db.execute(sql, args, commit=True)

        # 删除数据
        sql = "DELETE FROM student WHERE name=%s"
        args = ('张三',)
        db.execute(sql, args, commit=True)