A single-file Python REST API for tracking personal expenses. Built with FastAPI and SQLite. Supports full CRUD for expenses and categories, date range filtering, monthly summaries, CSV export, and a health check endpoint.
You are an assistant that generates a single-file Python REST API. Produce a file called expense_tracker.py runnable with Python 3.10+ that implements a personal expense tracker.
The only external dependency is FastAPI with uvicorn. The script must work after:
pip install "fastapi[standard]" uvicorn
Use Python's built-in sqlite3 module for the database — no ORM, no SQLAlchemy.
On startup, create an SQLite database file (expenses.db) if it doesn't exist. Enable WAL mode for better concurrent read performance. Create these tables using IF NOT EXISTS:
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
color TEXT NOT NULL DEFAULT '#6c757d',
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
amount REAL NOT NULL CHECK(amount > 0),
description TEXT NOT NULL,
category_id INTEGER NOT NULL REFERENCES categories(id),
date TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_expenses_date ON expenses(date);
CREATE INDEX IF NOT EXISTS idx_expenses_category ON expenses(category_id);
Seed 3 default categories on first run (use INSERT OR IGNORE):
Create a helper function get_db() that returns a new sqlite3.Connection with:
row_factory = sqlite3.Row (so rows can be accessed by column name)PRAGMA foreign_keys = ON (enforce foreign key constraints)Each request handler should open its own connection and close it when done. Use try/finally or a context manager to ensure connections are always closed. Do not share connections across requests — SQLite handles file-level locking, but each connection should be short-lived.
Define request/response models using Pydantic BaseModel:
class CategoryCreate(BaseModel):
name: str = Field(min_length=1, max_length=50)
color: str = Field(default="#6c757d", pattern=r"^#[0-9a-fA-F]{6}$")
class CategoryResponse(BaseModel):
id: int
name: str
color: str
created_at: str
class ExpenseCreate(BaseModel):
amount: float = Field(gt=0, description="Must be positive")
description: str = Field(min_length=1, max_length=200)
category_id: int
date: str = Field(pattern=r"^\d{4}-\d{2}-\d{2}$", description="ISO format YYYY-MM-DD")
class ExpenseUpdate(BaseModel):
amount: float | None = Field(default=None, gt=0)
description: str | None = Field(default=None, min_length=1, max_length=200)
category_id: int | None = None
date: str | None = Field(default=None, pattern=r"^\d{4}-\d{2}-\d{2}$")
class ExpenseResponse(BaseModel):
id: int
amount: float
description: str
category_id: int
category_name: str
date: str
created_at: str
class CategorySummary(BaseModel):
category_name: str
total: float
count: int
class MonthlySummary(BaseModel):
month: str
total: float
count: int
by_category: list[CategorySummary]
Additionally, validate the date field in ExpenseCreate and ExpenseUpdate beyond the regex — use datetime.date.fromisoformat() to ensure it's a real date (reject 2025-02-30). Raise an HTTPException(422) with a clear message if invalid.
All error responses must use this JSON structure:
{"detail": "Human-readable error message"}
This is FastAPI's default for HTTPException, so use HTTPException consistently. Never return raw strings or unstructured errors.
GET /health — return {"status": "ok", "database": "connected"}. Verify the database is reachable by executing SELECT 1. If the database is unreachable, return 503 with {"status": "error", "database": "disconnected"}.GET /categories — list all categories, ordered by namePOST /categories — create a category, return 201. If the name already exists, return 409 with message "Category already exists"DELETE /categories/{id} — delete a category. If expenses reference it, return 400 with message "Cannot delete category with existing expenses". If the category does not exist, return 404.GET /expenses — list all expenses with category names joined via SQL JOIN. Support these query params:
date_from (str, optional) — filter expenses on or after this date (inclusive)date_to (str, optional) — filter expenses on or before this date (inclusive)category_id (int, optional) — filter by categorysort (str, optional, default "date") — sort field, one of: date, amount, created_atorder (str, optional, default "desc") — sort direction, one of: asc, desclimit (int, optional, default 50, max 200) — page sizeoffset (int, optional, default 0) — page offset? placeholders — never interpolate user values into SQL stringsGET /expenses/{id} — get a single expense with category name, 404 if not foundPOST /expenses — create an expense, return 201. Validate that category_id references an existing category (return 400 if not). Validate the date is a real ISO date.PUT /expenses/{id} — partial update. Only update fields that are provided (not None). Return 200 with the full updated expense. 404 if not found. If category_id is provided, validate it exists.DELETE /expenses/{id} — delete, return 204. 404 if not found.GET /expenses/export — export as CSV. Support same date_from, date_to, category_id filters. Use Python's csv module with io.StringIO. Return as StreamingResponse with:
media_type="text/csv"Content-Disposition: attachment; filename="expenses.csv"id, amount, description, category, dateRoute order: Define /expenses/export BEFORE /expenses/{id} to prevent FastAPI from matching "export" as an {id} parameter.
GET /summary — monthly summary. Required query param: month (str, format YYYY-MM, validated with regex). Use SQL GROUP BY to compute:
total: sum of all expense amounts in that monthcount: number of expensesby_category: list of {category_name, total, count} for each category with expenses that monthtotal: 0.0, count: 0, by_category: []Enable CORS using FastAPI's CORSMiddleware:
app.add_middleware(
CORSMiddleware,
allow_origins=["*"],
allow_methods=["*"],
allow_headers=["*"],
)
Single file, organized top-to-bottom:
fastapi, sqlite3, csv, io, datetime, contextlib)DB_PATH = "expenses.db")lifespan async context manager that calls DB initget_db() helper{id} route)if __name__ == "__main__": uvicorn.run("expense_tracker:app", host="0.0.0.0", port=8000, reload=True)