Building a Python‑MySQL Automated Test Framework with Dependency and Parameterization Support

Introduction: In software development, automated testing is crucial. This article explains how to use Python and MySQL to manage and execute test cases, handling dependencies and parameterization.

Project requirements: Build a test framework that reads test cases from a MySQL table, sends HTTP requests, records responses, and supports case dependencies and parameterization. The testdata table fields are listed.

Project structure: The code is organized into three modules: db_operations.py for database interactions, test_executor.py for executing test cases, and main.py as the entry point.

Database operations module ( db_operations.py ):

import mysql.connector
from mysql.connector import Error
class DB:
    def __init__(self, host, database, user, password):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.connection = None
    def connect(self):
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password
            )
            if self.connection.is_connected():
                return True
        except Error as e:
            print(f"Error while connecting to MySQL: {e}")
        return False
    def close(self):
        if self.connection and self.connection.is_connected():
            self.connection.close()
    def get_test_cases(self):
        cursor = self.connection.cursor(dictionary=True)
        query = "SELECT * FROM testdata"
        cursor.execute(query)
        results = cursor.fetchall()
        cursor.close()
        return results
    def update_test_case(self, id, response):
        cursor = self.connection.cursor()
        query = "UPDATE testdata SET 返回数据 = %s WHERE id = %s"
        cursor.execute(query, (response, id))
        self.connection.commit()
        cursor.close()

Test case execution module ( test_executor.py ):

import requests
import json
from db_operations import DB
def send_request(test_case, context):
    headers = {}
    if test_case['请求数据格式'] == 0:
        headers['Content-Type'] = 'application/json'
        data = json.loads(test_case['请求数据'])
    else:
        headers['Content-Type'] = 'application/x-www-form-urlencoded'
        data = test_case['请求数据']
    # Parameterization
    for key, value in data.items():
        if isinstance(value, str) and value.startswith('{{') and value.endswith('}}'):
            var_name = value[2:-2]
            if var_name in context:
                data[key] = context[var_name]
    url = "http://your_api_url_here"  # replace with actual API URL
    if test_case['请求方式'] == 0:
        response = requests.get(url, params=data, headers=headers)
    else:
        if test_case['是否需要token'] == 0:
            headers['Authorization'] = 'Bearer your_token_here'  # replace with actual token
        response = requests.post(url, data=json.dumps(data) if headers['Content-Type'] == 'application/json' else data, headers=headers)
    return response.text

def run_tests(host, database, user, password):
    db = DB(host, database, user, password)
    if not db.connect():
        return
    test_cases = db.get_test_cases()
    context = {}
    for test_case in test_cases:
        depends_on = test_case.get('depends_on')
        if depends_on:
            dependency = next((tc for tc in test_cases if tc['id'] == depends_on), None)
            if dependency and '返回数据' in dependency:
                context.update(json.loads(dependency['返回数据']))
        response = send_request(test_case, context)
        db.update_test_case(test_case['id'], response)
        context.update(json.loads(response))
    db.close()

if __name__ == "__main__":
    # Add argument parser for dynamic DB connection info if needed
    run_tests('localhost', 'your_database', 'your_user', 'your_password')

Main program ( main.py ):

# main.py
from test_executor import run_tests
if __name__ == "__main__":
    # Additional initialization, logging, etc. can be added here
    run_tests('localhost', 'your_database', 'your_user', 'your_password')

Conclusion: By following these steps, a basic test framework is built that reads test cases from MySQL, handles dependencies and parameterization, and can be extended with error handling, logging, and more complex logic.