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

 

本系列汇总，请查看这里：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条