有几个注意的点,excel里面的数据要用双引号
因为:
1、get请求传params需要是dict EXCEL里面的数据拿到的是str,所有要转成dict
json.loads(data)可以将str转成dict 前提是数据里面键值对要用双引号
2、post请求传data需要是str,所以直接拿就好不用转,假如要把dictr转成str,用到的是json.dumps(data)
import requests
import json
import xlrd
import xlsxwriter as xlsxwriter
def test_add():
# https://v0.yiketianqi.com/api?version=v61&appid=48482133&appsecret=4ehQ0ofG&cityid=9090
# data={'version':'v61','appid':'48482133','appsecret':'4ehQ0ofG'}
# url='https://v0.yiketianqi.com/api'
# r=requests.get(url=url,params=data,headers="")
# print(r.text)
# assert '"cityid":"101280101"' in r.text
#测试数据
path_excel=r'C:\Users\xi\PycharmProjects\pytest_allure\data_separate\data.xlsx'
#测试结果
result_excel=r'C:\Users\xi\PycharmProjects\pytest_allure\data_separate\data_result.xlsx'
#创建测试结果excel
xl=xlsxwriter.Workbook(result_excel)
# 创建sheet1测试结果
sheet_result=xl.add_worksheet("测试结果")
#获取测试数据book对象
book=xlrd.open_workbook(path_excel)
print("type(book)",type(book))
#获取测试数据sheet对象
sheet1 = book.sheets()[0]
#测试数据表格行数
nrows = sheet1.nrows
#测试数据表格列数
ncols = sheet1.ncols
for i in range(0,nrows):
a=[]
for j in range(0,ncols):
sheet_result.write_string(i, j, sheet1.cell(i,j).value)
a.append((sheet1.cell(i,j).value))
if a!=[] and i!=0:
print("diaoyong")
if a[2]=='get':
a=get_request(a[1],a[3],a[4],a[5])
if(a):
sheet_result.write_string(i, 5, "pass")
else:
sheet_result.write_string(i, 5, "fail")
elif a[2]=='post':
b=post_request(a[1],a[3],a[4],a[5])
if (b):
sheet_result.write_string(i, 5, "pass")
else:
sheet_result.write_string(i, 5, "fail")
xl.close()
def get_request(url,data,header,response):
headers=""
if header.strip()!="":
headers = json.loads(header)
r=requests.get(url=url,params=json.loads(data),headers=headers)
return response in r.text
def post_request(url,data,header,response):
headers = ""
if header.strip() != "":
headers = json.loads(header)
r=requests.post(url=url,data=data,headers=headers)
return response in r.text
if __name__=="__main__":
test_add()
测试数据:
测试结果: