Python+requests+yaml+pandas+openpyxl实现接口接口自动化

遇到的坑以及解决方式:
1.pandas读取EXCEL的时,空的时候显示 为 nan,eval()转字典的时候会报错
2.openpyxl操作EXCEL的时候需要保证EXCEL没有被占用状态
3.requests请求的时候不能打开抓包工具,要不然https会报SSL错误
4.提取响应中的值参数化的时候重新定义变量名避免变量提取重复被覆盖
定义变量名为[]newkey,value为另一个[]newdata,然后用zip方法合并转为字典newkeyvalue = dict(zip(newkey, newdata))
5.直接连redis数据库获取手机验证码code(这个需要code在代码中特殊处理)
6.EXCEL中的数据值提前设置格式为文本格式，要不然在linux上运行的时候会报一些错误
7.通过运行时的命令行传入参数，实现运行不同的EXCEL数据源达到测试不同的系统或者环境

VarConfig 传入不同的命令行参数运行不同的数据

# encoding=utf-8

import os
import sys

SystemName = "小程序"
title_str = "接口自动化测试报告"

if len(sys.argv) > 1:
    print("传入的参数为：", sys.argv[1:])
    DataPathFile = '/DataProd.xlsx'
else:
    print("未传入参数")
    DataPathFile = '/data.xlsx'

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

一.DatabaseUtils:redis数据库操作

import redis
import yaml


class DatabaseUtils:

    def __init__(self):
        self._yaml_path = "../data/variables.yaml"

    # 查询redis获取登录的验证码
    def Redisslect(self):
        res = redis.Redis(
            host="11.11.11.11",
            port=65534,
            db=0,
            password="com",
            decode_responses=True
        )
        phone= self.test_get_value('phone')
        code = res.get("verify_code:"+phone)
        print(code)
        res.close()
        return code

    def test_get_value(self, value):
        with open(self._yaml_path, encoding="utf-8") as f:
            doc = yaml.safe_load(f.read())
            print("doc========", doc)
            return str(doc[value])


if __name__ == '__main__':
    p = DatabaseUtils()
    p.Redisslect()

二.Excel的相关操作:ExcelUtil

# coding=utf-8
import os
import sys

import openpyxl
import pandas as pd
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import WHITE

from config.VarConfig import DataPathFile

abs_path = os.path.abspath(__file__)
# print(abs_path)
# 获取文件所在目录的上一级目录,也就是根目录
project_path = os.path.dirname(os.path.dirname(abs_path))
# print(project_path)
# 通过os.sep的方法来获取config目录的全路径
abs_file_path = project_path + os.sep + "data" + DataPathFile


