要求:根据excel中的测试用例,执行接口测试,并将结果录入excel,邮件发送给相关人
思路如下:
1.读取excel,保存测试用例中的内容
2.根据excel中的请求url,method,data,调用接口,并保存返回报文
3.读取返回报文,和检查点对比,不一致则不通过,一致则通过,并保存测试结果
4.将测试结果和保存报文写入excel中
5.测试结束后将excel变成附件发邮件
需要用到xlrd,openpyxl,requests,json模块
这是excelway,py文件,将读取excel用例和修改写入测试结果和返回报文的函数放在这个文件里
import xlrd,openpyxl,json
def readExcel(excel_path):
'''
读取excel表中测试用例的函数
:param excel_path: 传入excel文件路径
:return: 返回该excel中第一个sheet中全部的测试用例
'''
try:
book = xlrd.open_workbook(excel_path) #打开excel
except Exception as e:
print('excel路径错误或文件无法打开')
else:
sheet = book.sheet_by_index(0) #取第一个sheet页
all_test_cases = []
for row_num in range(1,sheet.nrows): #从第2行开始获取所有行excel内容
row=sheet.row_values(row_num)
all_test_cases.append(row) #将excel的每一条测试用例添加到all_test_cases
return all_test_cases
def updateExcel(excel_path,result_list,responses_list):
book = openpyxl.load_workbook(excel_path)
sheet = book.active
for col8 in range(len(result_list)): #将测试结果写入第八列
sheet.cell(col8+2,8,result_list[col8])
for col9 in range(len(responses_list)): #将返回报文写入第九列
sheet.cell(col9+2,9,json.dumps(responses_list[col9],ensure_ascii=False))
book.save(excel_path)
这是HttpRequest.py文件,将post、get请求的操作放在这个文件下,定义为一个类
import requests
class MyRequest:
def __init__(self,url,method='get',data=None,headers=None,is_json=False):
method = method.lower()
self.url = url
self.data = data
self.headers = headers
self.is_json = is_json
if hasattr(self,method):
getattr(self,method)()
def get(self):
try:
req = requests.get(self.url,self.data,headers=self.headers).json()
except Exception as e:
self.response = {"error":"接口请求出错%s"%e}
else:
self.response = req
def post(self):
try:
if self.is_json:
req = requests.post(self.url,json=self.data,headers=self.headers).json()
else:
req = requests.post(self.url, self.data, headers=self.headers).json()
except Exception as e:
self.response = {"error": "接口请求出错%s" % e}
else:
self.response = req
这是mail.py文件,将发送邮件的函数放入文件中
import yagmail
def send_mail():
smtp = yagmail.SMTP(host='smtp.163.com',
user='xxx@163.com',
password='xxxxx',
)
smtp.send(to='xxxx@qq.com',
cc='xxxx@sina.com',
subject='测试报告',
contents='请查收测试报告',
attachments=[r'C:\Users\ght\PycharmProjects\untitled\homework\testcase.xlsx']
)
send_mail()
接口自动化脚本如下,执行脚本时,需导入excelway,HttpRequest,mail
import excelway
import HttpRequest
import mail
import json,threadpool
def get_data_dic(data):
'''
将测试用例中传入参数data'a=sdaf,b=1'这样格式的字符串转化为字典{'a':'1','b':'2'}
:param data:传入测试数据,格式为a=1,b=2
:return:返回字典格式
'''
str1 = []
data_list = data.split(',')
for d in data_list:
str1.append(d.split('='))
return dict(str1)
def res_check(res,res_check):
'''
比对接口返回值是否和预期结果一致,一致则返回“通过”,不一致则返回“失败”
:param res: 接口返回数据
:param res_check: 检查值
:return: 测试结果通过或不通过
'''
res =json.dumps(res,ensure_ascii=False) #字典转化为字符串
res = res.replace('": "',"=").replace('": ',"=") #格式转换为a=1,b=2这样,方便与检查点匹配
res_check = res_check.split(',')
for r in res_check:
if r in res:
pass
else:
return '失败'
return '通过'
def interfaceTest(all_test_cases):
'''
将测试用例逐条执行,获得返回报文和测试结果,并传入updateExcel函数
:param all_test_cases: 传入所有的测试用例的list
'''
responses_list = [] #存返回报文的list
result_list =[] #存测试结果的list
for case in all_test_cases:
try:
test_project = case[0]
test_module = case[1]
url = case[2]
method = case[3]
data = get_data_dic(case[4])
test_description = case[5]
test_check = case[6]
except Exception as e:
return('测试用例格式不正确%s'%e)
else:
response = HttpRequest.MyRequest(url=url,method=method,data=data).response
responses_list.append(response)
res = res_check(response,test_check) #判断返回结果符合检查点
if '通过' in res:
result_list.append('通过')
else:
result_list.append('失败')
excelway.updateExcel(excel_path, result_list, responses_list)
if __name__ == '__main__':
excel_path = 'testcase.xlsx'
all_test_cases = excelway.readExcel(excel_path)
interfaceTest(all_test_cases)
mail.send_mail()
print('完成')