SQLite & ORMs

Published by Brayan K

Master database operations with SQLite and SQLAlchemy ORM for building data-driven applications

Part of the free Python course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

A connection, a row and a value are different things

SQLite runs inside your Python process. A connection to ":memory:" owns a temporary database; a connection to a filename can keep data after the program ends. A cursor reads query results, and a row is one result. None of these is a Python list that automatically saves itself when you change it.

This lesson owns the Python/database boundary: binding values, transaction lifetime and SQLAlchemy sessions. SQL syntax has its own SQL course; the resource-management protocol is explained in Context Managers & the with Statement in Depth.

The placeholders below bind data separately from the SQL statement. The apostrophe in a learner's name needs no manual escaping. execute() returns a cursor; fetchall() consumes its remaining rows. ORDER BY makes the display order explicit.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
try:
    db.execute("CREATE TABLE learner (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
    db.execute("BEGIN")
    db.executemany("INSERT INTO learner (name) VALUES (?)", [("O'Neil",), ("Ada",)])
    db.commit()
    cursor = db.execute("SELECT id, name FROM learner ORDER BY id")
    print(cursor.fetchall())
    print(cursor.fetchone())
finally:
    db.close()


# ✅ Expected output:
# [(1, "O'Neil"), (2, 'Ada')]
# None

isolation_level=None disables the sqlite3 module's implicit transaction opening. We use explicit BEGIN, then commit or roll back, so the boundary is visible and works on Python 3.11–3.14. Outside an explicit transaction, SQLite commits each standalone statement. Do not remove BEGIN and expect several writes to remain one operation.

A single bound value still needs a sequence. Parentheses around a string do not make a tuple; the comma does. Here the driver sees three characters for one placeholder, and stops before inserting anything:

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.execute("CREATE TABLE learner (name TEXT)")
    db.execute("INSERT INTO learner VALUES (?)", ("Ada"))
finally:
    db.close()

# ✅ Expected error:
# sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

Repair that argument to ("Ada",). Placeholders represent values, not table names or keywords. If a user chooses a sort column, map the choice to a small allow-list of known columns; do not interpolate arbitrary input into SQL.

Two writes must succeed together

A transfer has two updates. Committing the debit before attempting the credit can lose money if the second step fails. Put both writes in one transaction and let an exception leave its context. A connection's with block commits an open transaction on success and rolls it back on failure; it does not close the connection.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
try:
    db.execute("CREATE TABLE wallet (name TEXT PRIMARY KEY, pence INTEGER)")
    db.executemany("INSERT INTO wallet VALUES (?, ?)", [("Ada", 500), ("Bo", 100)])
    try:
        with db:
            db.execute("BEGIN")
            db.execute("UPDATE wallet SET pence = pence - ? WHERE name = ?", (200, "Ada"))
            credit = db.execute("UPDATE wallet SET pence = pence + ? WHERE name = ?", (200, "Missing"))
            if credit.rowcount != 1:
                raise ValueError("recipient does not exist")
    except ValueError as error:
        print(type(error).__name__ + ":", error)
    print(db.execute("SELECT name, pence FROM wallet ORDER BY name").fetchall())
finally:
    db.close()


# ✅ Expected output:
# ValueError: recipient does not exist
# [('Ada', 500), ('Bo', 100)]

The except is outside with db. Catching and suppressing the failure inside that block would make its exit look successful and could commit the debit. The final query also proves the connection remains usable until close().

Database constraints provide a second defence when another caller bypasses Python validation. Here a nonnegative-stock rule is enforced by SQLite itself:

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.execute("CREATE TABLE stock (quantity INTEGER CHECK (quantity >= 0))")
    db.execute("INSERT INTO stock VALUES (?)", (-1,))
finally:
    db.close()

# ✅ Expected error:
# sqlite3.IntegrityError: CHECK constraint failed: quantity >= 0

Do not catch this error and report that the save succeeded. Decide whether the caller can correct the input; keep related changes in the same transaction so rejection leaves a consistent state.

SQLAlchemy tracks objects inside a session

An ORM connects Python objects to database rows; it does not remove SQL or transaction rules. These examples use SQLAlchemy's 2.0 API. For local Python, install the course's checked dependency with python -m pip install SQLAlchemy==2.0.52. Package isolation belongs to Virtual Environments & Dependency Management Best Practices.

Engine manages database connectivity. A mapped class describes a table, Mapped describes an attribute's type, and Session tracks pending object changes. Use an engine across operations, but keep each session short-lived. A session is not a global object to share across concurrent requests.

from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Task(Base):
    __tablename__ = "task"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    done: Mapped[bool] = mapped_column(default=False)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session:
    with session.begin():
        task = Task(title="Practise transactions")
        session.add(task)
        session.flush()
        print("assigned id:", task.id)
        task.done = True
with Session(engine) as session:
    with session.begin():
        tasks = session.scalars(select(Task).order_by(Task.id)).all()
        print([(task.title, task.done) for task in tasks])
        session.delete(tasks[0])
with Session(engine) as session:
    print("remaining:", len(session.scalars(select(Task)).all()))
engine.dispose()


# ✅ Expected output:
# assigned id: 1
# [('Practise transactions', True)]
# remaining: 0

session.begin() commits on a normal exit and rolls back on an exception. The surrounding Session context closes the session. A plain with Session(engine) alone does not commit for you. scalars(select(Task)) yields Task objects, whereas execute(select(Task)) yields result rows containing them.

flush() sends pending SQL and can assign a generated ID. It is not a commit. Queries may also trigger a flush automatically. This independent example proves a flushed row can still disappear on rollback:

from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Note(Base):
    __tablename__ = "note"
    id: Mapped[int] = mapped_column(primary_key=True)
    text: Mapped[str]

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session:
    note = Note(text="not saved")
    session.add(note)
    session.flush()
    print("has id:", note.id is not None)
    session.rollback()
with Session(engine) as session:
    print("stored rows:", len(session.scalars(select(Note)).all()))
engine.dispose()


# ✅ Expected output:
# has id: True
# stored rows: 0

After a failed flush, roll the session back before reusing it, or exit its transaction/session context and start a fresh operation. Also keep attribute reads inside the session when they might load data: committed objects normally expire, and lazy relationships can require another query. Returning a simple tuple or dictionary of the values you need avoids accidentally triggering database access while displaying a result.

A relationship can hide another query

Fetching authors once does not necessarily fetch their books. Accessing each unloaded collection can issue one query per author: the N+1 problem. Count statements instead of guessing from how short the Python loop looks. Both reads below use a fresh session, so previously cached collections cannot hide the difference.

from sqlalchemy import create_engine, ForeignKey, select, event
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session, selectinload

class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = "author"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    books: Mapped[list["Book"]] = relationship(order_by="Book.id")

class Book(Base):
    __tablename__ = "book"
    id: Mapped[int] = mapped_column(primary_key=True)
    author_id: Mapped[int] = mapped_column(ForeignKey("author.id"))
    title: Mapped[str]

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    for name in ["Ada", "Bo", "Cy"]:
        session.add(Author(name=name, books=[Book(title=name + "'s notes")]))

statements = []
def record(connection, cursor, statement, parameters, context, many):
    statements.append(statement)
event.listen(engine, "before_cursor_execute", record)

for eager in [False, True]:
    statements.clear()
    with Session(engine) as session:
        query = select(Author).order_by(Author.id)
        if eager:
            query = query.options(selectinload(Author.books))
        authors = session.scalars(query).all()
        print([len(author.books) for author in authors])
    print("select-in" if eager else "lazy", len(statements))
engine.dispose()


# ✅ Expected output:
# [1, 1, 1]
# lazy 4
# [1, 1, 1]
# select-in 2

ForeignKey identifies the stored reference; relationship() gives Python access to related objects. selectinload() loads these three collections in a second SELECT using their parent IDs. That two-query result is for this fixture, not a promise for every dataset: large parent batches can need more SELECTs. Joined eager loading is another strategy, but joining a collection repeats parent rows and requires result de-duplication with unique().

Creating tables with create_all() does not upgrade an existing table when you edit a class. Real deployments need a reviewed schema migration and a backup/rollback plan. Likewise, SQLite's foreign-key enforcement needs enabling per connection; merely declaring a reference is not evidence that a database has rejected orphan rows.

Finish a conditional stock update

Reserve two notebooks only if enough remain. Fill the three value slots, commit the transaction, then inspect both the affected-row count and remaining quantity. Keeping the availability check in the UPDATE avoids a separate read-then-write decision that another transaction could invalidate.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
try:
    db.execute("CREATE TABLE stock (id INTEGER PRIMARY KEY, quantity INTEGER)")
    db.execute("INSERT INTO stock VALUES (1, 5)")
    with db:
        db.execute("BEGIN")
        result = db.execute(
            "UPDATE stock SET quantity = quantity - ? WHERE id = ? AND quantity >= ?",
            (___, ___, ___),
        )
    print("reserved:", result.rowcount == 1)
    print("remaining:", db.execute("SELECT quantity FROM stock WHERE id = 1").fetchone()[0])
finally:
    db.close()

# 👉 replace ___ with 2
# 👉 replace ___ with 1
# 👉 replace ___ with 2


# ✅ Expected output:
# reserved: True
# remaining: 3

Build a lending ledger that rejects duplicate loans

Create an in-memory loan table with a unique book title and a borrower. Insert Python Notes for Ada and commit. Attempt to lend the same title to Bo in a second transaction. Handle the uniqueness failure outside the transaction, print duplicate rejected, then print the remaining title/borrower rows ordered by title. Close the connection even if something fails. The original loan must survive.

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)
try:
    db.execute("CREATE TABLE loan (title TEXT UNIQUE NOT NULL, borrower TEXT NOT NULL)")
    with db:
        db.execute("BEGIN")
        db.execute("INSERT INTO loan VALUES (?, ?)", ("Python Notes", "Ada"))
    try:
        with db:
            db.execute("BEGIN")
            db.execute("INSERT INTO loan VALUES (?, ?)", ("Python Notes", "Bo"))
    except sqlite3.IntegrityError:
        print("duplicate rejected")
    print(db.execute("SELECT title, borrower FROM loan ORDER BY title").fetchall())