class ExcelUtil:

    def __init__(self):
        self.filepath = abs_file_path

    def ReadExcelList(self):
        excel = pd.read_excel(self.filepath)
        print("行列数", excel.shape)
        print("列数", excel.shape[1])
        print("行数", excel.shape[0])
        print("RequestMethod 列 第一行的数据", excel.loc[0, 'RequestMethod'])
        # print("获取指定行数的值", excel.sample(1).values)
        print("表头", excel.columns[0])
        # print("指定具体行列数据", excel.iloc[1, 1])
        productcode = excel.values

        apidataset = []
        for Line in range(excel.shape[0]):
            # 字典必须放在这里,要不然会重复被覆盖
            apidata = {}
            for Column in range(excel.shape[1]):
                apidata.update({excel.columns[Column]: excel.iloc[Line, Column]})
            # print(apidata)
            apidataset.append(apidata)
        print(apidataset)
        return apidataset

    # 根据Excel值获取对应的行号和列号(列号是表头列的列号)
    def GetExcelRowCol(self, num_value, excel_header):
        global row, col
        demo_df = pd.read_excel(self.filepath)
        for indexs in demo_df.index:
            for i in range(len(demo_df.loc[indexs].values)):
                if (str(demo_df.loc[indexs].values[i]) == num_value):
                    row = indexs + 2
                    print('行号', indexs + 2)
        list = demo_df.columns
        for indexs in range(len(list)):
            if (list[indexs] == excel_header):
                col = indexs + 1
                print('列号', indexs + 1)
        return row, col

    # 根据Excel单元格的值获得对应的行号和列号(不包括表头行)
    def GetExcelRowColExceptHeader(self, value):
        demo_df = pd.read_excel(self.filepath)
        for indexs in demo_df.index:
            for i in range(len(demo_df.loc[indexs].values)):
                if (demo_df.loc[indexs].values[i] == value):
                    row = indexs + 2
                    col = i + 1
                    print('行号', row, '列号', col)
                    return row, col

    def writeCell(self, rowNo, colsNo, content):
        '''
        :param rowNo: 行号
        :param colsNo: 列好
        :return: 传入行号和列好，对单元格回写返回的内容,且Fail单元格标黄
        '''
        book = openpyxl.load_workbook(self.filepath)
        if content == 'Fail':
            sheet = book['api']
            white = PatternFill(fill_type='solid', fgColor="FFC125")
            cell = sheet.cell(rowNo, colsNo)
            cell.fill = white
        else:
            sheet = book['api']
            white = PatternFill(fill_type='solid', fgColor=WHITE)
            cell = sheet.cell(rowNo, colsNo)
            cell.fill = white
        sheet = book['api']
        sheet.cell(rowNo, colsNo).value = content
        book.save(self.filepath)
        book.close()

    def statistical(self):
        excel = pd.read_excel(self.filepath)
        # 总得用例数
        Totalcount = excel.shape[0]
        # 通过的用例数
        Passcount = len(excel.loc[(excel['Practical'] == 'Pass')])
        # 失败的用例数据
        failcount = len(excel.loc[(excel['Practical'] == 'Fail')])
        Passrate = '{:.2%}'.format(Passcount / Totalcount)
        Failrate = '{:.2%}'.format(failcount / Totalcount)
        print(Passrate)
        return Totalcount, Passcount, failcount, Passrate, Failrate


excel = ExcelUtil()
if __name__ == '__main__':
    pa = ExcelUtil()

    print(pa.statistical())

三.参数关联的相关操作:GetResponsePara

class GetResponsePara(object):
    # 获取字典中的某一个键的值
    def dict_get(self, dic, locators, default=None):
        if not isinstance(dic, dict) or not isinstance(locators, list):
            return default
        value = None
        for locator in locators:
            if not type(value) in [dict, list] and isinstance(locator, str) and not self.can_convert_to_int(locator):
                try:
                    value = dic[locator]
                except KeyError:
                    return default
                continue
            if isinstance(value, dict):
                try:
                    value = self.dict_get(value, [locator])
                except KeyError:
                    return default
                continue
            if isinstance(value, list) and self.can_convert_to_int(locator):
                try:
                    value = value[int(locator)]
                except IndexError:
                    return default
                continue
        return value

    def can_convert_to_int(self, input):
        try:
            int(input)
            return True
        except BaseException:
            return False

    # 获取字典中的某一个键的值(只支持结果是唯一值)(如果键对应的值是list只会取到其中的一个值)
    def get_para2(self, response, key, path):
        output = path.split('.')
        for m in output:
            if isinstance(response, dict):
                if key in response:
                    print('==========', response[key])
                    return response[key]
                response = response[m]
            else:
                for n in response:
                    if key in n:
                        return n[key]

    def chaiparam(self, output):
        list = []
        mn = output.split(';')
        print(mn)
        for i in mn:
            ban = i.split('=')
            list.append(ban)
        print(dict(list))
        return dict(list)


