Framework Overview

Managing API test cases across multiple endpoints often leads to scattered Python files that become difficult to maintain. When an API changes, developers must locate and update corresponding test files individually. This framework addresses the problem by centralizing test data in Excel files, allowing test case updates without code modifications.

Each API gets its own Excel worbkook containing two sheets:

• Sheet 1: Interface metadata including endpoint name, URL path, and HTTP method
• Sheet 2: Test cases where columns represent parameters and rows represent individual test scenarios, with the second-to-last column for expected results and the last column for test descriptions

Project Structure

project/
├── common/
│   └── logger.py           # Logging utilities
├── config/
│   ├── cfg.ini             # Configuration settings
│   └── readConfig.py       # Configuration reader
├── data/
│   ├── api_user.xlsx       # User API test cases
│   └── api_order.xlsx      # Order API test cases
├── logs/
│   └── test_execution.log
├── report/
│   └── test_report.html
└── run_main.py             # Entry point

Configuration Management

cfg.ini stores enviroment-specific settings:

[email]
smtp_server = smtp.example.com
port = 465
sender = sender@example.com
psw = authorization_code
receiver = recipient@example.com

[interface]
ip = 192.168.1.100
port = 8080

readConfig.py parses the configuration:

import os
import configparser

current_dir = os.path.dirname(os.path.realpath(__file__))
config_path = os.path.join(current_dir, "cfg.ini")
config = configparser.ConfigParser()
config.read(config_path, encoding="utf-8")

smtp_server = config.get("email", "smtp_server")
sender = config.get("email", "sender")
psw = config.get("email", "psw")
receiver = config.get("email", "receiver")
mail_port = config.get("email", "port")
host_ip = config.get("interface", "ip")
host_port = config.get("interface", "port")

Core Test Executor

api_test.py handles test data loading, API execution, and result validation:

import os
import xlrd
import requests
from datetime import datetime
from xlrd import xldate_as_tuple
from config import readConfig
from common.logger import Log

base_dir = os.path.dirname(__file__)
parent_dir = os.path.dirname(base_dir)
data_dir = os.path.join(parent_dir, 'data')
data_files = os.listdir(data_dir)

log = Log()
session = requests.session()

def execute_api_tests():
    for filename in data_files:
        workbook = xlrd.open_workbook(os.path.join(data_dir, filename))
        
        # Extract interface metadata from first sheet
        meta_sheet = workbook.sheet_by_index(0)
        api_name = meta_sheet.row_values(1)[0]
        api_path = meta_sheet.row_values(1)[1]
        http_method = meta_sheet.row_values(1)[2]
        
        # Load test cases from second sheet
        case_sheet = workbook.sheet_by_index(1)
        total_rows = case_sheet.nrows
        field_names = case_sheet.row_values(0)
        
        for row_idx in range(1, total_rows):
            request_params = {}
            
            # Build request parameters
            for col_idx in range(len(field_names) - 2):
                cell_type = case_sheet.cell(row_idx, col_idx).ctype
                cell_value = case_sheet.cell_value(row_idx, col_idx)
                
                if cell_type == 2 and cell_value % 1 == 0:
                    cell_value = int(cell_value)
                elif cell_type == 3:
                    date_obj = datetime(*xldate_as_tuple(cell_value, 0))
                    cell_value = date_obj.strftime('%Y/%m/%d')
                elif cell_type == 4:
                    cell_value = True if cell_value == 1 else False
                
                request_params[field_names[col_idx]] = cell_value
            
            # Extract expected results and descriptions
            expected_result = case_sheet.cell_value(row_idx, len(field_names) - 2)
            test_description = case_sheet.cell_value(row_idx, len(field_names) - 1)
            
            # Construct full URL
            target_url = f"http://{readConfig.host_ip}:{readConfig.host_port}{api_path}"
            request_headers = {
                "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
                "X-Requested-With": "XMLHttpRequest",
                "Connection": "keep-alive"
            }
            
            # Execute request based on HTTP method
            if http_method == 'GET':
                response = session.get(target_url, params=request_params)
            elif http_method == 'POST':
                response = session.post(target_url, data=request_params, headers=request_headers)
            
            result = response.json()
            result_str = str(result).replace('None', 'null')
            
            log.info(f"API: {api_name}, Result: {result_str}")
            
            if result_str == str(expected_result).replace('None', 'null'):
                log.info("PASS")
            else:
                log.info("FAIL")

execute_api_tests()

Session Management for Authentication

Many APIs require authentication. The login handler establishes a session that persists across subsequent requests:

user_login.py:

import requests
from common.logger import Log

class AuthenticationHandler:
    def __init__(self, session):
        self.session = session
        self.logger = Log()
    
    def authenticate(self, username, password):
        login_url = "http://192.168.1.100:8080/backend/system/user/login"
        headers = {
            "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
            "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36",
            "X-Requested-With": "XMLHttpRequest"
        }
        payload = {"code": username, "passwd": password}
        
        response = self.session.post(login_url, headers=headers, data=payload)
        self.logger.info(f"Login response: {response.text}")
        return response.json()

Integration with test executor:

from case.user_login import AuthenticationHandler

def execute_api_tests():
    test_counter = 1
    auth = AuthenticationHandler(session)
    
    for filename in data_files:
        workbook = xlrd.open_workbook(os.path.join(data_dir, filename))
        # ... loading code ...
        
        for row_idx in range(1, total_rows):
            # Authenticate before each request
            auth.authenticate("test_user", "test_pass123")
            
            # Execute and validate
            if http_method == 'GET':
                response = session.get(target_url, params=request_params)
            elif http_method == 'POST':
                response = session.post(target_url, data=request_params, headers=request_headers)
            
            result = response.json()
            log.info(f"Test #{test_counter}: {api_name}, Description: {test_description}")
            
            test_counter += 1

Writing Results Back to Excel

Test results can be written directly into the Excel file using openpyxl, with color-coded formatting:

import openpyxl
from openpyxl.styles import Font

def execute_api_tests():
    test_counter = 1
    
    for filename in data_files:
        workbook = xlrd.open_workbook(os.path.join(data_dir, filename))
        
        # Open workbook for writing with openpyxl
        write_wb = openpyxl.load_workbook(os.path.join(data_dir, filename))
        write_ws = write_wb.worksheets[1]
        
        green_font = Font(color="37b400")
        red_font = Font(color="ff0000")
        
        # ... loading code ...
        
        for row_idx in range(1, total_rows):
            # ... parameter extraction ...
            
            # Execute request
            if http_method == 'GET':
                response = session.get(target_url, params=request_params)
            elif http_method == 'POST':
                response = session.post(target_url, data=request_params, headers=request_headers)
            
            result = response.json()
            result_str = str(result).replace('None', 'null')
            
            # Write result with color coding
            if result_str == str(expected_result).replace('None', 'null'):
                write_ws.cell(row=row_idx + 1, column=ncols, value='PASS')
                write_ws.cell(row=row_idx + 1, column=ncols).font = green_font
            else:
                write_ws.cell(row=row_idx + 1, column=ncols, value='FAIL')
                write_ws.cell(row=row_idx + 1, column=ncols).font = red_font
            
            write_wb.save(os.path.join(data_dir, filename))
            test_counter += 1
    
    log.info(f"Total tests executed: {test_counter}")

Excel Structure

Sheet 1 (Interface Info):

Sheet 2 (Test Cases):

This framework enables test maintainance through Excel editing alone, eliminating the need to modify Python code when API parameters or expectations change.