Python+excel+unittest+ddt+requests数据驱动接口自动化

1、环境准备

Python3、request、unittest、ddt、mock、cx_Oracle、pymysql、openpyxl

2、实现的功能

对requests请求方法封装

获取Excel数据读取数据及测试结果写入方法的封装

HTTPTestRunner生产测试报告，进行自动邮件发送

token使用获取依赖的方法实现

logging日志文件写入

对读取数据库方法封装

加入mock测试

项目结构

excel测试数据

requests请求方法的封装

对get、post、put、delete方法进行封装

import json,requests
from Base.read_db import oracle

class BaseRequest():

    def send_post(self,url,data,headers=None):
        """post请求"""
        res = requests.post(url=url,data=data,headers=headers).text
        return res

    def sen_get(self,url,data =None,headers=None):
        """get请求"""
        res = requests.get(url,params =data,headers=headers).text
        return res
    def send_put(self,url,data =None,headers=None):
        """put请求"""
        res = requests.put(url=url, data=data, headers=headers).text
        return res
    def send_delete(self,url,data =None,headers=None):
        """delete请求"""
        res = requests.delete(url,data=data,headers=headers).text
        return res

    def run_man(self,method,url,data=None,headers=None):
        """执行方法，传递method、URL、data参数"""
        if method == "post":
            res = self.send_post(url,data,headers)
        elif method == "put":
            res = self.send_put(url,data,headers)
        elif method == "delete":
            res = self.send_delete(url,data,headers)
        else:
            res = self.sen_get(url,data,headers)
        try:
            res=json.loads(res)
        except:
            print('解析失败')
        return res

读取oracle，MySQL方法封装

读取数据库中文乱码解决，开头增加 os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'

