python接口自动化系列（07）：执行初始化sql

阅读目录

• 实现目标
• 安装模块
• 项目配置添加数据库配置
• 添加数据库工具类
• 修改测试类
• 执行结果

 

本系列汇总，请查看这里：https://www.cnblogs.com/uncleyong/p/18033074

回到顶部

实现目标

执行初始化sql，保证测试用例中的数据可用，这样不用每次执行测试前修改测试数据。

 

回到顶部

安装模块

安装操作mysql数据库的模块pymysql

pip install pymysql

 

回到顶部

项目配置添加数据库配置

?

1 2 3 4 5 6 7 8 9

# mysql数据库的连接信息 mysql_info = {     "host":"166.99.166.99",     "port":3306,     "user":"root",     "password":"123456",     "db":"qzcsbj",     "charset":"utf8" }

 

补充：也可以放yaml文件中

 

回到顶部

添加数据库工具类

utils下添加mysql_tool.py

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

#!/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

 

回到顶部

修改测试类

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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条