finally:
    db.close()


# ✅ Expected output:
# duplicate rejected
# [('Python Notes', 'Ada')]

For the APIs used here, see the sqlite3 reference, SQLAlchemy session guide and relationship loading guide.

Practice quiz

An INSERT has one placeholder. Its parameters argument is ("Ada"), which causes a binding error. Which replacement binds the whole name once?

  • ("Ada")
  • ("Ada",)
  • tuple("Ada")
  • list("Ada")

Answer: ("Ada",). The comma makes a one-item tuple. The other expressions supply a string or a sequence of its three characters, not one parameter.

A transfer opens a transaction with BEGIN inside with db, debits the sender, then catches and suppresses a missing-recipient error inside that block. It exits normally. What does the context manager do?

  • Roll back the debit because an error occurred
  • Leave the transaction open for the caller
  • Close the connection without committing
  • Commit the open transaction, including the debit

Answer: Commit the open transaction, including the debit. The context manager sees a normal exit and commits. Catch the error outside with db so it can roll back the whole transfer; the context does not close the connection.

An empty mapped table already exists. A session adds one row, flushes it, then rolls back. A fresh session prints the table's row count. What does it print?

  • 0
  • 1
  • A closed-session error
  • A missing-table error

Answer: 0. Flushing sends the INSERT and can assign an ID, but rollback undoes the insert. The table existed before this transaction; a fresh session can still query it.

