17、接口自动化之读取excel测试数据（含header之方法三）

a.首先编写请求函数

import requests

class HttpRequest():
    def http_request(self, url, data, method, cookie=None):
        if method.lower() == "get":
            # verify=False忽略证书  ssl
            res = requests.get(url, data, cookies=cookie, verify=False)
            return res
        elif method.lower() == "post":
            res = requests.post(url, data, cookies=cookie, verify=False)
            return res  # 返回的是一个消息实体
        else:
            raise  Exception('不支持的请求')

b.存在依赖关系，编写反射函数 

class GetData:
    Cookie = '小郭'

setattr(GetData,'Cookie','小黄')
print(GetData.Cookie)

c.读取excel函数

from openpyxl import load_workbook

class DoExcel:
    def __init__(self,file_name,sheet_name):
        self.file_name=file_name
        self.sheet_name=sheet_name


    def get_header(self):
        """
        获取第一行的标题行
        """
        wb=load_workbook(self.file_name)
        sheet=wb[self.sheet_name]
        #存储标题行
        header=[]
        for j in range(1,sheet.max_column+1):
            header.append(sheet.cell(1,j).value)
        return header



    def get_data(self):
        """
        根据嵌套循环读取数据

        """
        wb=load_workbook(self.file_name)
        sheet=wb[self.sheet_name]

        #拿到的header 是一个列表  索引是从0开始的
        header=self.get_header()
        test_data=[]

        for i in range(2,sheet.max_row+1):
            sub_data={}
            for j in range(1,sheet.max_column+1):
                #header是列表，索引从0开始的，j从1开始的，所以要减一
                sub_data[header[j-1]]=sheet.cell(i,j).value

            test_data.append(sub_data)

        #返回获取到的数据
        return test_data

if __name__ == '__main__':
    print(DoExcel(r"D:\PycharmProjects\class_01\testcase_02\test.xlsx",'test').get_data())

d、编写测试用例

import unittest
from testcase_01.test_http import HttpRequest
from testcase_01.test_reflect import GetData




# 登录接口
class TestHttp(unittest.TestCase):
    def setUp(self):
        print("我已经开始测试用例了")
    #超继承
    #传递的参数必须与excel中的表单顺序一一对应，方便操作
    def __init__(self,methodName,url,data,method,expected):#通过初始化函数传参
        super(TestHttp,self).__init__(methodName)#保留父类的方法
        self.url = url
        self.data = data
        self.method = method
        self.expected = expected

    def test_api(self):
            res = HttpRequest().http_request(self.url, self.data, self.method,getattr(GetData,'Cookie'))
            if res.cookies:
                setattr(GetData, 'Cookie', 'res.cookies')
            try:
                self.assertEqual(self.expected, res.json()['code'])
            except Exception as e:
                print("test_api's error is {0}".format(e))
                raise e
            print(res.json())
    def tearDown(self):
        print("我已经执行完毕测试用例了")

f.读取ECEL添加测试集输出测试报告

# ..成功  F失败 1代码错误
import unittest
import HTMLTestRunner
import time
from testcase_01.test_case import TestHttp  # 具体到类名
from testcase_02.do_excel import DoExcel


test_data = DoExcel(r"D:\PycharmProjects\class_01\testcase_02\test.xlsx", 'test')
suite = unittest.TestSuite()  # 存储用例
#根据行数进行遍历
for i in range(1,test_data.max_row+1):  # 遍历是为了创建实例
    #数据类型转换     suite.addTest(TestHttp('test_api',test_data.get_data(i,1),test_data.get_data(i,2),test_data.get_data(i,3),test_data.get_data(i,4)))  # 实例方式去加载用例

if __name__ == "__main__":
    time1 = time.strftime("%Y-%m-%d %H-%M-%S")
    name1 = time1 + "report.html"

with open(name1, 'wb') as file:
    runner = HTMLTestRunner.HTMLTestRunner(
        stream=file,
        title="单元测试报告",
        description="测试用例执行统计1",
        verbosity=2
    )
    runner.run(suite)