if __name__ == '__main__':
    p = GetResponsePara()
    dict_test = {"result": {"code": "110002", "msg": [{'status': 'ok'}, {'status': 'failed'}]}}
    result = p.dict_get(dict_test, ['result', 'msg', '1', 'status'])
    print(result)
    dict_test = {"statusCode": 0, "message": "success", "data": {"token": "2C8A41292D428", "onlyUser": 2}}
    result = p.dict_get(dict_test, ['data', 'token'])
    print(result)
    response = {"statusCode":0,"message":"success","data":[{"id":213},{"id":214}]}
    key = 'id'
    path = 'data.id'
    print(p.get_para2(response, key, path))
    output = 'token=data.token'
    print(p.chaiparam(output))

四.生成html报告

# encoding=utf-8

import os

from utils.ExcelUtil import ExcelUtil


class TestCaseRunnerHtml(object):
    """html报告"""
    # ------------------------------------------------------------------------
    # HTML Template
    HTML_TEMPLATE = r"""
        <!DOCTYPE html>
        <html lang="en">
        <head>
            <meta charset="UTF-8">
            <title>接口自动化测试报告</title>
            <link href="http://libs.baidu.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
            <h2 style="font-family: Microsoft YaHei">%(title)s</h2><br>

            <p class='attribute' style="font-size:18px;"><strong>测试结果 : </strong> %(total)s</p><br>
            <style type="text/css" media="screen">
        body  { font-family: Microsoft YaHei,Tahoma,arial,helvetica,sans-serif;padding: 20px;}
                table {font-size: 100%%;}
                #header_row1 td{text-align: center}
                #header_row1 th{text-align: center}
                #header_row td{text-align: center}
                #header_row th{text-align: center}
                #sys td{text-align: center}
                #sys th{text-align: center}
                #mes td{text-align: center}
                #mes th{text-align: center}
        </style>
        </head>
        <body>
            <table id='result_table1' class="table table-condensed table-bordered table-hover">
                <colgroup>
                    <col align='left' />
                    <col align='right' />
                    <col align='right' />
                    <col align='right' />
                </colgroup>
                <tr id='header_row1' class="text-center success" style="font-weight: bold;font-size: 18px;">
                    <th>客户端及版本</th>
                    <th>通过率</th>
                    <th>失败率</th>
                    <th>开始时间</th>
                    <th>结束时间</th>
                </tr>
                %(table_total)s

            </table>
            <!-- 执行模块 -->
            <p class='attribute' style="font-size:18px;"><strong>测试报告详情 : </strong> </p>
            <table id='result_table' class="table table-condensed table-bordered table-hover">
                <colgroup>
                   <col align='left' />
                   <col align='right' />
                   <col align='right' />
                   <col align='right' />
                   <col align='right' />
                </colgroup>
                <tr id='header_row' class="text-center success" style="font-weight: bold;font-size: 14px;">
                    <th width="10%%">用例ID</th>
                    <th width="10%%">接口名称</th>
                    <th width="10%%">URL</th>
                    <th width="10%%">请求类型</th>
                    <th width="10%%">请求内容</th>
                    <th width="10%%">请求头</th>
                    <th width="10%%">响应Code</th>
                    <th width="2%%">响应结果</th>
                    <th width="20%%">实际结果</th>
                </tr>
                %(table_module)s
                %(table_case)s


            </table>
            <script type="text/javascript">
                //change color
                //取都用demo的多组
                var eles = document.getElementsByClassName('demo');
                console.log(eles);
                var x=document.getElementById("demo2").innerText;
                console.log("the value is :"+x);
                //每组都应用样式
                for(var i = 0; i < eles.length; i++){
                    if(eles[i].innerText == 'Pass'){
                        eles[i].style.color = 'green';
                    }else{
                        eles[i].style.color = 'red';
                    }
                }

            </script>   

        </body>
        </html>"""
 
    # 总数据
    REPORT_TMPL_TOTAL = """
        <tr class='failClass warning' id="sys">
            <td>%(systemName)s</td>
            <td>%(runCasePass)s</td>
            <td>%(runCaseFail)s</td>
            <td>%(runStartTime)s</td>
            <td>%(runEndTime)s</td>
        </tr>"""

    # 详情表头
    REPORT_TMPL_MODULE = """
        <tr id='header_row2' class="text-center success" style="font-weight: bold;font-size: 14px;">
            <th>%(id)s</th>
            <th>%(apiName)s</th>
            <th>%(url)s</th>
            <th>%(method)s</th>
            <th>%(message)s</th>
            <th>%(expect)s</th>
            <th>%(statusCode)s</th>
            <th>%(responseResult)s</th>
            <th class='demo' id="demo">%(runResult)s</th>
        </tr>"""

    # case数据
    REPORT_TMPL_CASE = """
        <tr class='failClass warning' id="mes">
            <td>%(id)s</td>
            <td>%(apiName)s</td>
            <td>%(url)s</td>
            <td>%(method)s</td>
            <td>%(message)s</td>
            <td>%(headers)s</td>
            <td>%(statusCode)s</td>
            <td>
            <div style="height: 80px;overflow-y: auto; overflow-x: hidden;">%(responseResult)s
            </div>
            </td>
            <td class='demo' id="demo2">%(runResult)s</td>
        </tr>"""

    # 解析列表表头
    def analysis(self, SystemName, Passrate, Failrate, RunStartTime, RunEndTime):
        a = ["systemName", "runCasePass", "runCaseFail", "runStartTime", "runEndTime"]
        b = [SystemName, Passrate, Failrate, RunStartTime, RunEndTime]
        c = zip(a, b)
        d = dict(c)
        html = TestCaseRunnerHtml()
        table_td = html.REPORT_TMPL_TOTAL % dict(systemName=d['systemName'],
                                                 runCasePass=d['runCasePass'],
                                                 runCaseFail=d['runCaseFail'],
                                                 runStartTime=d['runStartTime'],
                                                 runEndTime=d["runEndTime"])

        return table_td

    # 表头总数
    def Header(self, Totalcount, failcount, Passcount):
        total_str = '共 %s，通过 %s，失败 %s' % (Totalcount, Passcount, failcount)
        return total_str

    def htmlRunner(self, title_str, SystemName, Passrate, Failrate, RunStartTime, RunEndTime, Totalcount, failcount, Passcount):
        table_tr0 = ''
        table_tr1 = ""
        table_tr2 = ""
        html = TestCaseRunnerHtml()
        table_td = html.analysis(SystemName, Passrate, Failrate, RunStartTime, RunEndTime)
        total_str = html.Header(Totalcount,failcount, Passcount)
        shuju = ExcelUtil()
        cases = shuju.ReadExcelList()
        for case in cases:
            table_td_case = str(html.REPORT_TMPL_CASE % dict(id=case["Index"], apiName=case["InterfaceName"],
                                                             url=case["url"], headers=case["headers"],
                                                             method=case["RequestMethod"], message=case["resquest"],
                                                             statusCode=case["statusCode"],
                                                             responseResult=case["response"],
                                                             runResult=case["Practical"]), )

            table_tr2 += table_td_case
        output = html.HTML_TEMPLATE % dict(title=title_str, total=total_str, table_total=table_td,
                                           table_module=table_tr1,
                                           table_case=table_tr2)
        # 生成html报告
        filename = '{name}测试报告.html'.format(name=SystemName)
        print(filename)
        # 获取report的路径
        dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'report')
        filename = os.path.join(dir, filename)
        with open(filename, 'wb') as f:
            f.write(output.encode('utf8'))


