How to Supercharge API Test Automation with Python Faker and Database Seeding

In API automation testing, preparing test data is often a bottleneck; manually creating users, orders, or products before each run is time‑consuming and error‑prone.

Core Tool: Faker Library Overview

Faker is a Python library for generating fake data, supporting multiple languages and data types.

Installation

pip install faker

Common Data Types Example

from faker import Faker
fake = Faker('zh_CN')
print(fake.name())          # 张伟
print(fake.phone_number()) # 13812345678
print(fake.email())        # [email protected]
print(fake.address())      # 北京市朝阳区建国路88号
print(fake.date_of_birth())# 1990-05-20
print(fake.job())          # 软件工程师

You can also generate structured data:

user = {
    "username": fake.user_name(),
    "password": "Test@123",
    "real_name": fake.name(),
    "phone": fake.phone_number(),
    "email": fake.email(),
    "address": fake.address(),
    "created_at": fake.date_this_year()
}

Practical Example: Bulk Generate Users and Insert into MySQL

Scenario: create 100 activated users for API testing and insert them into a MySQL users table.

Step 1: Define Database Model

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Step 2: Database Connection Config (config.py)

# config.py
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'test_user',
    'password': 'test_pass',
    'database': 'test_db',
    'charset': 'utf8mb4'
}

Step 3: Data Generation and Insertion Script (data_generator.py)

import pymysql
from faker import Faker
from config import DB_CONFIG
import random

class TestDataGenerator:
    def __init__(self):
        self.fake = Faker('zh_CN')
        self.connection = None

    def connect_db(self):
        """Establish database connection"""
        try:
            self.connection = pymysql.connect(**DB_CONFIG)
            print("✅ Database connection successful")
        except Exception as e:
            print(f"❌ Database connection failed: {e}")
            raise

    def generate_user(self):
        """Generate a single user record"""
        return {
            "username": self.fake.user_name() + str(random.randint(100, 999)),
            "password": "Test@123",
            "real_name": self.fake.name(),
            "phone": self.fake.phone_number(),
            "email": self.fake.email(),
            "address": self.fake.address(),
            "status": random.choice([1, 2])  # 1: active, 2: disabled
        }

    def insert_users(self, count=100):
        """Batch insert user data"""
        if not self.connection:
            self.connect_db()
        with self.connection.cursor() as cursor:
            sql = """
            INSERT INTO users
            (username, password, real_name, phone, email, address, status)
            VALUES (%(username)s, %(password)s, %(real_name)s, %(phone)s, %(email)s, %(address)s, %(status)s)
            """
            users = [self.generate_user() for _ in range(count)]
            try:
                cursor.executemany(sql, users)
                self.connection.commit()
                print(f"✅ Successfully inserted {cursor.rowcount} user records")
            except Exception as e:
                self.connection.rollback()
                print(f"❌ Data insertion failed: {e}")
                raise

    def close(self):
        """Close database connection"""
        if self.connection:
            self.connection.close()

# Usage example
if __name__ == "__main__":
    generator = TestDataGenerator()
    try:
        generator.insert_users(count=100)
    finally:
        generator.close()

Advanced Usage: Custom Rules

Generate specific format data, e.g., order numbers:

def generate_order_no(self):
    """Generate order number: prefix + date + random number"""
    return f"ORD{self.fake.date_object().strftime('%Y%m%d')}{random.randint(1000, 9999)}"

Generate related data such as user‑order pairs:

def generate_order(self, user_id):
    return {
        "user_id": user_id,
        "order_no": self.generate_order_no(),
        "amount": round(random.uniform(10, 1000), 2),
        "status": random.choice([1, 2, 3]),
        "created_at": self.fake.date_time_this_month()
    }

Configuration‑driven generation can be achieved via YAML/JSON files.

Integration with Test Frameworks

Fixture example for pytest (conftest.py):

import pytest
from data_generator import TestDataGenerator

@pytest.fixture(scope="session")
def setup_test_users():
    generator = TestDataGenerator()
    generator.insert_users(50)
    yield
    # optional cleanup logic
    generator.close()

Command‑line usage:

python data_generator.py --table users --count 200 --env test

Safety and Best Practices

Never run data‑generation scripts against production databases.

Isolate configurations so scripts cannot connect to production.

Store database credentials securely (environment variables or encrypted config).

Provide cleanup scripts to delete test data after execution.

Log generated record counts, duration, and key fields for traceability.

Use transactions or record primary keys to enable rollback.

Conclusion

Test data should not be a bottleneck for automation. Using Python, Faker, and direct database operations enables fast, bulk, and controllable data creation, improving test independence, repeatability, and reducing reliance on manual setup or upstream systems.