import pymysql.cursors
import os,pymysql
import cx_Oracle
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
class ReadDB():

    def read_oracle(self,sql):
        '''查询oracel的数据库'''
        conn = cx_Oracle.connect('', '', '')
        cursor = cx_Oracle.Cursor(conn)
        cursor.execute(sql)
        result = cursor.fetchall()
        cols = [d[0] for d in cursor.description]
        a = []
        for row in result:
            b = dict(zip(cols, row))
            a.append(b)
        cursor.close()
        conn.close()
        return a

    def del_mysqldb(self,sql,name):
        '''MySQL数据库删除数据'''
        db = pymysql.connect(host='', port=3306, user='', passwd='', db=name, charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
        print(cursor.rowcount,'记录删除条数')

    def read_mysqldb(self,sql,name):
        '''MySQL数据库查询数据'''
        db = pymysql.connect(host='', port=, user='', passwd='', db=name, charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)
        cursor = db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data



oracle = ReadDB()

if __name__ == "__main__":

    # 
    # print(oracle.read_oracle(sql)[0]["CONTENT"])

    sql = "'"

    print(oracle.read_mysqldb(sql,'hsp_user'))

获取依赖方法封装

获取Excel中的数据使用split()方法进行拆分，使用Excel获取单元格数据的方法获取依赖数据

import sys,os,json
from Base.read_excel import readExcel
from jsonpath_rw import parse

pwd = os.getcwd()
sys.path.append(pwd)
base_path=os.path.abspath(os.path.dirname(pwd)+os.path.sep+".")


def split_data(data):
    '''拆分单元格数据'''
    list_data = data.split(">")
    case_id = list_data[0]
    row_num = list_data[1]
    rule_data = list_data[2]
    return case_id,row_num,rule_data


def depend_data(data):
    '''获取依赖结果集'''
    case_id = split_data(data)[0]
    num = split_data(data)[1]
    row_number = readExcel.get_rows_number(case_id)
    rul_data = readExcel.get_cell_value(row_number,int(num))
    return rul_data


def get_depend_data(res_data,key):
    '''获取依赖字段'''
    res_data = json.loads(res_data)
    json_exe = parse(key)
    madle = json_exe.find(res_data)
    return [math.value for math in madle][0]

def get_data(data):
    '''获取依赖数据'''
    res_data = depend_data(data)
    # print(res_data)
    rule_data = split_data(data)[2]
    return get_depend_data(res_data,rule_data)




if __name__ == "__main__":
    data = 'case_001>15>data'

    print(get_data(data))

测试用例使用unittest+ddt

# -*- coding: utf-8 -*-
import json
import unittest,os,sys

import mock
from ddt import ddt,data
pwd = os.getcwd()
base_path=os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
sys.path.append(base_path)

from Base.read_excel import readExcel
from Base.read_ini import config_data
from Base.base_request import request
from Base.read_db import oracle
from Base.depend_date import get_data
from Base.read_json import get_value
from Base.log import logger

excel_data = readExcel.get_excel_data()
token = request.token()
config_url = config_data.get_value('host')
@ddt
class Test_case01(unittest.TestCase):

    @data(*excel_data)
    def test_user(self,excel_data):
        print(excel_data)
        case_id = excel_data[0]
        """获取测试用例的行号i"""
        i = readExcel.get_rows_number(case_id)
        depend = excel_data[3]
        is_run = excel_data[2]
        base_url = excel_data[5]
        url = config_url + base_url
        para_data = json.loads(excel_data[7])
        if is_run == "yes":
            """该用例是否进行执行"""
            if depend:
                """使用获取依赖的方法获取依赖数据，把数据添加到url或data中"""
                depend_data = get_data(depend)
                if excel_data[14] == "url":
                    url = url+'/'+str(depend_data)
                elif excel_data[14] == "data":
                    para_data['id']=depend_data
            headers = json.loads(excel_data[8])
            """把token添加到headers中"""
            headers['Authorization'] = token
            method = excel_data[6]
            """使用mock数据"""
            # request.run_man = mock.Mock(return_value=get_value(base_url))
            """使用run_man方法进行get/post/put/delete请求"""
            if method == "get" or method == "delete":
                res = request.run_man(method, url, para_data, headers)
            else:
                res = request.run_man(method, url, json.dumps(para_data), headers)
            print(res)
            """把接口返回的结果写入Excel中"""
            readExcel.excel_write_data(i, 13, json.dumps(res, ensure_ascii=False).encode('utf-8'))
            assert_ = excel_data[10]
            expect_msg = excel_data[9]
            """根据预期结果方式msg/code/sql结果进行断言"""
            if assert_ == "msg":
                try:
                    self.assertEqual(expect_msg,res['msg'])
                    readExcel.excel_write_data(i, 12, '成功')
                except Exception as msg:
                    logger.info(msg)
                    readExcel.excel_write_data(i, 12, '失败')
            elif assert_ == "code":
                try:
                    self.assertEqual(expect_msg,res['status'])
                    readExcel.excel_write_data(i, 12, '成功')
                except Exception as msg:
                    logger.info(msg)
                    readExcel.excel_write_data(i, 12, '失败')
            elif assert_ == "sql":
                sql_data = excel_data[4]
                try:
                    result_data = oracle.read_mysqldb(sql_data,'hsp_user')
                    self.assertEqual(para_data['username'], result_data[0]['username'])
                    """把自己后面测试用例使用到的数据可以添加到字典中，写入Excel"""
                    excel_sql_data = {"id":result_data[0]['id'],'name':result_data[0]['username']}
                    readExcel.excel_write_data(i, 12, '成功')
                    readExcel.excel_write_data(i, 14, json.dumps(excel_sql_data))
                except Exception as msg:
                    logger.info(msg)
                    readExcel.excel_write_data(i, 12, '失败')


if __name__ == "__main__":
    unittest.main()

生产测试报告

生产html测试报告，并以邮件的形式发送

import os
import sys

"""
author:
ad_date:
brief:  运行全部测试用例
"""
pwd = os.getcwd()
base_path=os.path.abspath(os.path.dirname(pwd)+os.path.sep+".")

import unittest,time,smtplib,configparser
from Base.HTMLTestRunner_jpg import HTMLTestRunner
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from Base.get_path import GetPath



def add_case():
    """
    添加报告路径，添加所有的测试用例
    :return: 一个包含路径下所有测试用例（test*.py）的list集合˚
    """
    testunit = unittest.TestSuite()
    case_path = base_path+'/interface/Case'
    print("测试用例的路径 %s" % case_path)
    discover = unittest.defaultTestLoader.discover(case_path,
                                                   pattern="test*.py",
                                                   top_level_dir=None)
    testunit.addTests(discover)
    return testunit


def report_path():
    """报告路径"""
    result_path = os.path.join(os.getcwd(), 'report')
    if os.path.isdir(result_path):
        pass
    else:
        os.makedirs(result_path)
    return result_path


def run_case():
    now = time.strftime("%Y_%m_%d_%H_%M_%S")
    result_abspath = os.path.join(report_path(), ('%s.html' % now))
    print(result_abspath)
    fp = open(result_abspath, 'wb')
    runner = HTMLTestRunner(title="自动化测试报告",
                            description="用例执行情况",
                            stream=fp,
                            retry=0,
                            )
    runner.run(add_case())
    fp.close()


def get_report_file():
    lists = os.listdir(report_path())
    lists.sort(key=lambda fn: os.path.getmtime(os.path.join(report_path(), fn)))
    report_file = lists[-1]
    print("最新的测试的报告为：%s" % report_file)
    return os.path.join(report_path(), report_file)


def send_mail():
    """
    发送最新的测试报告
    :return:
    """
    configPath = GetPath().get_conf_path('server.ini')
    conf = configparser.ConfigParser()
    conf.read(configPath, encoding='UTF-8')
    sender = conf.get('email', 'sender')
    pwd = conf.get('email', 'psw')
    server = conf.get('email', 'smtp_server')
    port = conf.get('email', 'port')
    receiver = conf.get('email', 'receiver')
    time.sleep(2)
    report_file = get_report_file()
    with open(report_file, 'rb') as f:
        mail_body = f.read()
    msg = MIMEMultipart()
    body = MIMEText(mail_body, _subtype='html', _charset='utf-8')
    msg['Subject'] = '自动化测试报告'
    msg['from'] = sender
    msg['to'] = receiver
    #添加时间
    msg['ad_date'] = time.strftime('%a,%d,%b,%Y_%M_%S %Z')
    msg.attach(body)
    #添加附件
    att = MIMEText(open(report_file, 'rb').read(), 'base64', 'utf-8')
    att['Content-Type'] = 'application/octet-stream'
    att['Content-Disposition'] = 'attachment; filename = "report.html"'
    msg.attach(att)
    #发件人邮箱中的SMTP服务器，端口是465
    smtp = smtplib.SMTP_SSL(server, port)

    # 登录用户名和密码
    smtp.login(sender, pwd)
    smtp.sendmail(sender, receiver.split(','), msg.as_string())
    smtp.quit()
    print('邮件已经发送给：%s' % str(receiver))


if __name__ == '__main__':

    run_case()
    # send_mail()