excel用例接口自动化

excel⽤例接⼝⾃动化要求：根据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='',
user='xxx@',
password='xxxxx',
)
smtp.send(to='xxxx@',
cc='xxxx@',
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('完成')。