使用openpyxl进行接口测试

根据 http://docs.api.elementwin.com/这个网址来测试登陆

以下是excel

备注：文件名是demo_excel,sheet名是测试登陆

以下是代码

from openpyxl import load_workbook
#Python读取Excel数据
def demo_excel(file_name,sheet_name):
       open_wb=load_workbook(file_name)
       sheet=open_wb[sheet_name]
       sheet_max=sheet.max_row
       data_list=[]
       for i in range(2,sheet_max+1):
           data_dict={}
           data_dict['ID']=sheet.cell(i,1).value
           data_dict['TITLE']=sheet.cell(i,2).value
           data_dict['METHOD']=sheet.cell(i,3).value
           data_dict['URL']=sheet.cell(i,4).value
           data_dict['DATA']=sheet.cell(i,5).value
           data_dict['expeced_errcode'] = sheet.cell(i, 6).value
           data_dict['expeced_errmsg'] = sheet.cell(i, 7).value
           data_dict['Actual_Result'] = sheet.cell(i, 8).value
           data_dict['Test_Result'] = sheet.cell(i, 9).value
           data_list.append(data_dict)
       return data_list
t=demo_excel("C:\\Users\\123456\\Desktop\\demo_excel.xlsx",'测试登陆')
print(t)
 #发起http请求：
import requests
class read_request:
    def http_request(self,url,data,method,cookie=None):
        if method=="get":
            response=requests.get(url,data,cookie)
        else:
            response = requests.post(url,data,cookie)
        res=response.json()#如果写成response.text下边取数据的时候会报错
        return  res
#执行用例
open_wb=load_workbook("C:\\Users\\123456\\Desktop\\demo_excel.xlsx")
sheet=open_wb['测试登陆']
for item in t:
    print('正在执行第{}次用例{}'.format(item['ID'],item['TITLE']))
    url=item['URL']
    data = item['DATA']
    method = item['METHOD']
    res = read_request().http_request(url,eval(data),method)
    print(res)
#返回请求的实际结果：
    sheet.cell(item['ID']+1,8).value=str(res)
#结果比对并且记录测试结果：
    if str(res['errcode'])==str(item['expeced_errcode']) and str(res['errmsg'])==str(item['expeced_errmsg']):
        Test_Result='PASS'
    else:
        Test_Result='FAIL'
    sheet.cell(row=item['ID']+1,column=9).value=Test_Result
open_wb.save("C:\\Users\\123456\\Desktop\\demo_excel.xlsx")#注意：这边一定不要写成excel.save('demo_excel.xlsx')

注意点：

1. result = response.json() 和 result = json.loads(response.text)的不同： #requests中response.json()方法等同于json.loads（response.text）方法

2. open_wb.save("C:\\Users\\123456\\Desktop\\demo_excel.xlsx")#注意：这边一定不要写成excel.save('demo_excel.xlsx')

3. cookie作为全局变量

import requests
cookie = None
def http_request(req_url, req_data, method):
    global cookie
    if method == 'get':
        response = requests.get(req_url, req_data, cookies=cookie)
    else:
        response = requests.post(req_url, req_data, cookies=cookie)

4. 知识点：

xiaoming_dict ={"name":"小明",
                "qq":"123456",
                "phone":"10086"}
#迭代吧遍历字典
# 变量k是每一次循环中，获取到的键值对的key
for k in xiaoming_dict:
    print("%s-%s"%(k,xiaoming_dict[k]))