Databases and ORMs in Python
From the raw sqlite3 DB-API up to SQLAlchemy models, sessions and migrations
sqlite3), where you write SQL by hand, and an ORM (SQLAlchemy), which maps table rows to Python objects so you mostly work in Python. Neither replaces the other — the ORM is built on top of the DB-API, and knowing both lets you choose deliberately.
- open a connection, run SQL through a cursor, and
commita transaction withsqlite3, - use parameterized queries to stay safe from SQL injection,
- explain what an ORM maps and why,
- define a SQLAlchemy 2.0 model and drive a Session (the unit of work),
- describe what migrations (Alembic) solve and the real trade-offs of an ORM.
1. Why a database instead of a file?
You already know how to read and write JSON and CSV. Files are perfect for small, single-writer data. A database earns its place when you need any of: querying ("all orders over 100 from last week" without loading everything into memory), concurrency (many readers and writers without corrupting the file), integrity (types, uniqueness, foreign keys enforced by the engine), and transactions (a group of changes that either all happen or none do).
SQLite is the gentlest on-ramp: it is a full SQL engine that lives inside a single file, ships with Python as the sqlite3 module, and needs no server. The same DB-API you learn here is the standard interface (PEP 249) that psycopg for PostgreSQL and drivers for MySQL also implement, so the shapes transfer.