接口自动化测试-通过读取excel进行接口自动化测试

一、编写测试用例

----按此模板，进行编写

二、配置文件

1、新建一个.ini文件

[localhost]
host = 0.0.0.0
port = 5555


[path]
upload_export_path = D:\\python\\bushu1\\jiekou\\file\\
moban_path = D:\\python\\bushu1\\jiekou\\moban\\
result_path = D:\\python\\bushu1\\jiekou\\core\\result.py

2、新建一个conf.py文件，读取配置文件

# -*- coding:utf-8 -*-
# Time:2022/9/13 16:25
# Author:Xue
# FileName:conf.py
from configparser import ConfigParser


def conf():
    cfg = ConfigParser()

    cfg.read('D:\\python\\bushu1\\jiekou\\config\\conf.ini')

    cfg1 = dict(cfg.items('localhost'))

    return cfg1


def conf1():
    cfg = ConfigParser()

    cfg.read('D:\\python\\bushu1\\jiekou\\config\\conf.ini')

    cfg2 = dict(cfg.items('path'))

    return cfg2

三、读取测试用例excel

# -*- coding:utf-8 -*-
# Time:2022/7/15 15:14
# Author:Xue
# FileName:read_excel.py

import openpyxl

# 读excel数据：测试用例
def read_data(filename, sheetname):
    """

    :param filename:  # 表明
    :param sheetname:  # sheet名
    :return:
    """
    list1 = []  # 新建一个空列表，存放测试用例
    file = openpyxl.load_workbook(filename)  # 加载工作簿
    sheet = file[sheetname]  # 获取工作表sheet
    max_now = sheet.max_row  # 获取最大行数
    for i in range(2, max_now + 1):
        dict1 = dict(
            case_id=sheet.cell(row=i, column=1).value,  # 获取case_id
            headers=sheet.cell(row=i, column=5).value,  # 获取headers地址
            cookies=sheet.cell(row=i, column=6).value,  # 获取cookies传入的参数
            url=sheet.cell(row=i, column=7).value,  # 获取url地址
            data=sheet.cell(row=i, column=8).value,  # 获取data传入的参数
            expect=sheet.cell(row=i, column=9).value  # 获取预期结果
        )
        list1.append(dict1)  # 将每次循环的结果添加到列表
    return list1  # 返回测试用例列表


# cases = read_data('D:\\python\\bushu1\\jiekou\\file\\测试用例模板.xlsx', 'TestUserLogin')  # 调用读取excel文件函数
# print(cases)  # 打印结果

四、请求接口

# -*- coding:utf-8 -*-
# Time:2022/7/15 15:24
# Author:Xue
# FileName:register.py
import requests

# 请求结果函数
def register(url, data, headers, cookies):
    """

    :param cookies:
    :param headers:
    :param url:
    :param data:
    :return:
    """

    re = requests.post(url=url, json=data, headers=headers, cookies=cookies)  # post请求
    response = re.json()  # JSON格式的字符串转化为字典
    return response  # 返回结果

五、运行结果写入excel

# -*- coding:utf-8 -*-
# Time:2022/7/15 15:25
# Author:Xue
# FileName:write_result.py
import time
import openpyxl
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill


# 将实际结果写到excel中
def write_actual_result(filename, sheetname, row, column, final_result):
    """

    :param filename:
    :param sheetname:
    :param row:
    :param column:
    :param final_result:
    """
    wb = openpyxl.load_workbook(filename)
    sheet = wb[sheetname]
    sheet.cell(row=row, column=column).value = final_result  # 获取实际结果
    sheet.cell(row=row, column=column).alignment = Alignment(vertical='center')  # 上下居中
    wb.save(filename)


