python+unittest+excel 接口自动化测试(xlrd , xlsxwriter)

本文主要是介绍怎么把测试数据存放到excel，接口读取测试，然后将测试时结果写入excel

1. excel处理

import xlrd
import xlsxwriter

class Excel:
    def __init__(self, mode, file):
        #读取excel
        if mode=="r":
            self.workbook = xlrd.open_workbook(file)
            #获取所有sheet
            self.sheet_names = self.workbook.sheet_names()
            #装在所有的数据
            self.list_data = []
        #写入excel
        elif mode=="w":
            self.workbook = xlsxwriter.Workbook(file)
    
    def read(self, sheet_name):
        sheet = self.workbook.sheet_by_name(sheet_name)
        #获取行数
        rows = sheet.nrows
        #根据行数读取
        for i in range(1,rows):
            #获取每行内容
            row_valus = sheet.row_values(i)
            #将每行添加到数据列表
            self.list_data.append(row_valus)
        return self.list_data

    def write(self, data, sheet_name):
        # 设置报告格式
        sheet = self.workbook.add_worksheet(sheet_name)
        # 每行的宽度
        sheet.set_column('A:Q', 30)
        cell_format = self.workbook.add_format({'bold': True})
        sheet.set_row(0, 20, cell_format)
        # 红色
        red = self.workbook.add_format({'bg_color': 'red', 'color': 'white'})
        # 绿色
        green = self.workbook.add_format({'bg_color': 'green', 'color': 'white'})
        for i in range(len(data)):
            data_keys = data[i].keys()
            for (j, key) in zip(range(len(data_keys)),data_keys):
                # 进行用例结果的背景颜色更改 不同状态的用例 不同颜色
                if data[i]["result"]=="fail":
                    sheet.write(i, j, str(data[i][key]), red)
                elif data[i]["result"] == 'pass':
                    sheet.write(i, j, str(data[i][key]), green)
                else:
                    sheet.write(i, j, str(data[i][key]))
        self.workbook.close()   #如果不关闭，则无法写入内容

2.有道词典爬虫认证

import requests
import hashlib
import time
import random

# var t = n.md5(navigator.appVersion),对应浏览器版本
t = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36"

session = requests.Session()
def get_auth_data(keyword):
    bv = hashlib.md5(bytes(t,encoding="utf-8")).hexdigest()
    #b4046c447614dcbe06d949fe3062a42a
    # 70244e0061db49a9ee62d341c5fed82a
    r = str(int(round(time.time(),3)*1000))
    ts = r      #1585970420994
    i = ts + str(random.randint(1,10))
    salt = i #15859705441292
    sign = hashlib.md5(bytes("fanyideskweb" + keyword + salt +"Nw(nmmbP%A-r6U3EUn]Aj",encoding="utf-8")).hexdigest()
    return bv,ts,salt,sign

def login():
    headers = {"User-Agent": t}
    res = requests.get("http://fanyi.youdao.com/",headers=headers)
    cookie_list = []
    for cookie in res.cookies:
        cookie_list.append("%s=%s"%(cookie.name,cookie.value))
    headers["Content-Type"] = "application/x-www-form-urlencoded"
    headers["Referer"] = "http://fanyi.youdao.com/"
    headers["Cookie"] = ';'.join(cookie_list)
    session.headers.update(headers)

3. 业务

from test import setUp
import json

def translate(data):
    test_result = {}
    test_result["tc"] = "%s_%s"%(data[0],data[1])
    bv,ts,salt,sign = setUp.get_auth_data(data[2])
    url = "http://fanyi.youdao.com/translate_o?smartresult=dict&smartresult=rule"
    req_data = {"i": data[2],
            "from": data[3],
            "to": data[4],
            "smartresult": "dict",
            "client": "fanyideskweb",
            "salt": salt,
            "sign": sign,
            "ts": ts,
            "bv": bv,
            "doctype": "json",
            "version": "2.1",
            "keyfrom": "fanyi.web",
            "action": "Y_BY_CLICKBUTTION"}
    res = setUp.session.post(url, data=req_data)
    try:
        assert res.status_code==int(data[6])
        #验证响应状态码
        assert res.status_code==int(data[6])
        #验证相应结果
        result = res.json()
        assert result["translateResult"][0][0]["src"]==data[2]
        assert result["translateResult"][0][0]["tgt"]==data[5]
        test_result["result"] = "pass"
    except:
        test_result["result"] = "fail"
    finally:
        print("-url:%s"%url)
        print("-request data:")
        print(req_data)
        print("-http code:%d"%res.status_code)
        print("-response content:")
        print(res.json())
        return test_result
       

4. 测试用例

import unittest
from test import setUp
from test.Excel import Excel as EX
from test import business as BS
import time

class MyTest(unittest.TestCase):
    
    def setUp(self):
        #1. login
        setUp.login()
        self.r_data_excel = EX("r", "elements.xlsx")
        self.w_data_excel = EX("w", "results.xlsx")
        self.result_list = []
        
    def test_translate(self):
        #2.翻译
        #2.1 读取测试数据
        list_data = self.r_data_excel.read("translate")
        #2.2 翻译
        for data in list_data:
            print("==start test:%s_%s"%(data[0],data[1]))
            try:
                test_result = BS.translate(data)
                self.result_list.append(test_result)
            except:
                pass
            finally:
                print("==end test:%s_%s\n"%(data[0],data[1]))
                time.sleep(3)
    def tearDown(self):
        self.w_data_excel.write(self.result_list, "translate")
        
if __name__=="__main__":
    unittest.main()

---

测试数据

测试结果