版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_42846555/article/details/97761484
读取Excel接口信息,来做接口测试,测试结果填写到Excel表格中
测试前表格:
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...')