接口自动化数据存储与读取（2）

使用内置函数读取

import re


class RecordFirstLinePlace:
    CordPlace = {} # 记录每一行title在列的位置，使用索引取值时会使用其中记录的数据


class TableTreating:
    def __init__(self, filename):
        self.filename = filename
        self.filepath = filename
        self.wb = load_workbook(self.filepath)
        self.test_data = []

    def read_excel(self, sheet_name):
        work_sheet_data = self.wb[sheet_name].rows
        work_sheet_title = [title_value.value for title_value in next(work_sheet_data)]
        title_place = [i for i in range(1, len(work_sheet_title) + 1)]
        RecordFirstLinePlace.CordPlace[sheet_name] = dict(list(zip(work_sheet_title, title_place)))
        return work_sheet_data, work_sheet_title

    def get_all_data(self, config_var):
        sheet_names = self.wb.sheetnames + ['others']
        model = config_var.get('mode').get(self.filename)
        if model.get('others') == 'skip':
            sheet_name_dict = model
        else:
            sheet_name_dict = {sheet_name: 'all' for sheet_name in sheet_names}
            sheet_name_dict.update(model)
        for key in sheet_name_dict:
            if key not in sheet_names:
                raise KeyError(f'sheetName = {key} is not in {self.filepath}, please check run_model key is right')
        for sheet_name, model in sheet_name_dict.items():
            if model == 'all':
                sheet_row_generator, sheet_title = self.read_excel(sheet_name)
                test_data_list = list(map(lambda row_data:
                                          {**dict(zip(sheet_title, [cell.value for cell in row_data])),
                                           'sheetName': sheet_name}, list(sheet_row_generator)))
                self.test_data += test_data_list
            elif isinstance(model, list):
                sheet_row_generator, sheet_title = self.read_excel(sheet_name)
                all_row_list = list(sheet_row_generator)
                row_list = [all_row_list[index-1] for index in model]
                test_data_list = list(map(lambda row_data:
                                          {**dict(zip(sheet_title, [cell.value for cell in row_data])),
                                           'sheetName': sheet_name}, row_list))
                self.test_data += test_data_list
            elif not model or sheet_name=='others':
                pass
            else:
                raise ValueError(f'run_model={model}, value must be "all" or list, list just contains int value')
        return self.test_data

    def get_index_data(self, index):
        index_list = index.split('.')
        work_sheet_data, work_sheet_title = self.read_excel(index_list[0])
        title_place = RecordFirstLinePlace.CordPlace[index_list[0]][index_list[1]]
        data_object = [[cell_value for cell_value in row] for row in work_sheet_data]
        split_place = eval(index_list[2]) if re.findall(r'^\d+$', index_list[2]) else index_list[2]
        index_row_data = list(filter(lambda x: x[title_place - 1].value == split_place, data_object))
        index_row_data_value = [[cell.value for cell in row] for row in index_row_data]
        test_data = list(map(lambda x: {**dict(zip(work_sheet_title, x)), 'sheetName':index_list[0]}, index_row_data_value))
        return test_data

run_model数据记录在配置文件可依据手动配置每个数据表的用例读取方式，间接控制用例执行方式
yaml数据读取
安装第三方库：pip install yaml-config -i https://pypi.douban.com

import yaml

with open('config.yaml', 'r', encoding='utf8') as f:
    content = f.read()
res = yaml.load(content, yaml.FullLoader)
print(res)
>>>{'model': {'FirstRoom.xlsx': {'others': None, 'sheet_one': 'all'}}}

扩展：读取模式
others：非表单名称，控制未做特殊说明的其他表单执行方式，当等于skip时表示未记录表单用例全部跳过，否则其他表单用例全部读取。
表单名：值为all时全部执行，为列表[2,3,4]时读取caseid=2,3,4的测试用例，为None时跳过
① 调用方法读取excel数据，读取sheet_one表单所有测试数据

run_model = {'mode': {'FirstRoom.xlsx': {'others': None, 'sheet_one': 'all'}}}
res = TableTreating('FirstRoom.xlsx').get_all_data(run_model)
print(res)

② 读取sheet_one表单case_id=2,3

run_model = {'mode': {'FirstRoom.xlsx': {'others': None, 'sheet_one': [2,3]}}}
res = TableTreating('FirstRoom.xlsx').get_all_data(run_model)
print(res)

③ 索引取值， 取表中某个表单列下某个值得行数据，如索引：sheet_one.CaseId.2，读取改文件下sheet_one表单中列名为CaseId值为2的一行数据，若该列有多个值为2则会全部读取

res = TableTreating('FirstRoom.xlsx').get_index_data('sheet_one.CaseId.2')
print(res)
>>>[{'CaseId': 2, 'url': '/settings/user/info', 'CaseName': '查询当前用户信息', 'yapi_id': 1392, 'req_query': '{}', 'req_body': 'None', 'saveKey': None, 'jsonDone': '["message"]', 'expect': '["操作成功"]', 'sheetName': 'sheet_one'}]
res = TableTreating('FirstRoom.xlsx').get_index_data('sheet_one.url./settings/user/info')
print(res)
>>>[{'CaseId': 2, 'url': '/settings/user/info', 'CaseName': '查询当前用户信息', 'yapi_id': 1392, 'req_query': '{}', 'req_body': 'None', 'saveKey': None, 'jsonDone': '["message"]', 'expect': '["操作成功"]', 'sheetName': 'sheet_one'}]