前言
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)