Automating Test Data Generation with SQLAlchemy ORM, Faker, and Requests in Python

The testing team for an e‑commerce platform needs to create realistic test data for scenarios such as merchant onboarding, product publishing, and order generation, and automation can simplify this process.

First, the required information for each test scenario is obtained directly from the database using the SQLAlchemy ORM framework. SQLAlchemy provides an object‑relational mapping that lets developers define Python classes that correspond to database tables.

ORM Core Principles

Simple: model data in its most basic form.

Communicable: document the schema in a language understandable by anyone.

Precise: create a correctly normalized structure based on the data model.

The typical workflow involves creating an Engine , defining a Base class with declarative_base() , and then declaring mapping classes. For example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine('dialect+driver://username:password@host:port/database')
Base = declarative_base()

class user(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(12))
    age = Column(String(2))
    city = Column(String(16))

    def __repr__(self):
        demo = "user(id={}, name={}, age={}, city={})"
        return demo.format(self.id, self.name, self.age, self.city)

After defining the mapping class, a Session is created to query the database:

Session = sessionmaker(bind=engine)
session = Session()
result1 = session.query(user).all()               # Returns a list of user objects
result2 = session.query(user.name, user.age).all() # Returns a list of tuples
result3 = session.query(user).filter_by(name='lisi').first()
result4 = session.query(user).filter(user.age > '19').first()

To generate diverse test data, the Faker library is used. Custom providers can produce age ranges for children, young, middle‑aged, and senior users:

from faker import Faker
from faker.providers import BaseProvider
from random import choice

faker = Faker('zh_CN')

class AgeProvider(BaseProvider):
    child = range(0, 14)
    young = range(14, 45)
    middle = range(45, 60)
    old = range(60, 120)
    def get_child_age(self):
        return choice(self.child)
    def get_young_age(self):
        return choice(self.young)
    def get_middle_age(self):
        return choice(self.middle)
    def get_old_age(self):
        return choice(self.old)

faker.add_provider(AgeProvider)

A helper function builds a table of random users using PrettyTable :

from prettytable import PrettyTable

def test_person():
    sex_set = ['男', '女']
    age_set = range(14, 101)
    tb = PrettyTable(['姓名', '性别', '年龄', '手机号', '邮箱'])
    for i in range(10):
        tb.add_row([
            faker.name(),
            choice(sex_set),
            choice(age_set),
            faker.phone_number(),
            faker.email()
        ])
    log.info('\n' + str(tb))

Once the data is assembled, it can be sent to the target API using the requests library. The article covers basic HTTP concepts, request structures, and common parameters such as params , data , json , headers , proxies , allow_redirects , and verify . Example GET and POST calls:

# Simple GET request
import requests
response = requests.get('http://m.mall.autohome.com.cn')
print(response.text)

# GET with URL parameters
response = requests.get('http://m.mall.autohome.com.cn?_appid=mall')
print(response.text)

# GET with params dict (None values are omitted)
param = {'_appid': 'mall', 'cityid': ''}
response = requests.get('http://m.mall.autohome.com.cn', params=param)
print(response.text)

# POST with form data
param = {'_appid': 'mall', 'cityid': '110100'}
response = requests.post('http://m.mall.autohome.com.cn', data=param)
print(response.text)

# POST with JSON payload
import json
response = requests.post('http://m.mall.autohome.com.cn', data=json.dumps(param))
print(response.text)

# Custom headers, proxies, and SSL verification
headers = {'User-Agent': 'Mozilla/5.0', 'Host': 'xx.xxx.xxx', 'Content-Type': 'text/html'}
proxies = {'http': 'http://127.0.0.1:9743', 'https': 'https://127.0.0.1:9743'}
response = requests.get('http://m.mall.autohome.com.cn', headers=headers, proxies=proxies, verify=False)
print(response.status_code)

The response object provides useful attributes such as encoding , text , content , headers , cookies , history , status_code , ok , json() , and raise_for_status() for handling API results.

By combining database extraction, ORM mapping, synthetic data generation, and HTTP submission, the workflow enables fully automated creation of realistic test data for e‑commerce scenarios, and can be extended to include related features such as promotions and coupons.