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.

Est. 55 min Updated Suggest a fix
// Part 01 — Persistence

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.

💡 Note
SQLite is excellent for local tools, prototypes, tests, and single-node applications with a suitable write pattern. A database server may be a better fit for concurrent, multi-instance production services. Choose from operational requirements, not fashion.
// Part 02 — sqlite3

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.

Create a small orders table
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.

Insert and retrieve rows with placeholders
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)
⚠️ Important
Never assemble SQL with f-strings or string concatenation for values: 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.
// Part 03 — Rows and Transactions

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.

Named column lookup and a transaction
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.

// Part 04 — SQLAlchemy

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.

Declare a model with SQLAlchemy 2 style
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.

// Part 05 — Sessions and Queries

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.

Create, commit, and query
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.

🎯 Pro Tip
At the service boundary, define who owns the session and transaction. A helper that commits unexpectedly can make a larger operation impossible to roll back as one unit.
// Part 06 — Choosing a Layer

Use the Simplest Abstraction That Fits

ApproachUseful whenTrade-off
sqlite3Small script, simple schema, learning SQL, precise controlYou write SQL and map rows yourself
SQLAlchemy CoreParameterized SQL composition and portable SQL expressionRequires learning its expression API
SQLAlchemy ORMDomain objects, relationships, and unit-of-work changesMust 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.

// Part 07 — Common Mistakes

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.
📌 Real World Example
If confirming an order also writes an audit event, perform both writes in one transaction. If the event insert fails, do not leave the order marked confirmed without its required audit record.
// Part 08 — Practice

Practice: Persist a Small Order Lifecycle

  1. Write a sqlite3 table with a primary key, required customer, positive amount constraint, and status.
  2. Insert and retrieve a record with placeholders. Try a value containing an apostrophe to confirm it is treated as data.
  3. Write a transaction that changes status and inserts an event; deliberately trigger a constraint error and confirm rollback.
  4. Map the table with SQLAlchemy typed declarations and select all orders in a chosen status.
  5. 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 pytest

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...