# 将最终结果写到excel中
def write_result(filename, sheetname, row, column, final_result):
    """

    :param filename:
    :param sheetname:
    :param row:
    :param column:
    :param final_result:
    """
    wb = openpyxl.load_workbook(filename)
    sheet = wb[sheetname]
    sheet.cell(row=row, column=column).value = final_result  # 获取最终结果：pass/failed
    sheet.cell(row=row, column=column).alignment = Alignment(horizontal='center', vertical='center')  # 上下左右居中
    if final_result == 'pass':
        sheet.cell(row=row, column=column).fill = PatternFill('solid', fgColor="00FF00")  # 绿色
    elif final_result == 'failed':
        sheet.cell(row=row, column=column).fill = PatternFill('solid', fgColor='FF0000')  # 红色
    wb.save(filename)


# 将执行时间写到excel中
def write_time(filename, sheetname, row, column):
    """

    :param filename:
    :param sheetname:
    :param row:
    :param column:
    """
    wb = openpyxl.load_workbook(filename)
    sheet = wb[sheetname]
    sheet.cell(row=row, column=column).value = time.strftime("%Y-%m-%d  %H:%M:%S", time.localtime())  # 获取时间
    sheet.cell(row=row, column=column).alignment = Alignment(horizontal='center', vertical='center')  # 上下左右居中
    wb.save(filename)

六、主文件----执行测试用（运行此文件即可）

# -*- coding:utf-8 -*-
# Time:2022/7/15 15:27
# Author:Xue
# FileName:result.py
# noinspection PyCompatibility
from pathlib import Path
from jiekou.core.read_excel import *
from jiekou.core.register import *
from jiekou.core.write_result import *
from jiekou.config.conf import *

# 服务器
# from read_excel import *
# from register import *
# from write_result import *


# 取出备用列表中的url，data、headers、cookies传给接口函数
def test_excel(filename, sheetname):
    for case in cases:
        case_id = case.get('case_id')  # 获取case_id
        url = case.get('url')  # 获取url
        headers = eval(case.get('headers'))
        cookies = eval(case.get('cookies'))
        data = case.get('data')  # 获取data,用eval（）函数可以去掉外面的双引号，直接用内部的值
        data = eval(data.replace("null", "None"))
        expect = eval(case.get('expect'))  # 获取预期结果
        expect_msg = expect.get('message')  # 获取预期结果中的message
        expect_code = expect.get('code')
        real_result = register(url=url, data=data, headers=headers, cookies=cookies)  # 将取出的url和data作为参数传给接口
        real_msg = real_result.get('message')  # 获取实际结果中的message
        real_code = real_result.get('code')
        print('预期结果的message：{}'.format(expect_msg), '预期结果的code：{}'.format(expect_code))
        print('实际结果的message：{}'.format(real_msg), '实际结果的code：{}'.format(real_code))
        # 将实际结果与预期结果作比较
        if real_msg == expect_msg and real_code == expect_code:
            print('第{}条测试用例通过'.format(case_id))
            final_re = 'pass'
        else:
            print('第{}条测试用例不通过'.format(case_id))
            final_re = 'failed'
        # 调用 write_actual_result函数，将实际结果写入到excel
        write_actual_result(filename, sheetname, case_id + 1, 10, str(real_result))
        # 调用write_result函数，将最终测试结果写入到excel
        write_result(filename, sheetname, case_id + 1, 11, final_re)
        # 调用write_time函数，将执行时间写入到excel
        write_time(filename, sheetname, case_id + 1, 12)


if __name__ == "__main__":
    folder_path = Path(conf1().get('upload_export_path'))

    file_list = folder_path.glob('*.xls*')  # 获取文件夹下所有工作簿的文件路径
    for i in file_list:  # 遍历已获取的文件路径
        file_name = i.name  # 提取工作簿的文件
        excel_total = openpyxl.load_workbook(str(folder_path) + '\\' + file_name)  # 打开工作簿
        for sheet_name in excel_total.sheetnames:  # 遍历sheet名
            sheet_names = sheet_name
            cases = read_data(str(folder_path) + '\\' + file_name, sheet_names)
            test_excel(str(folder_path) + '\\' + file_name, sheet_names)