if __name__ == "__main__":
    html = TestCaseRunnerHtml()
    SystemName = "小程序"
    Passrate = "9"
    Failrate = "3"
    RunStartTime = "2022-04-07"
    RunEndTime = "5"
    Totalcount=40
    failcount = 20
    Passcount = 10
    title_str = "接口自动化测试报告"
    html.htmlRunner(title_str, SystemName, Passrate, Failrate, RunStartTime, RunEndTime, Totalcount,failcount, Passcount)

五.正则替换数据

import re


from utils.yamltools import ReadYamlRender


class ReplacePara:
    def __init__(self):
        self.ReadYamlRender = ReadYamlRender()

    def replace_data(self,data):
        r = r"#(.+?)#"  # 注意这个分组()内的内容
        # 判断是否有需要替换的数据
        while re.search(r, data):
            res = re.search(r, data)  # 匹配出第一个要替换的数据
            print('------',res)
            item = res.group()  # 提取要替换的数据内容
            key = res.group(1)  # 获取要替换内容中的数据项
            # 根据替换内容中的数据项去配置文件中找到对应的内容，进行替换
            data = data.replace(item, self.ReadYamlRender.test_get_value(key))

        return data


if __name__ == '__main__':
    data=str({"mobile":"1052153","pwd":"123456","token":"#token#","id":"#id#"})
    #BASE_DIR = Path(__file__).resolve().parent.parent
    #FILE_PATH = Path.joinpath(BASE_DIR, "data", "variables.yaml")
    print('更换后的数据',ReplacePara().replace_data(data))

