python读取excel组装请求

1.excel数据如下，实现自动读取excel中各字段的值并组装请求，自动断言，校验通过就在实际结果那一栏写“pass”，校验失败就写入返回数据。

2.首先编写操作excel的方法，需要用到xlrd这个包

import xlrd
from xlutils.copy import copy
class OperationExcel():
    def __init__(self,filename=None,sheetid=None):
        self.filename = '../dataconfig/case.xls'
        wb = xlrd.open_workbook(self.filename)
        self.data = wb.sheets()[0]#取第一个sheet的数据

    #获取单元格的行数
    def get_lines(self):
        return self.data.nrows

    #获取某一个单元格的内容
    def get_cell_value(self,row,col):
        return self.data.cell_value(row,col)

    # 根据对应的caseid 找到对应行的内容
    def get_rows_data(self, case_id):
        row_num = self.get_row_num(case_id)
        rows_data = self.get_row_values(row_num)
        return rows_data

    # 根据对应的caseid找到对应的行号
    def get_row_num(self, case_id):
        num = 0
        clols_data = self.get_cols_data()
        for col_data in clols_data:
            if case_id in col_data:
                return num
            num = num + 1

    # 根据行号，找到该行的内容
    def get_row_values(self, row):
        tables = self.data
        row_data = tables.row_values(row)
        return row_data

    # 获取某一列的内容
    def get_cols_data(self, col_id=None):
        if col_id != None:
            cols = self.data.col_values(col_id)
        else:
            cols = self.data.col_values(0)
        return cols

    # 写入数据
    def write_value(self, row, col, value):
        read_data = xlrd.open_workbook(self.filename)
        write_data = copy(read_data)
        sheet_data = write_data.get_sheet(0)
        sheet_data.write(row, col, value)
        write_data.save(self.filename)

3.将固定字段与行号绑定

class global_var:
    Id = '0'
    request_name = '1'
    url = '2'
    run = '3'
    request_way = '4'
    data = '9'
    expect = '10'

def get_id():   #获取caseid
    return global_var.Id

def get_url():  #获取url
    return global_var.url

def get_run():  #获取是否执行
    return global_var.run

def get_request_method():   #获取请求方式
    return global_var.request_way

def get_request_data():     #获取请求数据
    return global_var.data

def get_expect():   #获取期望值
    return global_var.expect

4.封装请求方法

import requests
import json
class RunMethod():
    def post_main(self,url,data,header=None):
        res = None
        if header != None:
            res = requests.post(url=url,data=data,headers=header)
        else:
            res = requests.post(url=url, data=data)
        return res.json()

    def get_main(self, url, data=None, header=None):
        res = None
        if header != None:
            res = requests.get(url=url, params=data, headers=header, verify=False)
        else:
            res = requests.get(url=url, params=data, verify=False)
        return res.json()

    def run_main(self,method,url,data=None,header=None):
        res = None
        if method == 'POST':
            res = self.post_main(url,data,header)
        else:
            res = self.get_main(url,data,header)
        return json.dumps(res,ensure_ascii=False)

5.封装一个判断一个字符串是否包含另一个字符串的方法

def is_contain(self,str_one,str_two):
    flag = None
    if str_one in str_two:
        flag = True
    else:
        flag = False
    return flag

6.封装一个发邮件的方法，自动发送测试报告

import smtplib
from email.mime.text import MIMEText
class SendEmail:
    global send_user
    global password
    global email_host

    email_host = "smtp.163.com"
    send_user = "10023904@163.com"
    password = "123456"
    def send_mail(self,user_list,sub,content):
        user = "xunyeting<"+send_user+">"
        message = MIMEText(content,_subtype='plain',_charset='utf-8')
        message['Subject'] = sub
        message['from'] = user
        message['to'] = ";".join(user_list)
        server = smtplib.SMTP()
        server.connect(email_host)
        server.login(send_user,password)
        server.sendmail(user,user_list,message.as_string())
        server.close()

    def send_main(self, pass_list, fail_list):
        pass_num = float(len(pass_list))
        fail_num = float(len(fail_list))
        count_num = pass_num + fail_num
        pass_result = "%.2f%%" % (pass_num / count_num * 100)
        fail_result = "%.2f%%" % (fail_num / count_num * 100)

        user_list = ['693895@qq.com']
        sub = "接口自动化测试报告"
        content = "此次一共运行接口个数为%d个，通过个数为%d个，失败个数为%d,通过率为%s,失败率为%s" % (
        count_num, pass_num, fail_num, pass_result, fail_result)
        self.send_mail(user_list, sub, content)

7.定义主函数，执行用例

class RunTest:
    def __init__(self):
        self.run_method = RunMethod()
        self.data = GetData()
        self.com_util = CommonUtil()
        self.send_mail = SendEmail()

    def go_on_run(self):
        res = None
        pass_count = []
        fail_count = []

        rows_count = self.data.get_case_lines()
        for i in range(1, rows_count):#遍历每一行
            url = self.data.get_request_url(i)
            method = self.data.get_request_method(i)
            request_data = self.data.get_request_data(i)
            expect = self.data.get_expcet_data(i)
            is_run = self.data.get_is_run(i)
            if is_run:#判断是否执行该条用例
                res = self.run_method.run_main(method, url, request_data)
                print("expect"+i+"==="+expect)
                print("result"+i+"===="+res)

            if self.com_util.is_contain(expect, res):
                self.data.write_result(i, 'pass')
                pass_count.append(i)#成功数+1
            else:
                self.data.write_result(i, res)
                fail_count.append(i)#失败数+1
        self.send_mail.send_main(pass_count, fail_count)#发送邮件

if __name__ == '__main__':
    run = RunTest()
    print(run.go_on_run())

8.执行成功

9.用例里面正确写入执行结果

10.成功收到测试报告邮件还是挺开心的。