本系列汇总,请查看这里:https://www.cnblogs.com/uncleyong/p/18033074
实现目标
执行初始化sql,保证测试用例中的数据可用,这样不用每次执行测试前修改测试数据。
安装模块
安装操作mysql数据库的模块pymysql
pip install pymysql
项目配置添加数据库配置
# mysql数据库的连接信息
mysql_info = {
"host":"166.99.166.99",
"port":3306,
"user":"root",
"password":"123456",
"db":"qzcsbj",
"charset":"utf8"
}
补充:也可以放yaml文件中
添加数据库工具类
utils下添加mysql_tool.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author: 韧
# @wx: ren168632201
# @Blog: https://www.cnblogs.com/uncleyong/
import pymysql
import logging
logger = logging.getLogger(__name__)
from conf.settings import mysql_info
class MySQLDB:
__instance = None
def __new__(cls, *args, **kwargs):
if not cls.__instance:
cls.__instance = object.__new__(cls, *args)
return cls.__instance
def __init__(self):
# 建立数据库连接
self.conn = pymysql.connect(
host=mysql_info["host"],
port=mysql_info["port"],
user=mysql_info["user"],
password=mysql_info["password"],
db=mysql_info["db"],
charset="utf8"
)
# 使用cursor方法获取操作游标
self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
def __del__(self):
try:
# 关闭游标
self.cur.close()
# 关闭连接
self.conn.close()
except Exception as ex:
logger.error(f"出错了:{ex}")
def query(self, sql, state="all"):
try:
self.cur.execute(sql)
if state == "all":
# 查询全部
data = self.cur.fetchall()
else:
# 查询单条
data = self.cur.fetchone()
return data
except Exception as ex:
logger.error(f"出错了:{ex}")
raise
def execute(self, sql):
try:
# 增删改
rows = self.cur.execute(sql)
# 提交事务
self.conn.commit()
return rows
except Exception as ex:
logger.error(f"出错了:{ex}")
self.conn.rollback()
raise
if __name__ == '__main__':
pass
修改测试类
class TestCase:
@pytest.mark.parametrize("casedata", read_data_from_yaml(CASE_DATA_PATH))
def test_case(self, casedata):
# logger.info(f"---当前测试用例数据是:{casedata}")
# 获取initSql
initSql = casedata["initSql"]
# 类型是list
# logger.info(type(initSql))
# logger.info(">>>>>initSql:{}".format(initSql))
if initSql:
for sql in initSql:
logger.info("==================替换前sql:{}".format(sql))
sql = var_substitution(sql)
logger.info(f"==================替换后sql:{sql}")
if sql.strip().startswith('select'):
res = my.query(sql)
logger.info("{}查询结果是:{}".format(sql,res))
else:
res = my.execute(sql)
logger.info("操作成功:{}条".format(res))
执行结果
sql执行成功
因为上一次数据都删除了,再次执行,结果都是0条