六.Yaml文件的操作

import yaml
import jinja2
from pathlib import Path


class ReadYamlRender:

    def __init__(self,  content=None):
        if content is None:
            content = {}
        self._yaml_path = "..\\data\\variables.yaml"
        self._content = content

        with open(self._yaml_path, encoding="utf-8") as w:
            self._string_var = w.read()

    @property
    def render(self):
        response = jinja2.Template(self._string_var).render(self._content)
        results = yaml.safe_load(response)
        return results

    # 修改yaml文件中某一个字段的值
    def test_write_yaml_which(self, para, value):

        with open(self._yaml_path, encoding="utf-8") as f:
            doc = yaml.safe_load(f.read())
            print("doc========", doc)
        doc[para] = value
        with open(self._yaml_path, 'w', encoding="utf-8") as f:
            yaml.safe_dump(doc, f, default_flow_style=False, allow_unicode=True)

    # 获取yaml文件中某一个参数的值
    def test_get_value(self, value):
        with open(self._yaml_path, encoding="utf-8") as f:
            doc = yaml.safe_load(f.read())
            print("doc========", doc)
            return str(doc[value])


if __name__ == '__main__':
    # 测试下封装是否正确
    new_data = {"status": 3, "token": "D979442EE8E0B8686CC04C"}
    new_data2 = {"name": "测试"}
    print(ReadYamlRender(new_data).render)
    print(ReadYamlRender(new_data2).render)
    print(ReadYamlRender().test_get_value('phone'))

七.配置参数

# encoding=utf-8

import os

SystemName = "小程序"
title_str = "接口自动化测试报告"

八.用例操作

import ast
import json

import sys
import time
from pathlib import Path
import os
import requests
import datetime

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from utils import ExcelUtil
from utils.DatabaseUtil import DatabaseUtils
from utils.HtmlReport import TestCaseRunnerHtml
from utils.getresponsepara import GetResponsePara
from utils.replacepara import ReplacePara

from utils.yamltools import ReadYamlRender
from config.VarConfig import title_str
from config.VarConfig import SystemName