A function adds an object inside with Session(engine), then exits without committing. Which change gives the write a commit-on-success, rollback-on-error boundary?

  • Call session.flush() after adding the object
  • Read the assigned ID before leaving the block
  • Wrap the write in with session.begin()
  • Call session.close() before leaving the block

Answer: Wrap the write in with session.begin(). Session cleanup is not a commit. A session.begin() block commits on a successful exit and rolls back if an exception escapes; flush and an assigned ID do not prove a durable save.

In a fresh session, a report loads authors, then issues another SELECT for each author's books. Which change addresses this N+1 pattern?

  • Materialize the authors with .all() before the loop
  • Load the authors with selectinload(Author.books)
  • Commit the session before reading the collections
  • Sort the authors by their IDs before reading books

Answer: Load the authors with selectinload(Author.books). Select-in loading fetches related collections in batches. Materializing or sorting the parent rows does not load those collections; query counts still depend on the data and loader limits.

SELECT * FROM ? fails when the parameter is a table name. Which assumption caused the bug?

  • A value placeholder can stand for SQL syntax
  • A quoted parameter is needed for every string
  • A SELECT accepts only numeric bound values
  • A table name must be passed as a dictionary

Answer: A value placeholder can stand for SQL syntax. Placeholders bind values, not identifiers such as table names. Select identifiers from an application-controlled allow-list; never interpolate arbitrary user input.

A mapped class gains a column, but create_all(engine) leaves the existing table unchanged. What is missing from the deployment?

  • Another call to create_all with the same metadata
  • A fresh Session for each row in the existing table
  • Disposing and recreating the engine connection pool
  • A schema migration that alters the existing table

Answer: A schema migration that alters the existing table. create_all creates missing tables; it does not evolve an existing table to match a changed mapping. A schema change needs a migration strategy.

A positive stock request uses UPDATE with WHERE sku = ? AND quantity >= ?. The statement succeeds, but rowcount is zero. What can the caller conclude?

  • The SQL syntax was rejected before execution
  • The transaction automatically rolled back
  • No row matched both the SKU and available stock
  • The reservation succeeded without changing a row

Answer: No row matched both the SKU and available stock. Zero matched rows means this statement reserved nothing: the SKU may be absent or its quantity too small. It is not a syntax error or an automatic rollback.

Continue this course

Links on this page