本文共 5118 字，大约阅读时间需要 17 分钟。

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

1. excel处理

import xlrdimport xlsxwriterclass 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 requestsimport hashlibimport timeimport 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,signdef 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 setUpimport jsondef 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 unittestfrom test import setUpfrom test.Excel import Excel as EXfrom test import business as BSimport timeclass 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()

测试数据

转载地址：http://stmji.baihongyu.com/