class Test_Cases:

    def __init__(self):
        self.getResponsePara = GetResponsePara()
        self.ReadYamlRender = ReadYamlRender()
        self.ReplacePara = ReplacePara()
        self.DatabaseUtil = DatabaseUtils()
        self.TestCaseRunnerHtml = TestCaseRunnerHtml()

    def testcases(self):
        global response, data, Practical
        RunStartTime = datetime.datetime.now()
        session = requests.session()
        cases = ExcelUtil.excel.ReadExcelList()
        for case in cases:
            Method = case["RequestMethod"]
            headers = eval(case["headers"])
            if str(case["data"]) != "nan":
                data = eval(case["data"], {"true": True, "false": False, "null": None})
            url = case["url"].strip()
            relation = case["relation"]
            Index = str(case["Index"])
            respectresult = str(case["respectresult"])
            print("respectresult======", respectresult)
            Namedvariable = case["Namedvariable"]
            # 动态EXCEL中某值对应的行 索引和列索引
            res = ExcelUtil.excel.GetExcelRowCol(Index, 'response')
            sta = ExcelUtil.excel.GetExcelRowCol(Index, 'statusCode')
            pra = ExcelUtil.excel.GetExcelRowCol(Index, 'Practical')
            resquest = ExcelUtil.excel.GetExcelRowCol(Index, 'resquest')
            # 每次请求之前去匹配一下字符串获得data
            data = eval(self.ReplacePara.replace_data(str(data)))
            print('请求入参-------:', json.dumps(data))

            # 每次请求之前去匹配一下header
            headers = eval(self.ReplacePara.replace_data(str(headers)))
            print('请求headers-------:', json.dumps(headers))
            print('请求url-------:', url)
            if Method == 'POST':
                response = session.post(url, data=json.dumps(data), headers=headers)
                print('响应结果111111111111:', type(response), response)
                response = response.json()

                print('请求行:', Index)
                print('请求头:', headers)
                print('请求入参:', data)
                print('响应结果:', type(response), response)
                if response['statusCode'] == 0:

                    Practical = 'Pass'
                else:
                    Practical = 'Fail'
                ExcelUtil.excel.writeCell(sta[0], sta[1], response['statusCode'])
            elif Method == 'GET':
                response = session.get(url, headers=headers)
                print(type(response))
                print('请求头:', headers)
                print('响应结果:', type(response), response)
                if respectresult in response.content.decode('utf-8'):
                    Practical = 'Pass'
                    code=0
                else:
                    Practical = 'Fail'
                    code=1
                ExcelUtil.excel.writeCell(sta[0], sta[1], code)
                print('响应结果===:', response.content.decode('utf-8'))
            ExcelUtil.excel.writeCell(resquest[0], resquest[1], str(data))
            ExcelUtil.excel.writeCell(pra[0], pra[1], Practical)

            # 把response写入excel
            ExcelUtil.excel.writeCell(res[0], res[1], str(response))
            if str(relation) != 'nan':
                print('relation', type(relation), str(relation))
                # 特殊处理验证码code,查询redis获取手机验证码并存到yaml中
                if str(relation) == 'code':
                    new_data = self.DatabaseUtil.Redisslect()
                    self.ReadYamlRender.test_write_yaml_which("code", new_data)
                else:
                    # 按照规则拆分relation并返回字典
                    paradict = self.getResponsePara.chaiparam(relation)
                    newkey = Namedvariable.split(';')
                    newdata = []
                    for key in paradict:
                        path = paradict[key]
                        # 获取key对应response中的值
                        new_data = self.getResponsePara.get_para2(response, key, path)
                        newdata.append(new_data)
                        print('KEY', key)
                        print('new_data', new_data)
                        # 把值写入yaml文件
                    print('newkey,newdata', newkey, newdata)
                    # 重新定义变量名,zip可以合并两个list为dict
                    newkeyvalue = dict(zip(newkey, newdata))
                    print('newkeyvalue', newkeyvalue)
                    for key in newkeyvalue:
                        self.ReadYamlRender.test_write_yaml_which(key, newkeyvalue[key])
        RunEndTime = datetime.datetime.now()
        Totalcount, Passcount, failcount, Passrate, Failrate = ExcelUtil.excel.statistical()
        html = TestCaseRunnerHtml()
        html.htmlRunner(title_str, SystemName, Passrate, Failrate, RunStartTime, RunEndTime, Totalcount, failcount,
                        Passcount)


if __name__ == '__main__':
    Test_Cases().testcases()
    print('ok')

九.用例

此套代码可用性很高,后续还会持续优化,希望多指点!