python+requests读取Excel接口信息，来做接口测试，测试结果填写到Excel表格中

读取Excel接口信息，来做接口测试，测试结果填写到Excel表格中
升级加强版：https://blog.csdn.net/qq_42846555/article/details/101566568

测试前表格：

import os
import time
import requests
import json
from openpyxl  import load_workbook
import logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


class ReadAPI():

    def ReadExel(self,dir):
        wb = load_workbook(dir)
        # 获取所有表名
        sheet_names = wb.sheetnames  # 得到工作簿的所有工作表名 结果： ['Sheet1', 'Sheet2', 'Sheet3']
        # 根据表名打开sheet表
        sheet1 = wb[sheet_names[0]]  # 打开第一个 sheet 工作表

        # 获取C列的所有数据
        list_sheet1_column_E = []  # 请求参数
        list_sheet1_column_B = []  # 请求地址
        list_sheet1_column_C = []  # 请求方式
        list_sheet1_column_A = []  # 请求名称项
        list_sheet1_column_F = []  # 请求名称项

        for b, e, c, a,f in zip(sheet1["B"], sheet1["E"], sheet1["C"], sheet1["A"],sheet1["F"]):
            list_sheet1_column_E.append(e.value)
            list_sheet1_column_B.append(b.value)
            list_sheet1_column_C.append(c.value)
            list_sheet1_column_A.append(a.value)
            list_sheet1_column_F.append(f.value)
        row = 2
        for url, method, str_param, testname,expect in zip(list_sheet1_column_B[1:], list_sheet1_column_C[1:], list_sheet1_column_E[1:], list_sheet1_column_A[1:],list_sheet1_column_F[1:]):
            try:
                ReadAPI.verdict(row,url, str_param,testname,sheet1,expect,method)  #读写excel
            except:
                logger.error('《' + str(testname) + '》项，接口地址或入参异常！！！')
                sheet1.cell(row=row, column=7, value='error')  # 响应结果
                sheet1.cell(row=row, column=8, value='error')  # 请求时间
                sheet1.cell(row=row, column=9, value='error')  # 状态码
                sheet1.cell(row=row, column=10, value='error')  # 判断通过

            row+=1
        try:
            wb.save(dir)
            logger.info('测试数据保存成功！！！')
        except:
            logger.error('保存失败，可能Excel文件未关闭，请关闭Excel文件后重新测试')

    def verdict(self,row,url, str_param,testname,sheet1,expect,method):

        if method=='get':
            param = eval(str_param)  # Exel读出来的数据类型是字符串，而get请求的入参必须是字典类型，post请求的入参是json字符串类型
            API_data = ReadAPI.get(url, param, testname)
            ReadAPI.writedata(API_data,expect,sheet1,row,testname)

        elif method=='post':
            API_data = ReadAPI.post(url, str_param, testname)
            ReadAPI.writedata(API_data, expect, sheet1, row, testname)


    def get(self, url, param,testname):
        try:
            r = requests.get(url, params=param, timeout=1)
            r.raise_for_status()  # 如果响应状态码不是 200，就主动抛出异常
        except requests.RequestException as e:
            print(e)
        else:
            js = json.dumps(r.json())
            #print( '请求项名称：'+testname+'、请求响应时间：'+str(r.elapsed.total_seconds()),'、请求状态：'+str(r.status_code))
            return [r.json(), r.elapsed.total_seconds(),js,r.status_code]

    def post(self, url, param,testname):

        try:
            r = requests.post(url, data=param, timeout=1)
            r.raise_for_status()  # 如果响应状态码不是 200，就主动抛出异常
        except requests.RequestException as e:
            print(e)
        else:
            js = json.dumps(r.json())
            #print('请求项名称：' + testname + '、请求响应时间：' + str(r.elapsed.total_seconds()), '、请求状态：' + str(r.status_code))
            return [r.json(), r.elapsed.total_seconds(), js, r.status_code]

    def find_value(self,dir_data, fvalue):  #封装个在查字典中的查找value
        yesOrno = False
        for key in dir_data.keys():
            if (dir_data[key] == fvalue):

                yesOrno = True
                break

        return (yesOrno)

    def writedata(self,API_data,expect,sheet1,row,testname):
        API_data[2] = API_data[2].replace("'", '"')  # 替换"'", '"'
        dict_data = json.loads(API_data[2])  # 转python字典
        if ReadAPI.find_value(dict_data, expect) == True:  # ReadAPI.get(url, param,testname)[3])==200:
            sheet1.cell(row=row, column=7, value=str(API_data[0]))  # 响应结果
            sheet1.cell(row=row, column=8, value=API_data[1])  # 请求时间
            sheet1.cell(row=row, column=9, value=int(API_data[3]))  # 状态码
            sheet1.cell(row=row, column=10, value="pass")  # 判断通过
            logger.info('《' + str(testname) + '》项，响应通过、响应时间：' + str(API_data[1]) + '、状态码：' + str(API_data[3]))
        else:
            sheet1.cell(row=row, column=10, value="Fail")  # 判断失败
            sheet1.cell(row=row, column=8, value=API_data[1])  # 请求时间
            sheet1.cell(row=row, column=7, value=str(API_data[0]))  # 响应结果
            sheet1.cell(row=row, column=9, value=int(API_data[3]))  # 状态码
            logger.error('《' + str(testname) + '》项，响应错误、响应时间：' + str(API_data[1]) + '、状态码：' + str(API_data[3]))

if __name__ == '__main__':
    ReadAPI=ReadAPI()
    ##dir =os.path.join(os.getcwd()+'\接口测试用例.xlsx')
    dir ='接口测试用例.xlsx'

    ReadAPI.ReadExel(dir)



    input('Press Enter to exit...')

测试后表格：