Python总结----Pytest实现数据驱动测试（二）

使用Excel进行数据驱动测试

新建一个名叫ExcelDataDrivenProject的Python工程，在工程下新建两个文件名分别为ExcelUtil.py、DataDriven.py的文件

其中ExcelUtil.py中代码如下：

# encoding = utf-8
from openpyxl import load_workbook

class ParseExcel(object):
    def __init__(self, excelPath, sheetName):
        """
        初始化方法，接收文件路径和工作表名称
        :param excelPath: Excel 文件的路径
        :param sheetName: 工作表名称
        """
        self.excelPath = excelPath
        self.sheetName = sheetName
        self.wb = load_workbook(self.excelPath)  # 将要读取的数据加载到内存
        self.sheet = self.wb[self.sheetName]  # 通过工作表明获取工作对象
        self.maxRowNum = self.sheet.max_row  # 获取工作表中存在数据区域的最大行号

    def getDatasFromSheet(self):
        """
        从工作表中读取数据
        :return: 包含从工作表中读取出来的数据的列表
        """
        dataList = []  # 定义用于存放从工作表中读取出来的数据
        rows_data = list(self.sheet.iter_rows(values_only=True))  # 获取表中所有行的数据
        for line in rows_data[1:]:  # 第一行为标题数据需要排除
            tmpList = []
            for cell in line:
                tmpList.append(cell)
            dataList.append(tmpList)
        return dataList

if __name__ == "__main__":
    excelPath = "D:\\user\\Downloads\\纸箱耗材导入模板.xlsx"
    sheetName = "Sheet0"
    pe = ParseExcel(excelPath, sheetName)
    for row in pe.getDatasFromSheet():
        print(row)

DataDriven.py中代码如下：

 

import pytest
from openpyxl import load_workbook

# 读取 Excel 文件中的测试数据
def read_excel_data(file_path, sheet_name):
    workbook = load_workbook(file_path)
    sheet = workbook[sheet_name]
    test_data = []
    
    # 跳过标题行，从第二行开始读取数据
    for row in sheet.iter_rows(min_row=2, values_only=True):
        test_data.append((row[1], row[2]))  # 假设第二列是输入，第三列是期望输出
    
    return test_data

# 测试函数
def add(a, b):
    return a + b

# 使用 pytest.mark.parametrize 动态生成测试用例
def test_addition(request):
    test_data = read_excel_data('test_data.xlsx', 'Sheet1')
    input_value, expected_output = request.param
    assert add(input_value, input_value) == expected_output

# 生成测试用例
test_cases = read_excel_data('test_data.xlsx', 'Sheet1')
test_ids = [f"Test Case {i+1}" for i in range(len(test_cases))]

@pytest.mark.parametrize("test_addition", test_cases, ids=test_ids, indirect=True)
def test_all_cases(test_addition):
    pass  # 实际测试逻辑在 test_addition 中

 

使用数据库进行驱动测试

 

import pytest
import mysql.connector
from mysql.connector import Error

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'database': 'test_db',
    'user': 'your_username',
    'password': 'your_password'
}

def get_db_connection():
    """创建并返回数据库连接"""
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        return connection
    except Error as e:
        print(f"数据库连接错误: {e}")
        return None

def fetch_test_data():
    """从数据库获取测试数据"""
    connection = get_db_connection()
    if connection is None:
        return []
    
    cursor = connection.cursor()
    try:
        cursor.execute("SELECT input_value, expected_result FROM test_data")
        test_data = cursor.fetchall()
        return test_data
    except Error as e:
        print(f"查询数据错误: {e}")
        return []
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

@pytest.mark.parametrize("input_value,expected_result", fetch_test_data())
def test_multiply_by_two(input_value, expected_result):
    """测试输入值乘以2是否等于预期结果"""
    result = input_value * 2
    assert result == expected_result, f"输入值 {input_value} 乘以 2 应该等于 {expected_result}，但得到了 {result}"

def test_database_connection():
    """测试数据库连接是否正常"""
    connection = get_db_connection()
    assert connection is not None, "数据库连接失败"
    if connection:
        connection.close()