Python and Databases — sqlite3 and SQLAlchemy
Persist application data with SQLite, parameterize SQL safely, and use SQLAlchemy models and sessions without hiding the underlying database concepts.
Move from In-Memory Objects to Durable Records
Lists and dictionaries disappear when a process exits. A database stores structured records, lets you query them, and supports transactions so related changes can succeed or fail together. We will use SQLite, a file-based relational database included with Python, to learn SQL and transactions with the built-in sqlite3 module; then we will express the same concepts with SQLAlchemy.
Know basic SQL concepts: tables, rows, columns, primary keys, and parameterized queries. We use a delivery order example with an integer identifier, customer name, amount in paise, and state. A relational database schema—not a Python annotation—enforces constraints that need to survive across every application path.
Connect, Create a Schema, and Query with Parameters
The built-in module avoids an external dependency. A connection manages database access; a cursor executes SQL. Use placeholders for values instead of inserting data into the SQL string. Placeholders protect values from being interpreted as SQL and keep query logic separate from data.
import sqlite3
from pathlib import Path
DB_PATH = Path("orders.sqlite3")
with sqlite3.connect(DB_PATH) as connection:
connection.execute("PRAGMA foreign_keys = ON")
connection.execute("""
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
total_paise INTEGER NOT NULL CHECK (total_paise > 0),
status TEXT NOT NULL
)
""")
connection.commit()The connection context manager commits a successful transaction and rolls back when an exception escapes the block; it does not close the connection itself. For short-lived scripts, a with sqlite3.connect(...) block still needs an explicit close or a helper that manages connection lifetime.
import sqlite3
with sqlite3.connect("orders.sqlite3") as connection:
cursor = connection.execute(
"INSERT INTO orders (customer, total_paise, status) VALUES (?, ?, ?)",
("Asha", 129900, "placed"),
)
order_id = cursor.lastrowid
row = connection.execute(
"SELECT id, customer, total_paise, status FROM orders WHERE id = ?",
(order_id,),
).fetchone()
print(row)f"... WHERE customer = '{name}'" is both fragile and unsafe. Use the database driver's parameter placeholders. Placeholders bind values, not table or column names; dynamic identifiers need a strict allow-list.Return Readable Rows and Group Related Writes
By default, SQLite rows are tuples in column order. Set row_factory to sqlite3.Row when name-based lookup improves readability. A transaction makes a set of related writes atomic: either all changes commit, or none do.
import sqlite3
with sqlite3.connect("orders.sqlite3") as connection:
connection.row_factory = sqlite3.Row
with connection:
connection.execute(
"UPDATE orders SET status = ? WHERE id = ?",
("confirmed", 1),
)
connection.execute(
"INSERT INTO order_events (order_id, event) VALUES (?, ?)",
(1, "confirmed"),
)
row = connection.execute(
"SELECT id, customer, status FROM orders WHERE id = ?", (1,)
).fetchone()
if row is not None:
print(row["customer"], row["status"])In real code, handle expected constraint or operational errors at an appropriate boundary and make sure a failed operation does not leave a partially updated domain state. Avoid swallowing every exception. Keep connection lifetime predictable; do not share one SQLite connection casually across threads.
Use a Higher-Level Mapping Without Forgetting SQL
SQLAlchemy is an external library: install it in the active virtual environment with pip install sqlalchemy. Its ORM maps Python classes to tables and tracks changes through a session. The SQLAlchemy 2 style uses a declarative base, typed mapped attributes, and select(). The ORM is not a replacement for schema design, transactions, indexes, or understanding generated queries.
from sqlalchemy import Integer, String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
customer: Mapped[str] = mapped_column(String(120), nullable=False)
total_paise: Mapped[int] = mapped_column(Integer, nullable=False)
status: Mapped[str] = mapped_column(String(30), nullable=False)
engine = create_engine("sqlite:///orders_orm.sqlite3")
Base.metadata.create_all(engine)create_all() creates missing tables for a simple example; it is not a migration system. Once a schema is in use, use explicit versioned migrations so changes can be reviewed and safely applied to existing data.
Keep the Session Short and the Transaction Clear
A SQLAlchemy Session is a unit-of-work and identity map, not a global connection. It tracks object changes, emits SQL when needed, and coordinates a transaction. Use a context manager so the session closes reliably; commit intentionally, and roll back if a transaction fails.
from sqlalchemy import select
from sqlalchemy.orm import Session
with Session(engine) as session:
with session.begin():
order = Order(
customer="Asha",
total_paise=129900,
status="placed",
)
session.add(order)
with Session(engine) as session:
statement = select(Order).where(Order.status == "placed")
orders = session.scalars(statement).all()
for order in orders:
print(order.id, order.customer, order.total_paise)ORM expressions still become SQL. For complex reports, bulk operations, or database-specific features, inspect generated statements and consider an explicit SQL query. Be deliberate about loading related objects to avoid issuing an unexpected query for every row.
Use the Simplest Abstraction That Fits
| Approach | Useful when | Trade-off |
|---|---|---|
| sqlite3 | Small script, simple schema, learning SQL, precise control | You write SQL and map rows yourself |
| SQLAlchemy Core | Parameterized SQL composition and portable SQL expression | Requires learning its expression API |
| SQLAlchemy ORM | Domain objects, relationships, and unit-of-work changes | Must understand sessions, query behavior, and migrations |
In production, also decide how the database file or server is configured, who owns migrations, how backups and restores are tested, what data should be indexed, and how credentials are managed. Keep secrets out of source control. For server databases, connection pooling and deployment topology become important operational concerns.
Database Bugs Often Hide at Boundaries
- Interpolating values into SQL: SQL can be malformed or manipulated. Bind values with the driver's placeholders.
- Forgetting transaction behavior: unrelated commits can leave partial state. Group related writes and roll back on failure.
- Assuming ORM means no SQL knowledge: generated queries still have performance and correctness consequences. Inspect them when behavior surprises you.
- Treating create_all as migrations: it does not manage safe evolution of an existing schema. Use versioned migrations for changes.
- Keeping sessions globally forever: stale state and transaction leaks result. Scope sessions to a request or unit of work.
- Using SQLite for every workload: concurrency, scaling, and operational needs may require a server database. Test the production-shaped workload.
Practice: Persist a Small Order Lifecycle
- Write a
sqlite3table with a primary key, required customer, positive amount constraint, and status. - Insert and retrieve a record with placeholders. Try a value containing an apostrophe to confirm it is treated as data.
- Write a transaction that changes status and inserts an event; deliberately trigger a constraint error and confirm rollback.
- Map the table with SQLAlchemy typed declarations and select all orders in a chosen status.
- Add tests for an invalid amount, a missing order, and a failed multi-write transaction using a temporary or in-memory database.
Think through: which parts are enforced by schema constraints, which are domain rules, and which belong to input validation or authorization?
🎯 Key Takeaways
- ✓A relational schema expresses durable constraints; Python type annotations alone do not protect stored records.
- ✓sqlite3 is built in and makes SQL explicit; bind every value through placeholders rather than string interpolation.
- ✓Transactions group related writes atomically, and failures should leave data in a known state.
- ✓SQLAlchemy 2 style maps typed Python classes and uses short-lived sessions to coordinate unit-of-work changes.
- ✓ORMs still generate SQL; understand queries, indexes, constraints, and relationship loading.
- ✓create_all is not a schema migration strategy; plan versioned migrations and backups for deployed data.
- ✓Choose SQLite, Core, or ORM based on workload and team needs, not abstraction for its own sake.
What comes next
Lesson 41 turns these concepts into tests that catch regressions before they reach a user.
Lesson 41 → Unit Testing with pytestHELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
