最近在研究pytest，集合所学与资料写一个Excel数据驱动的模型，目前只能读取数据，返回的是list列表，支持xls，xlsx。
包
首先安装包
- xlrd只支持xls
- openpyxl只支持xlsx
pip install xlrd
pip install openpyxl
接口用例设计
xlrd解读
这些方法是xlrd的常用方法
import xlrd
filename = 'attachment/接口自动化用例模板.xls'
work_book = xlrd.open_workbook_xls(filename, formatting_info=True)
# print(work_book.sheet_names()) # 获取所有sheet页的名字
# ['Sheet1', 'Sheet2', 'Sheet3']
# 通过名字获取单个sheet页内容
sheet = work_book.sheet_by_name('Sheet1')
# work_book.sheet_by_index(0) # 通过索引获取单个sheet页内容
# 获取第一列的数据
print(sheet.col_values(0))
# ['编号', 'case001', 'case002', 'case003', 'case004', 'case005']
# 获取第一行的数据, 返回给定行中单元格值的一个切片。
print(sheet.row_values(0))
# ['编号', '模块', '接口名称', '优先级', '标题', '前置条件', 'url', 'method', '参数', '预期结果', '描述']
# Cell对象有三个属性:“ctype”是一个int型，“value”(取决于“ctype”)和“xf_index”。如果打开工作簿时没有启用“formatting_info”，则“xf_index”将为“None”。
print(sheet.cell(0, 0))
# text:'编号' (XF:68)
print(sheet.cell(0, 0).xf_index)
# 68
print(sheet.cell(0, 0).ctype)
# 1
print(sheet.cell(0, 0).value)
# 编号
# 获取sheet中的全部数据
for col in range(len(sheet.col_values(0))):
print(sheet.row_values(col))
# ['编号', '模块', '接口名称', '优先级', '标题', '前置条件', 'url', 'method', '参数', '预期结果', '描述']
# ['case001', '', '', '', '', '', 'https://www.baidu.com', 'get', '{\n "method": "get",\n "url": "https://www.baidu.com",\n "headers": {},\n "params": {}\n}', '', '']
# ['case002', '', '', '', '', '', 'https://www.bing.com', 'get', '{\n "method": "get",\n "url": "https://www.bing.com",\n "headers": {},\n "params": {}\n}', '', '']
# ['case003', '', '', '', '', '', 'https://www.sougou.com', 'get', '{\n "method": "get",\n "url": "https://www.sougou.com",\n "headers": {},\n "params": {}\n}', '', '']
# ['case004', '', '', '', '', '', 'https://www.bilibili.com/', 'get', '{\n "method": "get",\n "url": "https://www.bilibili.com/",\n "headers": {},\n "params": {}\n}', '', '']
# ['case005', '', '', '', '', '', 'https://www.d53px.com/', 'get', '{\n "method": "get",\n "url": "https://www.d53px.com/",\n "headers": {},\n "params": {}\n}', '', '']
openpyxl读取.xlsx文件数据
只写读取方法，openpyxl没办法直接读取整行数据，需要自己写
from openpyxl import load_workbook
filename = 'attachment/接口自动化用例模板.xlsx'
workbook = load_workbook(filename)
# 获取全部数据，不分sheet页
sheet = workbook.active
# 获取行数
rows_len = sheet.max_row
# 获取列数
cols_len = sheet.max_column
# 获取单个sheet页数据
sheet1 = workbook['Sheet1']
# 获取行数
print(sheet1.max_row)
# 获取列数
print(sheet1.max_column)
# 获取单个单元格的数据, 这里是从 1 开始，0会报错
print(sheet.cell(1, 1).value)
# 获取sheet页的全部数据
row_list = []
for row in range(1, rows_len + 1):
col_list = []
for col in range(1, cols_len + 1):
col_list.append(sheet.cell(row, col).value)
row_list.append(col_list)
print(row_list)
# [['编号', '模块', '接口名称', '优先级', '标题', '前置条件', 'url', 'method', '参数', '预期结果', '描述', 'JSON数据传参json', '期望结果', '响应预期结果'],
# ['case001', None, None, None, None, None, 'https://www.baidu.com', 'get',
# '{\n "method": "get",\n "url": "https://www.baidu.com",\n "headers": {},\n "params": {}\n}', None, None,
# None, None, None], ['case002', None, None, None, None, None, 'https://www.bing.com', 'get',
# '{\n "method": "get",\n "url": "https://www.bing.com",\n "headers": {},\n "params": {}\n}',
# None, None, None, None, None],
# ['case003', None, None, None, None, None, 'https://www.sougou.com', 'get',
# '{\n "method": "get",\n "url": "https://www.sougou.com",\n "headers": {},\n "params": {}\n}', None, None,
# None, None, None], ['case004', None, None, None, None, None, 'https://www.bilibili.com/', 'get',
# '{\n "method": "get",\n "url": "https://www.bilibili.com/",\n "headers": {},\n "params": {}\n}',
# None, None, None, None, None],
# ['case005', None, None, None, None, None, 'https://www.d53px.com/', 'get',
# '{\n "method": "get",\n "url": "https://www.d53px.com/",\n "headers": {},\n "params": {}\n}', None, None,
# None, None, None]]
excel用例筛选器
在实际的测试中会单独运行某条数据或者指定几条数据，pytest中无法运行指定的用例
@pytest.mark.parametrize('database', [1, 2, 3, 4, 5, 6])只能运行接口层面的用例，因此只能让测试人员手动来筛选测试用例
下面分享一下思路
判断是否是json字符串
def is_json(msg: str):
if msg is None:
return
if isinstance(msg, str):
try:
json.loads(msg)
except JSONDecodeError:
return False
return True
else:
raise ValueError('类型错误!请传str类型的参数')
将两个列表或数据合并为dict
def merge_to_dict(list_1, list_2):
"""
将集合或列表合并成字典
:param list_1: [],()
:param list_2: [],()
:return:
"""
if len(list_1) != len(list_2):
return ValueError('list_1, list_2长度必须相同')
dic = dict(zip(list_1, list_2))
return dic
第一次筛选
首先进行第一阶段的筛选，根据用例的第一列进行筛选。
例如：case001使用前缀case进行筛选，使用in函数来进行判断筛选
for col_name in ['编号', 'case001','case002', ...]:
if case in 编号名:
# 第一次筛选后进行下次筛选
pass
else:
pass
第二次筛选
如果'all'在select_case中返回 Excel表的所有数据，否则就返回指定的行，可以看代码
""" 用例过滤器 :param select_case: 用例筛选器，运行指定的用例 1.全部执行, 包含 'all' ['all'] 2.分段执行 [ 'case001-case006' ] 3.随机某一个执行 ['case001', 'case003', 'case005'] 4.混合执行 ['case001', 'case003-case006', 'case009'] :param case_name: 用例筛选器，第一层筛选 例：'case' :param table_title: 第一行的名称，进行列数据的筛选 :return: Excel表的整行资料 """
def get_select_case(self, select_case, case_name, table_title):
"""
用例过滤器
:param select_case: 用例筛选器，运行指定的用例
1.全部执行, 包含 'all' ['all']
2.分段执行 [ 'case001-case006' ]
3.随机某一个执行 ['case001', 'case003', 'case005']
4.混合执行 ['case001', 'case003-case006', 'case009']
:param case_name: 用例筛选器，第一层筛选 例：'case'
:param table_title: 第一行的名称，进行列数据的筛选
:return: Excel表的整行资料
"""
# 需要返回的数据
select_list = []
if 'all' in select_case:
# 返回所有数据
select_list = table_title
else:
# ['case001', 'case003-case006', 'case009']
for one in select_case:
# 用-分割数据
if '-' in one:
start, end = one.split('-')
if case_name in start:
start = re.findall('\d+', start)[-1]
if case_name in end:
end = re.findall('\d+', end)[-1]
for i in range(int(start), int(end) + 1):
select_list.append(case_name + f'{i:0>3}')
else:
if case_name in one:
select_list.append(one)
else:
select_list.append(case_name + f'{one:0>3}')
return select_list
进行第三次筛选
第一列的单元格必须包含
case，并且必须在第二次筛选中
idx = 0
index_list = []
# 循环第一列数据
for one_col in work_sheet.col_values(0):
if case_name in one_col and one_col in select_list:
get_col_data = []
for colx in col_index_list:
# 获取单元格数据
index = work_sheet.cell_value(idx, colx)
# 判断是否是JSON格式
if is_json(index):
index = json.loads(index)
get_col_data.append(index)
index_list.append(get_col_data)
idx += 1
完整的代码
import json
import re
from json import JSONDecodeError
import xlrd
from openpyxl import load_workbook
def is_json(msg: str):
if msg is None:
return
if isinstance(msg, str):
try:
json.loads(msg)
except JSONDecodeError:
return False
return True
else:
raise ValueError('类型错误!请传str类型的参数')
def merge_to_dict(list_1, list_2):
"""
将集合或列表合并成字典
:param list_1: [],()
:param list_2: [],()
:return:
"""
if len(list_1) != len(list_2):
return ValueError('list_1, list_2长度必须相同')
dic = dict(zip(list_1, list_2))
return dic
class Excel:
def __init__(self, path: str):
self.excel_path = path
self.path_type = self.excel_path.split('.')[-1]
if self.path_type == 'xls':
self.work_book = xlrd.open_workbook_xls(self.excel_path, formatting_info=True)
elif self.path_type == 'xlsx':
self.work_book = load_workbook(self.excel_path)
else:
raise TypeError(f'文件类型错误, {self.path_type}不是xls, xlsx类型的文件')
def get_select_case(self, select_case, case_name, table_title):
"""
用例过滤器
:param select_case: 用例筛选器，运行指定的用例
1.全部执行, 包含 'all' ['all']
2.分段执行 [ 'case001-case006' ]
3.随机某一个执行 ['case001', 'case003', 'case005']
4.混合执行 ['case001', 'case003-case006', 'case009']
:param case_name: 用例筛选器，第一层筛选
:param table_title:
:return:
"""
select_list = []
if 'all' in select_case:
select_list = table_title
else:
for one in select_case:
if '-' in one:
start, end = one.split('-')
if case_name in start:
start = re.findall('\d+', start)[-1]
if case_name in end:
end = re.findall('\d+', end)[-1]
for i in range(int(start), int(end) + 1):
select_list.append(case_name + f'{i:0>3}')
else:
if case_name in one:
select_list.append(one)
else:
select_list.append(case_name + f'{one:0>3}')
return select_list
def get_xls_sheet_data(self, *col_names, sheet_name: str, case_name: str = '', select_case: list = ['all']):
"""
获取单个sheet的数据
:param sheet_name: excel单元页名称
:param case_name: 用例筛选器，第一层筛选
:param col_names: 需要查的列名
:param select_case: 用例筛选器，运行指定的用例
1.全部执行, 包含 'all' ['all']
2.分段执行 [ 'case001-case006' ]
3.随机某一个执行 ['case001', 'case003', 'case005']
4.混合执行 ['case001', 'case003-case006', 'case009']
:return:
"""
# 指定sheet页
work_sheet = self.work_book.sheet_by_name(sheet_name)
select_list = self.get_select_case(select_case, case_name, table_title=work_sheet.col_values(0))
col_index_list = []
for col_name in col_names:
col_index_list.append(work_sheet.row_values(0).index(col_name))
idx = 0
index_list = []
# 循环第一列数据
for one_col in work_sheet.col_values(0):
if case_name in one_col and one_col in select_list:
get_col_data = []
for colx in col_index_list:
# 获取单元格数据
index = work_sheet.cell_value(idx, colx)
# 判断是否是JSON格式
if is_json(index):
index = json.loads(index)
get_col_data.append(index)
index_list.append(get_col_data)
idx += 1
return index_list
def get_xlsx_sheet_data(self, *col_names, sheet_name: str, case_name: str = '', select_case: list = ['all']):
# 指定sheet页
sheet = self.work_book[sheet_name]
# 获取行数
rows_len = sheet.max_row
# 获取列数
cols_len = sheet.max_column
# 首行数据（标题）
table_title = []
for col in range(1, cols_len + 1):
table_title.append(sheet.cell(1, col).value)
# 首列数据
row_value = []
for row in range(1, rows_len + 1):
row_value.append(sheet.cell(row, 1).value)
# 第一次过滤用例
select_list = self.get_select_case(select_case, case_name, row_value)
# col_names 根据指定的标题名获取指定的列
title_list = []
for col_name in col_names:
title_list.append(table_title.index(col_name))
# 第二次过滤用例
table_value = []
inx = 1
for one_value in row_value:
if case_name in one_value and one_value in select_list:
row_list = []
for col in title_list:
# 获取单元格数据
index = sheet.cell(inx, col + 1).value
# 判断字符串是否是JSON
if is_json(index):
index = json.loads(index)
row_list.append(index)
table_value.append(row_list)
inx += 1
return table_value
def read_sheet(self, *col_names, sheet_name: str, case_name: str = '', select_case: list = ['all']):
if self.path_type == 'xls':
return self.get_xls_sheet_data(*col_names, sheet_name=sheet_name, case_name=case_name, select_case=select_case)
else:
return self.get_xlsx_sheet_data(*col_names, sheet_name=sheet_name, case_name=case_name, select_case=select_case)
if __name__ == '__main__':
excel = Excel('attachment/接口自动化用例模板.xls')
excel_title = ['参数']
print(excel.read_sheet(*excel_title, sheet_name='Sheet1', case_name='case', select_case=['case001']))
返回值：
返回值为list，如果需要转为dict，可以使用merge_to_dict在这里插入代码片
318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
