登录练习（for循环、类、函数、excel读取和写入）

1.main.py

from demo16testMmethod import *
import ast

if __name__ == '__main__':
    #创建excel类实例
    excel = HandleEcxel(workbook='testCase.xlsx', sheet='sheet1')

    #读取excel 测试数据
    case_datas = excel.get_data_dict()

    #创建登录类实例
    login = LoginCase()

    #执行每一个测试用例
    for case in case_datas:
        res = login.user_login(url=case['url'], data=ast.literal_eval(case['data']))

        #期望结果和实际结果比对
        result = login.login_check(expected_data=ast.literal_eval(case['expected_data']),
                                   response_data={'code': res['code'], 'msg': res['msg']})

        #写入结果到excel
        excel.write_result(row=case['id'] + 1, column=7, result=result)

2.测试方法.py

import requests
from openpyxl import load_workbook
import pprint


class LoginCase:
    def __init__(self):
        self.headers = {"X-Lemonban-Media-Type": "lemonban.v1", "Content-Type": "application/json"}

    # 用户登陆
    def user_login(self, url, data):
        """
        :param url: 接口地址
        :param data: 接口参数
        :return:
        """
        # url = 'http://api.lemonban.com/futureloan/member/login'
        # data = {'mobile_phone': '18820992515', 'pwd': 'Aa123456'}
        res = requests.post(url=url, json=data, headers=self.headers)
        # print(res.json())
        return res.json()

    # 登陆断言
    def login_check(self, expected_data, response_data):
        """
        :param expected_data: 预期结果
        :param response_data: 响应结果
        :return:
        """
        # actual_data ={'code':response_data['code'],'msg':response_data['msg']}
        if expected_data == response_data:
            return 'Pass'
        else:
            return 'Failed'


class HandleEcxel:
    # 初始化excel表名和sheet名
    def __init__(self, filepath, sheet):
        self.filepath = filepath
        self.workbook = load_workbook(filename=filepath)
        self.sheetname = self.workbook[sheet]

    # 获取data
    def get_data(self):
        all_datas = list(self.sheetname.iter_rows(values_only=True))  #返回的值需要转成list
        #将title和数据分开
        title = all_datas[0]
        datas = all_datas[1:]
        return title, datas

    # 把data拼接成对应的字典
    def get_data_dict(self):
        title, datas = self.get_data()
        res_list = []
        for case in datas:
            data_dict = dict(zip(title, case))
            res_list.append(data_dict)
        self.__close_excel()
        return res_list

    def write_result(self, row, column, result=None):
        self.sheetname.cell(row=row, column=column).value = result
        self.__save_excel()
        self.__close_excel()

    def __save_excel(self):
        self.workbook.save(filename=self.filepath)

    def __close_excel(self):
        self.workbook.close()

3.测试数据.xlsx