Generate Python Test Fixture Data from Excel for FastAPI Unit Tests

Generated fixture list

A login test sheet becomes a list of dicts. Rename data → CASES on paste.

Excel (Sheet1):
  name        | email          | password | expected_status
  valid_login | a@x.com        | pw123    | 200
  bad_pass    | a@x.com        | wrong    | 401
  no_user     | ghost@x.com    | pw123    | 404

format: list_of_dicts → data.py (renamed CASES)

CASES = [
    {"name": "valid_login", "email": "a@x.com", "password": "pw123", "expected_status": "200"},
    {"name": "bad_pass", "email": "a@x.com", "password": "wrong", "expected_status": "401"},
    {"name": "no_user", "email": "ghost@x.com", "password": "pw123", "expected_status": "404"}
]

Drive parametrize with the list

Feed CASES straight into parametrize. Cast expected_status to int because it arrived as a string.

import pytest
from fixtures.data import CASES

@pytest.mark.parametrize("case", CASES, ids=[c["name"] for c in CASES])
def test_login(client, case):
    r = client.post("/login", json={
        "email": case["email"], "password": case["password"]
    })
    assert r.status_code == int(case["expected_status"])

Build Pydantic payloads per case

The output is plain dicts. Strip the meta column, then construct the model — casting age to int first.

from pydantic import BaseModel
from fixtures.data import CASES

class SignUp(BaseModel):
    email: str
    age: int

for c in CASES:
    model = SignUp(email=c["email"], age=int(c["age"]))
    # send model.model_dump() to the endpoint

Optional fields via blank cells

A blank cell becomes None, which models an absent optional field directly.

Excel:
  email     | phone
  a@x.com   |          <- blank (optional)
  b@x.com   | 555-0100

CASES = [
    {"email": "a@x.com", "phone": None},
    {"email": "b@x.com", "phone": "555-0100"}
]
# Pydantic Optional[str] = None accepts the None case

Wrap in a fixture instead of importing the list

Some teams prefer a fixture function. Wrap the generated list so tests request it by name.

import pytest
from fixtures.data import CASES

@pytest.fixture
def login_cases():
    return CASES

def test_count(login_cases):
    assert len(login_cases) == 3

expected_status of 200 is emitted as "200". An assertion like assert r.status_code == case["expected_status"] compares int to str and always fails. Cast at the boundary: int(case["expected_status"]). This trips up almost every first run.

The generator emits plain dicts. To get model instances, construct them yourself — MyModel(**case) — and cast any int/float fields first, since they arrived as strings. Drop meta columns like name/expected_status before unpacking into a model that doesn't expect them.

Every run assigns to data. There's no option to set the variable name, so rename it after pasting (CASES, PAYLOADS). If you import without renaming you'll be importing data, which reads poorly in tests.

Only the first 500 cases are inlined; list_of_dicts appends # ... N more rows truncated. For a fixture sheet that grew past 500, split it across files or load the remainder at runtime with pandas.read_excel() / openpyxl in a fixture.

Multi-line cells (e.g. a multi-paragraph expected message) aren't newline-escaped and produce a broken string literal — the fixture file won't import. Keep test strings single-line, or post-process the literal.

Blank optional fields map cleanly to None, which is exactly what you want for Optional[...] model fields and missing-value cases. A cell with only spaces is NOT blank — it becomes a quoted whitespace string and may fail validation unexpectedly, so trim whitespace on the source sheet first if testers may have entered stray spaces.

Two columns that sanitise to the same key collapse — the later one wins inside each dict, so a field silently disappears from every case. Make headers distinct first with csv-header-rename.

A workbook with happy_path and error_cases tabs yields only the first tab's fixtures. Generate each tab separately (or split into files) and import both lists into your test module.

Uploads above Pro 50 MB / 100,000 rows (or your tier's ceiling) are rejected. Fixture files are tiny, so you'll hit the 500-row inline cap long before the size limit matters.

The generator is Pro-gated (Python Generator requires Pro tier.). Free-tier CI users can't generate fixtures with it — upgrade or commit the generated file from a Pro machine.