MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

Python  Module 8 – Files, paths and data formats

Beyond SQLite: DB-API drivers and ORMs

How Python database drivers share the DB-API 2.0, how psycopg runs SQL on PostgreSQL, and when SQLAlchemy Core or its ORM helps, N+1 queries included.

  • Intermediate
  • 20 minutes
  • Examples run with Python 3.14.8 and Pyodide 314.0.7
  • By MySmartCoPilot

What you will learn

  • Explain the DB-API 2.0 interface that Python database drivers share
  • Convert SQL between the placeholder styles of PEP 249
  • Compare psycopg on PostgreSQL with sqlite3 for placeholders, transactions, types and with blocks
  • Trace a query from SQLAlchemy Core down to the driver
  • Detect N+1 queries, fix them with eager loading, and decide when an ORM helps

Before you start

What you’ll learn · Before you start

What you will learn

  • Explain the DB-API 2.0 interface that Python database drivers share
  • Convert SQL between the placeholder styles of PEP 249
  • Compare psycopg on PostgreSQL with sqlite3 for placeholders, transactions, types and with blocks
  • Trace a query from SQLAlchemy Core down to the driver
  • Detect N+1 queries, fix them with eager loading, and decide when an ORM helps

Before you start

On this page

A page that lists 10 authors and counts their books sent 11 SQL statements through an object-relational mapper (ORM): A library that maps classes to tables and objects to rows, such as SQLAlchemy's ORM, so that code reads and changes objects while the library writes the SQL., where 2, or even 1, would do. To see why, and to move code between databases without surprises, you need the layers under it. Most Python database drivers follow one specification, PEP 249, the Python Database API 2.0, which is why code for PostgreSQL, MySQL, SQL Server or Oracle looks like the sqlite3 code of the previous lesson. What differs from driver to driver is how you connect, the placeholder style and some behaviour around transactions and types.

On top of the drivers sits SQLAlchemy, a widely used database toolkit for Python. Its Core builds SQL from Python expressions and manages connections; its ORM, an object-relational mapper, maps classes to tables so that code works with objects. You will tour the DB-API with sqlite3, run the same work on a real PostgreSQL 18 server through psycopg, follow SQLAlchemy’s queries down to the driver, and fix those N+1 queries.

Every DB-API driver has the same shape: connect, cursor, execute, fetch

A DB-API module describes itself with three attributes, and everything after connect() follows the specification:

The DB-API 2.0 surface, shown with sqlite3 Python · dbapi_tour.py 
import sqlite3

# Every DB-API 2.0 driver module describes itself with three attributes (PEP 249).
print("apilevel:", sqlite3.apilevel, "| threadsafety:", sqlite3.threadsafety, "| paramstyle:", sqlite3.paramstyle)

con = sqlite3.connect(":memory:")  # connect(): the driver's own arguments
cur = con.cursor()  # a cursor runs statements and holds their results
cur.execute("CREATE TABLE courses (code TEXT PRIMARY KEY, title TEXT, seats INTEGER)")
cur.executemany(
    "INSERT INTO courses VALUES (?, ?, ?)",
    [("CS101", "Programming in Python", 120), ("CS205", "Databases", 60), ("MA110", "Linear Algebra", 90)],
)
print("rows inserted:", cur.rowcount)
con.commit()

cur.execute("SELECT code, title, seats FROM courses WHERE seats >= ? ORDER BY seats DESC", (80,))
print("columns:", [d[0] for d in cur.description])  # one 7-item tuple per column; the name comes first
print("first row:", cur.fetchone())
print("the rest:", cur.fetchall())

# The standard exception classes, in one hierarchy that every driver shares.
try:
    cur.execute("INSERT INTO courses VALUES (?, ?, ?)", ("CS205", "Databases again", 30))
except sqlite3.DatabaseError as err:  # IntegrityError is a DatabaseError, which is an Error
    print(type(err).__name__, "->", " -> ".join(c.__name__ for c in type(err).__mro__[1:4]))
    con.rollback()
con.close()

Output

apilevel: 2.0 | threadsafety: 1 | paramstyle: qmark
rows inserted: 3
columns: ['code', 'title', 'seats']
first row: ('CS101', 'Programming in Python', 120)
the rest: [('MA110', 'Linear Algebra', 90)]
IntegrityError -> DatabaseError -> Error -> Exception

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 dbapi_tour.py

Version note

In the browser the first line says threadsafety: 0. The SQLite inside Pyodide is built for a single thread, which is all a browser page runs Python in, while this lesson’s CPython uses a multi-threaded SQLite build, reported as 1. The rest of the output is the same.

In your browser, Pyodide 314.0.7 (CPython 3.14.2) prints: 

apilevel: 2.0 | threadsafety: 0 | paramstyle: qmark
rows inserted: 3
columns: ['code', 'title', 'seats']
first row: ('CS101', 'Programming in Python', 120)
the rest: [('MA110', 'Linear Algebra', 90)]
IntegrityError -> DatabaseError -> Error -> Exception

Step through the program call by call. Every call is one that PEP 249 asks of every driver, so the same steps work with psycopg or mysqlclient:

Interactive · message by message The DB-API 2.0, one call at a time Step through dbapi_tour.py. Every call it makes is one that PEP 249 asks of every driver, so the same steps work with psycopg or mysqlclient.
The DB-API 2.0, one call at a time, step 9 of 9. Messages so far: Your code to The cursor: con.cursor(); The cursor to SQLite: INSERT × 3, rowcount 3; Your code to SQLite: con.commit(); The cursor to SQLite: SELECT … seats >= ?; The cursor to Your code: ('CS101', …, 120); The cursor to Your code: [('MA110', …, 90)]; SQLite to Your code: IntegrityError.Your codedbapi_tour.pyThe cursorcon.cursor()SQLiteclosedcon.cursor()INSERT × 3, rowcount 3con.commit()SELECT … seats >= ?('CS101', …, 120)[('MA110', …, 90)]IntegrityError

The drawing shows the whole scene; its 9 steps are listed below.

Every step as text
  1. The module. The module describes itself with three attributes, apilevel 2.0, threadsafety 1 and paramstyle qmark, so its placeholders are question marks.
  2. connect() and cursor(). sqlite3.connect(":memory:") returns a connection, and con.cursor() returns the cursor that runs statements and holds their results.
  3. executemany(). The cursor runs the INSERT once for each of the 3 tuples, and rowcount adds up the rows of all three runs, 3.
  4. commit(). con.commit() ends the transaction the INSERTs opened. The specification asks that a connection start with auto-commit off, so nothing was saved before this.
  5. A query. The cursor runs the SELECT for courses with 80 seats or more, with 80 as a parameter. description now holds one entry per column, code, title and seats, each with the name first.
  6. fetchone(). fetchone() returns the first row, CS101, Programming in Python, 120 seats.
  7. fetchall(). fetchall() returns the rows that are left, only MA110 with 90 seats. CS205 has 60 seats, fewer than 80.
  8. An error. A second CS205 breaks the primary key. The driver raises IntegrityError, which is a DatabaseError, which is an Error, as PEP 249 arranges them.
  9. rollback() and close(). con.rollback() ends the failed transaction, and con.close() ends the connection.

  • apilevel is "2.0"; threadsafety says what threads may share: 1 means the module but not connections, which is why each thread opens its own connection; paramstyle names the placeholder style.
  • cursor() returns a cursor, and execute() and executemany() run statements on it. sqlite3 also offers the shortcut con.execute(), which is not part of the standard and may not exist in other drivers.
  • description holds one entry per column of the last query, its name first, and rowcount the number of rows a statement touched; here it added up all three inserts of executemany().
  • fetchone(), fetchmany(n) and fetchall() return rows; fetchmany() reads a large result in chunks.
  • The exceptions form one hierarchy. IntegrityError, OperationalError and ProgrammingError derive from DatabaseError, which derives from Error, so except driver.Error catches every database error of that driver.

PEP 249 also says that a database’s auto-commit mode, if it has one, must be off when a connection opens: changes belong to a transaction: A group of SQL statements that a database applies together or not at all: commit() makes every change permanent, and rollback() or an error undoes all of them. Other connections see the changes only after the commit. More in “SQLite databases with sqlite3” until commit(). Drivers differ in exactly when they start that transaction, which matters as soon as you move between them.

Checkpoint

Check: The shape every driver shares

  1. Question 1 of 2 After cur.execute("SELECT code, title, seats FROM courses WHERE seats >= ?", (80,)), what does [d[0] for d in cur.description] give?

    Choose one answer.

    Show the answer

    Answer: ['code', 'title', 'seats']

    PEP 249 makes description a sequence with one 7-item entry per result column, the name first, so the list comprehension collects the three column names, as dbapi_tour.py prints.

  2. Question 2 of 2 Which except clause catches every database error that a DB-API driver raises, whatever its kind?

    Choose one answer.

    Show the answer

    Answer: except driver.Error

    The hierarchy has Error at its root, with InterfaceError and DatabaseError under it and IntegrityError, OperationalError, ProgrammingError and the others under DatabaseError, so except driver.Error catches them all.

Placeholder styles differ between drivers

PEP 249 allows five values of paramstyle, and code that works with one driver breaks with another if it ignores them. Each style writes its placeholder (SQL): A marker such as ? or :name where a value goes in an SQL statement. The driver sends the values apart from the SQL text, and the database binds them to the compiled statement as data. More in “SQLite databases with sqlite3” differently:

Style Placeholder Values passed as Used by
qmark ? a sequence sqlite3
numeric :1 a sequence some drivers; sqlite3 reads :1 as a name
named :name a mapping sqlite3 and SQLAlchemy’s text()
format %s a sequence psycopg, with a sequence
pyformat %(name)s a mapping psycopg, its paramstyle

The %s of the format styles is a marker, not Python string formatting: the driver still sends the values apart from the SQL, and the psycopg documentation tells you never to merge values into a query with % or + yourself. Because % is special to those drivers, a literal percent sign in a query that has parameters, as in LIKE 'A%', is written %%. This lesson’s exercise converts one placeholder style into all five.

Checkpoint

Check: The five placeholder styles

  1. One question This query works with sqlite3. Which version does psycopg need?

    Read the code, then choose one answer.

    SELECT * FROM t WHERE name LIKE 'A%' AND id = ?
    Show the answer

    Answer: SELECT * FROM t WHERE name LIKE 'A%%' AND id = %s

    psycopg's paramstyle is pyformat: %s with a sequence or %(name)s with a mapping. Every % in the query is read as the start of a marker, so a literal one is written %%, as the lesson's exercise does.

psycopg on PostgreSQL opens a transaction at the first statement

PostgreSQL is a database server: the database runs in its own process, often on another machine, and many clients connect to it at once. The example starts a private, throwaway PostgreSQL 18 server in a folder of its own, listening only on 127.0.0.1, an address no other computer can reach, runs the queries through psycopg 3 and stops the server again. It needs PostgreSQL’s programs and the psycopg package on your computer, so this page shows its recorded output:

A throwaway PostgreSQL 18 server, used through psycopg Python · pg_session.py 
"""The DB-API with PostgreSQL 18 through psycopg 3, on a throwaway server.

The example needs PostgreSQL's server programs (initdb and pg_ctl) and the psycopg package. It creates a database
cluster in a folder of its own, starts the server listening only on 127.0.0.1, an address no other computer can
reach, runs its queries and stops the server again, also when something fails.
"""
import shutil
import socket
import subprocess
import sys
import tempfile
from pathlib import Path

import psycopg
from psycopg import errors


def postgres_bin():
    """The folder that holds pg_ctl: from PATH, or from the mise installation beside this Python."""
    on_path = shutil.which("pg_ctl")
    if on_path:
        return Path(on_path).parent
    installs = Path(sys.executable).resolve().parents[3]  # <mise>/installs/python/3.14.8/bin/python3
    found = sorted(installs.glob("postgres/18.*/bin/pg_ctl"))
    if not found:
        sys.exit("PostgreSQL 18 was not found: install it so that pg_ctl is on PATH")
    return found[-1].parent


PG = postgres_bin()
work = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
data = work / "data"
subprocess.run([PG / "initdb", "-D", data, "-U", "postgres", "-A", "trust", "-E", "UTF8", "--no-locale"],
               check=True, capture_output=True)
with socket.socket() as probe:  # ask the operating system for a port that nothing uses
    probe.bind(("127.0.0.1", 0))
    port = probe.getsockname()[1]
with open(data / "postgresql.conf", "a") as conf:
    conf.write(f"listen_addresses = '127.0.0.1'\nport = {port}\nunix_socket_directories = ''\n")
subprocess.run([PG / "pg_ctl", "-D", data, "-l", work / "server.log", "-w", "start"], check=True, capture_output=True)

try:
    with psycopg.connect(host="127.0.0.1", port=port, dbname="postgres", user="postgres") as conn:
        print("PostgreSQL", conn.info.parameter_status("server_version"), "| psycopg", psycopg.__version__,
              "| paramstyle:", psycopg.paramstyle)
        with conn.cursor() as cur:
            cur.execute("CREATE TABLE courses (code text PRIMARY KEY, title text NOT NULL,"
                        " seats integer NOT NULL, fee numeric(8, 2) NOT NULL)")
            cur.executemany("INSERT INTO courses VALUES (%s, %s, %s, %s)", [
                ("CS101", "Programming in Python", 120, "4500.00"),
                ("CS205", "Databases", 60, "5200.50"),
                ("MA110", "Linear Algebra", 90, "3900.00"),
            ])
            cur.execute("SELECT code, seats, fee FROM courses WHERE seats >= %(min)s ORDER BY code", {"min": 80})
            print("rows:", cur.fetchall())  # numeric arrives as Decimal, never as a float
            cur.execute("INSERT INTO courses VALUES (%s, %s, %s, %s) RETURNING code, seats * 2",
                        ("PH120", "Physics Lab", 40, "6000.00"))
            print("RETURNING:", cur.fetchone())
        print("transaction:", conn.info.transaction_status.name)  # the first execute() opened one
        conn.commit()

        try:
            conn.execute("INSERT INTO courses VALUES (%s, %s, %s, %s)", ("CS205", "Databases again", 30, "100.00"))
        except errors.UniqueViolation as err:
            print(type(err).__name__, "| an IntegrityError:", isinstance(err, psycopg.IntegrityError), "|",
                  err.diag.message_primary)
        try:
            conn.execute("SELECT count(*) FROM courses")
        except errors.InFailedSqlTransaction as err:
            print(type(err).__name__, "|", err.diag.message_primary)
        conn.rollback()  # PostgreSQL refuses every statement after an error until the transaction ends
        print("after rollback:", conn.execute("SELECT count(*) FROM courses").fetchone()[0], "courses")
    print("connection closed by with:", conn.closed)
finally:
    subprocess.run([PG / "pg_ctl", "-D", data, "-m", "fast", "-w", "stop"], capture_output=True)
    shutil.rmtree(work, ignore_errors=True)

Output

PostgreSQL 18.6 | psycopg 3.3.6 | paramstyle: pyformat
rows: [('CS101', 120, Decimal('4500.00')), ('MA110', 90, Decimal('3900.00'))]
RETURNING: ('PH120', 80)
transaction: INTRANS
UniqueViolation | an IntegrityError: True | duplicate key value violates unique constraint "courses_pkey"
InFailedSqlTransaction | current transaction is aborted, commands ignored until end of transaction block
after rollback: 4 courses
connection closed by with: True

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 pg_session.py

Step through the session and watch the state of the transaction on the connection:

Interactive · message by message One psycopg session on PostgreSQL 18, transaction by transaction Step through pg_session.py. Watch the state of the transaction on the connection, and what the server does after an error.
One psycopg session on PostgreSQL 18, transaction by transaction, step 9 of 9. Messages so far: Your code to psycopg connection: psycopg.connect(); psycopg connection to PostgreSQL 18: BEGIN, CREATE TABLE; PostgreSQL 18 to Your code: Decimal('4500.00'), …; PostgreSQL 18 to Your code: ('PH120', 80); Your code to PostgreSQL 18: commit(); PostgreSQL 18 to Your code: UniqueViolation; Your code to PostgreSQL 18: rollback(), then count: 4.Your codepg_session.pypsycopg connectionclosedPostgreSQL 18on 127.0.0.1psycopg.connect()BEGIN, CREATE TABLEDecimal('4500.00'), …('PH120', 80)commit()UniqueViolationrollback(), then count: 4

The drawing shows the whole scene; its 9 steps are listed below.

Every step as text
  1. Connect. psycopg.connect() opens a connection to the throwaway server, which listens only on 127.0.0.1. Its paramstyle is pyformat, so placeholders are %s and %(name)s.
  2. The first statement. The first execute(), a CREATE TABLE, makes psycopg begin a transaction first. Every statement after it runs inside that transaction until commit() or rollback().
  3. Inserts and a query. executemany() inserts 3 courses with %s placeholders. The SELECT with %(min)s returns two rows, and each numeric(8, 2) fee arrives as a Decimal, such as Decimal('4500.00').
  4. RETURNING. An INSERT with RETURNING hands back values the server computed: ('PH120', 80), where 80 is seats * 2.
  5. Still in the transaction. The example prints the status, INTRANS, although it never asked for a transaction. Nothing is saved yet; commit() saves the 4 courses and ends it.
  6. A duplicate. Inserting CS205 again opens a new transaction and fails with UniqueViolation, the driver's subclass of the standard IntegrityError.
  7. Every statement refused. Even a harmless SELECT count(*) is refused with InFailedSqlTransaction. The server ignores every command until the failed transaction ends.
  8. rollback(). conn.rollback() ends the failed transaction. The count works again and finds 4 courses, the ones committed before.
  9. The block closes it. At the end of the with block, psycopg commits or rolls back and then closes the connection, so conn.closed is True. sqlite3's with leaves its connection open.

Compared with sqlite3, five differences stand out, and all of them come from the driver or the server, not from Python:

  • Placeholders are %s and %(name)s, as paramstyle: pyformat says.
  • Types are stricter and richer. The numeric(8, 2) fee arrives as Decimal('4500.00'), never as a rounded float, and RETURNING hands back values the server computed, such as seats * 2. A column declared integer refuses text, unlike SQLite’s flexible columns.
  • The first execute() opened a transaction, as the INTRANS status shows, even for a SELECT, and nothing is saved until commit(). psycopg’s documentation warns that a session left in this state for a long time, “idle in transaction”, holds locks; commit or roll back promptly.
  • An error poisons the transaction. After the UniqueViolation, a subclass of the standard IntegrityError, PostgreSQL refused even a harmless SELECT with InFailedSqlTransaction, until rollback() ended the transaction. SQLite lets the next statement run.
  • with psycopg.connect(...) as conn: also closes the connection at the end of the block, after committing or rolling back. sqlite3’s with leaves the connection open.

To connect to a real server, pass its address and credentials, psycopg.connect(host="db.internal", dbname="shop", user="app", password=...), or a connection string, and keep the password out of the code: read it from the environment or a secrets file.

Checkpoint

Check: psycopg and PostgreSQL transactions

  1. Question 1 of 2 In a psycopg session, an INSERT fails with UniqueViolation. The code then runs SELECT count(*) on the same connection without rolling back. What happens?

    Choose one answer.

    Show the answer

    Answer: PostgreSQL refuses it with InFailedSqlTransaction

    An error poisons a PostgreSQL transaction: every later statement is refused with InFailedSqlTransaction until rollback() ends it. After the rollback, the example's count finds the 4 committed courses.

  2. Question 2 of 2 In which Python type does psycopg return a value of a numeric(8, 2) column?

    Choose one answer.

    Show the answer

    Answer: decimal.Decimal

    PostgreSQL's numeric is an exact decimal type, and psycopg maps it to Python's Decimal, so money keeps its exact value on the way in and out.

SQLAlchemy Core rewrites your query for the driver underneath

SQLAlchemy is a third-party package, installed with python -m pip install SQLAlchemy; the recorded runs below used version 2.0.48. Everything starts from an engine, created from a URL such as sqlite:///college.db or postgresql+psycopg://app@db.internal/shop. The engine knows the database’s SQL dialect, uses the DB-API driver underneath and keeps a connection pool: A set of open database connections that a program keeps and lends out, as a SQLAlchemy engine does, so that each request does not pay for opening a new connection. of open connections, so a web application does not pay for a new connection on every request:

Layers from Python code to a database: an optional ORM, SQLAlchemy Core, then a DB-API driver such as sqlite3 or psycopg.Your Python codeORM: mapped classes, Session(optional)SQLAlchemy Core: SQL expressions,Engine, connection poolDB-API driver: sqlite3, psycopg,mysqlclient ... (PEP 249)Database: an SQLite file,a PostgreSQL server ...objectsSQL it generatesSQL + parametersthe database's own protocoltext() or select()plain execute()

The layers between your code and the database

Text description of the diagram

The diagram is a column of five boxes, from your Python code at the top to the database at the bottom.

  1. Your Python code.
  2. The ORM, which is optional: classes mapped to tables, and a Session that loads and saves objects. It hands the SQL it generates to the layer below.
  3. SQLAlchemy Core: SQL expressions, the Engine and its pool of connections. It passes SQL and parameters to the driver.
  4. A DB-API driver, the module that follows PEP 249, such as sqlite3, psycopg or mysqlclient. It speaks the database's own protocol.
  5. The database itself: an SQLite file or a PostgreSQL server, for example.

Dashed arrows show that code can skip layers: it can use SQLAlchemy Core directly with text() or select(), or call a driver's execute() with no SQLAlchemy at all, as plain sqlite3 code does.

The example runs one query three ways on the same SQLite file: through sqlite3 directly, through SQLAlchemy’s text(), and as a Core select() expression. An event listener prints what SQLAlchemy actually sent to the driver:

One query through sqlite3 and through SQLAlchemy Core Python · same_query_two_ways.py 
# Needs the third-party SQLAlchemy package: python -m pip install SQLAlchemy
import sqlite3
from pathlib import Path

import sqlalchemy
from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, event, select, text

Path("college.db").unlink(missing_ok=True)  # start from an empty database on every run
rows = [("CS101", "Programming in Python", 120), ("CS205", "Databases", 60), ("MA110", "Linear Algebra", 90)]

# 1. The DB-API driver directly: SQL text, qmark placeholders, tuples back.
con = sqlite3.connect("college.db")
with con:
    con.execute("CREATE TABLE courses (code TEXT PRIMARY KEY, title TEXT NOT NULL, seats INTEGER NOT NULL)")
    con.executemany("INSERT INTO courses VALUES (?, ?, ?)", rows)
print("sqlite3:   ", con.execute("SELECT code, seats FROM courses WHERE seats >= ? ORDER BY code", (80,)).fetchall())
con.close()

# 2. SQLAlchemy Core on the same file. The engine wraps the same sqlite3 driver and keeps a pool of connections.
engine = create_engine("sqlite:///college.db")
sent = []
event.listen(engine, "before_cursor_execute", lambda conn, cur, statement, params, ctx, many: sent.append((statement, params)))

with engine.connect() as conn:
    # text(): your own SQL, always with :name placeholders, whatever the database's driver uses.
    result = conn.execute(text("SELECT code, seats FROM courses WHERE seats >= :min ORDER BY code"), {"min": 80})
    print("text():    ", [tuple(r) for r in result])

    # A Core expression: SQLAlchemy writes the SQL for the database it is connected to.
    courses = Table("courses", MetaData(), Column("code", String, primary_key=True), Column("title", String), Column("seats", Integer))
    query = select(courses.c.code, courses.c.seats).where(courses.c.seats >= 80).order_by(courses.c.code)
    print("select():  ", [tuple(r) for r in conn.execute(query)])

print("SQLAlchemy", sqlalchemy.__version__, "sent to the driver:")
for statement, params in sent:
    print("  ", " ".join(statement.split()), params)
engine.dispose()

Output

sqlite3:    [('CS101', 120), ('MA110', 90)]
text():     [('CS101', 120), ('MA110', 90)]
select():   [('CS101', 120), ('MA110', 90)]
SQLAlchemy 2.0.48 sent to the driver:
   SELECT code, seats FROM courses WHERE seats >= ? ORDER BY code (80,)
   SELECT courses.code, courses.seats FROM courses WHERE courses.seats >= ? ORDER BY courses.code (80,)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 same_query_two_ways.py

Follow the two SQLAlchemy queries down the layers of the diagram, to what the driver received:

Interactive · message by message Two SQLAlchemy queries on their way to the driver Step through same_query_two_ways.py. Watch what the engine hands the sqlite3 driver for a text() query and for a select() expression.
Two SQLAlchemy queries on their way to the driver, step 7 of 7. Messages so far: Your code to SQLAlchemy Core: engine.connect(); Your code to SQLAlchemy Core: text(… :min …), {'min': 80}; SQLAlchemy Core to sqlite3 driver: … seats >= ? (80,); sqlite3 driver to Your code: 2 rows: CS101, MA110; Your code to SQLAlchemy Core: select(…).where(seats >= 80); SQLAlchemy Core to sqlite3 driver: courses.seats >= ? (80,); sqlite3 driver to Your code: 2 rows: CS101, MA110.Your codeconn.execute()SQLAlchemy Corethe engine and its poolsqlite3 driverparamstyle qmarkengine.connect()text(… :min …), {'min': 80}… seats >= ? (80,)2 rows: CS101, MA110select(…).where(seats >= 80)courses.seats >= ? (80,)2 rows: CS101, MA110connection back in the pool

The drawing shows the whole scene; its 7 steps are listed below.

Every step as text
  1. A connection from the pool. with engine.connect() as conn takes a connection from the engine's pool, which wraps an ordinary sqlite3 connection to college.db.
  2. text() with :min. The first query is your own SQL in text(), written with :min, the placeholder text() always takes, and the parameters as a dictionary.
  3. Rewritten for the driver. The engine knows that the sqlite3 driver takes question marks, so it rewrites :min as ? and the dictionary as the tuple (80,). That is what the listener recorded.
  4. The same rows. The driver runs the statement and the rows come back as they did through sqlite3 alone, CS101 with 120 seats and MA110 with 90.
  5. A select() expression. The second query is a Core expression, select() with a where() on courses.c.seats. There is no SQL text in your code at all.
  6. Core writes the SQL. The engine writes the SQL for SQLite, with every column named after its table, and turns the number 80 into a bound parameter as well.
  7. Back to the pool. The rows are the same again. At the end of the with block the connection goes back to the pool for the next caller, and engine.dispose() closes the pooled connections.

text() always takes :name placeholders and SQLAlchemy rewrites them for the driver, here into sqlite3’s ?; the select() expression wrote its own SQL and turned the number 80 into a bound parameter as well. With another database, SQLAlchemy writes the placeholders that database’s driver expects. In everyday use, create_engine(url, echo=True) prints every statement and its parameters, which is the first thing to switch on when a database is slow.

Checkpoint

Check: SQLAlchemy Core and the engine

  1. One question Which placeholder do you write in SQLAlchemy's text(), whatever the database underneath?

    Choose one answer.

    Show the answer

    Answer: :name, for example :min

    text() always uses :name placeholders. The engine knows the driver's paramstyle and rewrites them, so the same text() works with sqlite3, psycopg or any other driver it supports.

SQL Formatter Paste the SQL that echo=True printed and format it to read what the ORM really sent.

Lazy loading sends one query per object: the N+1 problem

The ORM maps a class to a table and its relationships to other classes: author.books is a list of Book objects, loaded when you first use it. That convenience hides SQL, and the most common way it goes wrong has a name. The example stores 10 authors with 3 books each and counts every author’s books, once with the default loading and twice with eager loading:

Ten authors, eleven queries, and two fixes Python · n_plus_one.py 
# Needs the third-party SQLAlchemy package: python -m pip install SQLAlchemy
from sqlalchemy import ForeignKey, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, joinedload, mapped_column, relationship, selectinload


class Base(DeclarativeBase):
    pass


class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80))
    books: Mapped[list["Book"]] = relationship(back_populates="author")  # loaded lazily by default


class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped[Author] = relationship(back_populates="books")


engine = create_engine("sqlite://")  # an in-memory database
statements = []
event.listen(engine, "before_cursor_execute", lambda conn, cur, sql, params, ctx, many: statements.append(" ".join(sql.split())))
Base.metadata.create_all(engine)

with Session(engine) as session:
    for a in range(1, 11):
        session.add(Author(name=f"Author {a}", books=[Book(title=f"Book {a}.{b}") for b in range(1, 4)]))
    session.commit()


def count_books(options=()):
    """Count every author's books, the way a page listing authors would, and the SQL statements it took."""
    statements.clear()
    with Session(engine) as session:
        authors = session.scalars(select(Author).options(*options).order_by(Author.id)).unique().all()
        total = sum(len(author.books) for author in authors)
    return total, list(statements)


for label, options in [("lazy (default)", ()), ("selectinload", (selectinload(Author.books),)), ("joinedload", (joinedload(Author.books),))]:
    total, sent = count_books(options)
    print(f"{label:<15} {total} books, {len(sent)} SQL statement{'s' if len(sent) > 1 else ''}")
    for sql in sent[1:3]:  # what the statements after the first one ask for
        print("    ...", sql[sql.index(" FROM "):])
engine.dispose()

Output

lazy (default)  30 books, 11 SQL statements
    ...  FROM books WHERE ? = books.author_id
    ...  FROM books WHERE ? = books.author_id
selectinload    30 books, 2 SQL statements
    ...  FROM books WHERE books.author_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
joinedload      30 books, 1 SQL statement

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 n_plus_one.py

Step through the three ways of loading and count the statements the session sends for the same 30 books:

Interactive · message by message Ten authors, three ways to load their books Step through n_plus_one.py. Count the SQL statements the session sends for the same 30 books under each kind of loading.
Ten authors, three ways to load their books, step 6 of 6. Messages so far: The ORM session to The database: SELECT … FROM authors; The ORM session to The database: books WHERE ? = author_id; The ORM session to The database: books WHERE author_id IN (…); The ORM session to The database: authors LEFT OUTER JOIN books.Your loop1 statementThe ORM sessionjoinedload()The database10 authors, 30 booksSELECT … FROM authorsbooks WHERE ? = author_idbooks WHERE author_id IN (…)authors LEFT OUTER JOIN bookslazy grows with the authors

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text
  1. Load the authors. session.scalars(select(Author)) sends one SELECT and builds 10 Author objects. Their books are not loaded yet.
  2. The first author.books. The loop reads author.books for the first author. The relationship loads lazily, so this first use sends a SELECT for that author's books.
  3. Nine more. The same happens for each of the other 9 authors, one query each. Counting 30 books took 11 statements, 1 + N with N = 10.
  4. selectinload(). With selectinload(Author.books), the session sends the authors' query and then one more, which fetches every author's books with author_id IN and the 10 ids. 2 statements.
  5. joinedload(). With joinedload(Author.books), one SELECT with a LEFT OUTER JOIN brings the authors and their books together. 1 statement.
  6. At scale. The counts grow differently. Lazy loading sends one more query for every author, so 5,000 authors would cost 5,001 round trips; the two eager loads never send a query per author.

With the default lazy loading: Loading related data only when code first uses it, such as an author's books on the first read of author.books. It saves work when the data is not needed, and costs one query per object in a loop., one query fetched the authors and each first use of author.books sent another query, eleven in all: the N+1 query problem: One query for a list of N rows, then one more query for each row's related data: N + 1 round trips where one or two would do. Lazy loading inside a loop causes it, and eager loading fixes it., which the SQLAlchemy documentation calls one of the most common issues in object-relational mapping. With 10 authors that is invisible; with 5,000 authors on a remote server, at a few milliseconds per round trip, it is many seconds per page. selectinload() fetched every author’s books in a second query with IN (...), two queries in all, and the documentation recommends it for collections. joinedload() used one query with a LEFT OUTER JOIN, and suits many-to-one relationships such as a book’s author. To make a forgotten eager load fail loudly in development, mark relationships lazy="raise", or add raiseload("*") to a query.

Key idea

A relationship read inside a loop costs one query per object unless the query that loads the objects also loads what the loop will read: count the statements, then add selectinload() or joinedload().

Checkpoint

Check: N+1 queries and eager loading

  1. Question 1 of 2 A page lists 20 courses and reads each course's teacher through a relationship that loads lazily. How many SQL statements does it send?

    Type a number.

    A hint

    One query loads the list. What does each first use of the relationship cost?

    Show the answer

    Answer: 21 statements

    One query loads the 20 courses, and each first read of course.teacher sends one more: 20 + 1 = 21, the N+1 pattern. Loading the teachers in the same query, or in one extra query, brings it down to 1 or 2.

  2. Question 2 of 2 Which loader option fetches every author's books in one extra query with IN (...)?

    Choose one answer.

    Show the answer

    Answer: selectinload(Author.books)

    selectinload() is the documentation's recommendation for collections: one query for the parents, then one with IN for their related rows. joinedload() suits many-to-one relationships such as a book's author.

An ORM earns its place in applications with many related tables, where code reads and changes objects and saves them together in a session, the ORM’s unit of work. It also helps when one code base must run on more than one kind of database, such as an application that some customers install on PostgreSQL and others on MySQL. Even then, run the tests on every database you ship for: SQLAlchemy writes each one’s SQL, but each server keeps its own rules, as PostgreSQL’s refusal after an error showed. Reports that aggregate millions of rows and bulk loads are clearer and faster as SQL, through Core or the driver. Answer the three questions in the table for a project of your own:

Interactive · answer and compare Driver, Core or ORM for your code? Answer three questions about your data and your work. The table marks the layers that fit and says why the others do not.
Is your data mostly objects with relationships that code reads, changes and saves together?
Must the same code run on more than one kind of database, such as PostgreSQL for some customers and MySQL for others?
Is the work mainly reports that aggregate many rows, or bulk loads?

Answer the questions above: the table then marks what fits your case and says why the rest does not.

SQL through the driver, SQLAlchemy Core and the SQLAlchemy ORM against four needs
Layer Objects with relations More than one database Reports and bulk loads Seeing the exact SQL
The DB-API driver You write: SQL text and placeholders, such as sqlite3 or psycopg Poor you turn rows into objects and track changes yourself Poor placeholders and SQL differ by driver: ? for sqlite3, %s for psycopg Good you write the exact query or bulk statement Good you write every statement yourself
SQLAlchemy Core You write: SQL as text() or select() expressions, through an engine Mixed rows, not objects; relationships stay in your queries Good one interface; the engine writes placeholders for each driver Good SQL expressions or text(), run over pooled connections Good you build the statement; echo=True prints it
SQLAlchemy ORM You write: classes mapped to tables, saved through a session Good classes, relationships, and a session that saves changes together Good one set of models; SQLAlchemy writes each database's SQL Poor builds an object per row, and a forgotten eager load costs N+1 queries Mixed the ORM writes the SQL; read it with echo=True
How the answers decide
  • “Is your data mostly objects with relationships that code reads, changes and saves together?” Yes, many related tables: adds a caveat to The DB-API driver and SQLAlchemy Core. Without the ORM you turn rows into objects and keep track of what changed yourself; with many related tables that code grows fast.
  • “Is your data mostly objects with relationships that code reads, changes and saves together?” No, mostly queries over rows: adds a caveat to SQLAlchemy ORM. With few relationships the ORM is a layer you pay for in setup and in SQL you did not write, for little gain.
  • “Must the same code run on more than one kind of database, such as PostgreSQL for some customers and MySQL for others?” Yes: adds a caveat to The DB-API driver. Driver code is written for one paramstyle and one SQL dialect, so it changes for each database; Core and the ORM write both for each one.
  • “Is the work mainly reports that aggregate many rows, or bulk loads?” Yes: adds a caveat to SQLAlchemy ORM. An object per row costs time and memory over millions of rows; write the report or the bulk load as SQL, through Core or the driver.

Whichever you use, close what you open: use with Session(engine) as session: and with engine.connect() as conn:, so connections return to the pool even when an error occurs.

Checkpoint

Check: When an ORM helps

  1. One question A nightly job adds up 10 million order rows by month for a report. Which way of writing it does the lesson suggest?

    Choose one answer.

    Show the answer

    Answer: As SQL, through SQLAlchemy Core or the driver

    An ORM pays off for objects that code reads and changes; aggregating millions of rows is clearer and faster as a GROUP BY in SQL, written with Core or through the driver.

SQLite Online (SQL Playground) Try the lesson's queries on SQLite in your browser before you write the Python around them.

Practise: write SQL once for every placeholder style

The exercise asks for to_paramstyle(), which rewrites :name placeholders for each of the five styles, leaves quoted text and ::numeric casts alone, doubles a literal % for the two percent styles and never puts a value into the SQL. Its sample tests run in your browser:

Exercise · Medium · Python

Write SQL once for every DB-API placeholder style

Database drivers disagree about placeholders: PEP 249 allows five paramstyle values. Write to_paramstyle(sql, params, style) in paramstyle.py, so that a program can write its SQL once with :name placeholders and run it through any driver. params is a dictionary of values, and the function returns a pair (new_sql, new_params):

  • "qmark": each placeholder becomes ?, and new_params is a tuple with one value per placeholder, in order.
  • "numeric": placeholders become :1, :2 and so on, numbered by first appearance, so a name used twice keeps its number; new_params is a tuple with one value per distinct name.
  • "named": the SQL is unchanged, and new_params is a dict of the names used.
  • "format": each placeholder becomes %s, and new_params is a tuple, as for qmark.
  • "pyformat": each placeholder becomes %(name)s, and new_params is a dict of the names used.

Rules for every style:

  • A name is a letter or underscore followed by letters, digits or underscores. Text inside single-quoted SQL strings, such as '10:30', is not a placeholder, and neither is the PostgreSQL cast ::numeric.
  • For "format" and "pyformat", every literal % in the SQL becomes %%, because those drivers treat % as special.
  • Raise ValueError for an unknown style and for a placeholder whose name is not in params.
  • Never put a value into the SQL text.

Starter code · paramstyle.py 

import re

STYLES = ("qmark", "numeric", "named", "format", "pyformat")


def to_paramstyle(sql, params, style):
    """Rewrite :name placeholders for a DB-API paramstyle; return (new_sql, new_params)."""
    return sql, params
The sample tests · test_paramstyle.py 
import sqlite3

from paramstyle import to_paramstyle

SQL = "SELECT name FROM members WHERE city = :city AND joined >= :since AND city <> :other"
PARAMS = {"city": "Pune", "since": 2020, "other": "Mumbai", "unused": 1}


def members():
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE members (name TEXT, city TEXT, joined INTEGER)")
    con.executemany("INSERT INTO members VALUES (?, ?, ?)", [("Asha", "Pune", 2021), ("Ravi", "Pune", 2019), ("Meera", "Mumbai", 2022)])
    return con


def test_qmark_runs_on_sqlite3():
    """qmark: ? markers and a tuple in order, which sqlite3 runs"""
    sql, params = to_paramstyle(SQL, PARAMS, "qmark")
    assert sql == "SELECT name FROM members WHERE city = ? AND joined >= ? AND city <> ?"
    assert params == ("Pune", 2020, "Mumbai")
    assert members().execute(sql, params).fetchall() == [("Asha",)]


def test_named_runs_on_sqlite3():
    """named: the SQL is unchanged and only the names used are passed"""
    sql, params = to_paramstyle(SQL, PARAMS, "named")
    assert sql == SQL
    assert params == {"city": "Pune", "since": 2020, "other": "Mumbai"}
    assert members().execute(sql, params).fetchall() == [("Asha",)]


def test_format_and_percent():
    """format: %s markers, and a literal % is doubled"""
    sql, params = to_paramstyle("SELECT * FROM t WHERE name LIKE 'A%' AND id = :id", {"id": 7}, "format")
    assert sql == "SELECT * FROM t WHERE name LIKE 'A%%' AND id = %s"
    assert params == (7,)


def test_pyformat():
    """pyformat: %(name)s markers and a dict"""
    sql, params = to_paramstyle("UPDATE t SET pct = 10% WHERE id = :id AND v = :v", {"id": 1, "v": 2}, "pyformat")
    assert sql == "UPDATE t SET pct = 10%% WHERE id = %(id)s AND v = %(v)s"
    assert params == {"id": 1, "v": 2}


def test_numeric_reuses_numbers():
    """numeric: one number per distinct name, by first appearance"""
    sql, params = to_paramstyle("SELECT :b, :a, :b", {"a": "x", "b": "y"}, "numeric")
    assert sql == "SELECT :1, :2, :1"
    assert params == ("y", "x")


def test_quoted_text_and_casts_are_not_placeholders():
    """'10:30' and ::numeric stay as they are"""
    sql, params = to_paramstyle("SELECT price::numeric FROM slots WHERE at = '10:30' AND room = :room", {"room": "B2"}, "qmark")
    assert sql == "SELECT price::numeric FROM slots WHERE at = '10:30' AND room = ?"
    assert params == ("B2",)


def test_missing_name_and_unknown_style():
    """a placeholder without a value, or an unknown style, raises ValueError"""
    for args in [("SELECT :x", {}, "qmark"), ("SELECT :x", {"x": 1}, "dollar")]:
        try:
            to_paramstyle(*args)
        except ValueError:
            continue
        assert False, f"no ValueError for {args!r}"


def test_values_never_reach_the_sql():
    """no style puts a value into the SQL text"""
    for style in ("qmark", "numeric", "named", "format", "pyformat"):
        sql, _ = to_paramstyle("SELECT * FROM t WHERE note = :note", {"note": "MARKER-7731"}, style)
        assert "MARKER" not in sql, style
A hint

One regular expression can find both things that matter, a quoted string or a placeholder: '(?:[^']|'')*'|(?<!:):([A-Za-z_]\w*). With re.sub() and a function as the replacement, return a quoted string unchanged (its group 1 is None) and replace a placeholder by the style's marker, remembering the names in the order you meet them. dict.fromkeys(names) keeps the distinct names in that order.

The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Python Online Compiler Run dbapi_tour.py with your own table and see how description and rowcount change.

The practice below goes back over the tour, the five placeholder styles, the psycopg session, the engine and the ten authors. It starts from the part your checkpoint answers left weakest and gives the reason for every pick:

Practice that adapts to you

Practise this lesson

17 questions about 6 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.

  1. Question 1 of 17 What does dbapi_tour.py print?

    What does this program print? Choose one answer.

    import sqlite3

# Every DB-API 2.0 driver module describes itself with three attributes (PEP 249).
print("apilevel:", sqlite3.apilevel, "| threadsafety:", sqlite3.threadsafety, "| paramstyle:", sqlite3.paramstyle)

con = sqlite3.connect(":memory:")  # connect(): the driver's own arguments
cur = con.cursor()  # a cursor runs statements and holds their results
cur.execute("CREATE TABLE courses (code TEXT PRIMARY KEY, title TEXT, seats INTEGER)")
cur.executemany(
    "INSERT INTO courses VALUES (?, ?, ?)",
    [("CS101", "Programming in Python", 120), ("CS205", "Databases", 60), ("MA110", "Linear Algebra", 90)],
)
print("rows inserted:", cur.rowcount)
con.commit()

cur.execute("SELECT code, title, seats FROM courses WHERE seats >= ? ORDER BY seats DESC", (80,))
print("columns:", [d[0] for d in cur.description])  # one 7-item tuple per column; the name comes first
print("first row:", cur.fetchone())
print("the rest:", cur.fetchall())

# The standard exception classes, in one hierarchy that every driver shares.
try:
    cur.execute("INSERT INTO courses VALUES (?, ?, ?)", ("CS205", "Databases again", 30))
except sqlite3.DatabaseError as err:  # IntegrityError is a DatabaseError, which is an Error
    print(type(err).__name__, "->", " -> ".join(c.__name__ for c in type(err).__mro__[1:4]))
    con.rollback()
con.close()
    Show the answer

    Answer: it prints 

    apilevel: 2.0 | threadsafety: 1 | paramstyle: qmark
rows inserted: 3
columns: ['code', 'title', 'seats']
first row: ('CS101', 'Programming in Python', 120)
the rest: [('MA110', 'Linear Algebra', 90)]
IntegrityError -> DatabaseError -> Error -> Exception

    executemany() sets rowcount to the total of all three inserts. Only two courses have 80 seats or more, and fetchone() takes the first of them, so fetchall() returns the one left. sqlite3's paramstyle is qmark, and its IntegrityError sits under DatabaseError and Error, as PEP 249 lays out.

  2. Question 2 of 17 Which three module-level attributes does PEP 249 ask every driver to define?

    Choose one answer.

    Show the answer

    Answer: apilevel, threadsafety and paramstyle

    apilevel says which version of the specification the module follows, threadsafety what threads may share and paramstyle which placeholders it takes. Everything else starts at connect().

  3. Question 3 of 17 A data-access module calls con.execute() on every connection, as sqlite3 allows. Why may it break when the module is pointed at another driver?

    Choose one answer.

    Show the answer

    Answer: con.execute() is a sqlite3 shortcut, not part of the standard; PEP 249 runs statements on a cursor

    Code that sticks to the standard, connection.cursor() and cursor.execute(), moves between drivers more easily. Shortcuts such as sqlite3's con.execute() may not exist elsewhere.

  4. Question 4 of 17 Which placeholders does psycopg, the PostgreSQL driver, expect in SQL passed to execute()?

    Choose one answer.

    Show the answer

    Answer: %s, or %(name)s with a dictionary

    psycopg's paramstyle is pyformat, so it accepts %s with a sequence and %(name)s with a dictionary. The % here is only a marker: psycopg still sends the values separately, so never fill them in with Python's % operator yourself.

  5. Question 5 of 17 How many values must the parameters tuple hold for UPDATE courses SET seats = ?, fee = ? WHERE code = ??

    Type a number.

    Show the answer

    Answer: 3 values

    In the qmark style every ? takes the next value of the sequence, in order, so three question marks need a tuple of three values, such as (90, 4200, "MA110").

  6. Question 6 of 17 A query passed to psycopg with parameters filters on LIKE 'A%'. Why is that written LIKE 'A%%', although the % sits inside a quoted string?

    Choose one answer.

    Show the answer

    Answer: In a query with parameters, psycopg reads every % as the start of a marker, so a literal one must be doubled

    In the format and pyformat styles % is special everywhere in the query, so a percent sign you mean literally is written %%, and the driver sends a single % to the server.

  7. Question 7 of 17 When does psycopg begin a transaction on a connection that has none?

    Choose one answer.

    Show the answer

    Answer: With the first statement it runs, whether that is a SELECT or an INSERT

    psycopg starts a transaction with the first statement on a connection and keeps it open until commit() or rollback(), which is why the example's status read INTRANS before its commit().

  8. Question 8 of 17 What happens at the end of with psycopg.connect(...) as conn: that does not happen at the end of with sqlite3.connect(...) as con:?

    Choose one answer.

    Show the answer

    Answer: The connection is closed

    Both commit on success and roll back after an exception. psycopg's connection also closes at the end of the block, while sqlite3's stays open until you call close(). Read what each driver's context manager does.

  9. Question 9 of 17 A psycopg connection runs a SELECT and then waits a minute for the user before it does anything else. What does psycopg's documentation warn about?

    Choose one answer.

    Show the answer

    Answer: The session sits idle in transaction and holds locks, so commit or roll back promptly

    Because psycopg begins a transaction at the first statement, a connection that waits after a SELECT is idle in transaction. End the transaction, with commit() or rollback(), before any long wait.

  10. Question 10 of 17 What does a SQLAlchemy engine keep so that a web application does not open a new database connection on every request?

    Choose one answer.

    Show the answer

    Answer: A pool of open connections

    Opening a database connection costs time, so the engine keeps a pool and lends its connections out. Close what you take, with with engine.connect() as conn, so it goes back to the pool.

  11. Question 11 of 17 A page is slow, and you suspect the SQL that SQLAlchemy sends. What do you switch on first?

    Choose one answer.

    Show the answer

    Answer: create_engine(url, echo=True), which prints every statement and its parameters

    echo=True logs each statement with its parameters. Read the log before you change anything: a line repeated once per object is the N+1 problem, and a slow single statement is a query to look at.

  12. Question 12 of 17 The same text("SELECT code, seats FROM courses WHERE seats >= :min") now runs on an engine for PostgreSQL with psycopg. What happens to :min?

    Choose one answer.

    Show the answer

    Answer: SQLAlchemy rewrites it into the placeholder style that psycopg expects and passes 80 apart from the SQL

    text() is written once, with :name. With sqlite3 SQLAlchemy sent ?, as the listener recorded; with another driver it writes that driver's marker, and the value goes alongside as a parameter.

  13. Question 13 of 17 A page lists 12 courses and shows each course's teacher through an ORM relationship. The SQL log shows 13 queries. What is happening?

    Choose one answer.

    Show the answer

    Answer: The ORM loads the courses in one query, then each course's teacher lazily, one query per course

    Relationships load lazily by default: reading the relationship the first time sends one more SELECT. A loop over N objects therefore costs N + 1 queries. Load the related objects up front, with selectinload() or joinedload(), or write a join.

  14. Question 14 of 17 How many SQL statements did n_plus_one.py send to count the 30 books with joinedload(Author.books)?

    Type a number.

    Show the answer

    Answer: 1 statement

    joinedload() brought the authors and their books together in one SELECT with a LEFT OUTER JOIN, against 2 statements with selectinload() and 11 with the default lazy loading.

  15. Question 15 of 17 A team has fixed an N+1 page with selectinload(). How can they make the next forgotten eager load fail loudly in development instead of quietly slowing a page down?

    Choose one answer.

    Show the answer

    Answer: Mark the relationships lazy="raise", or add raiseload("*") to the query

    lazy="raise" on a relationship, or raiseload("*") on a query, turns a lazy load that nobody planned into an error, so a forgotten eager load is found by the first test that touches it.

  16. Question 16 of 17 What is the ORM's session?

    Choose one answer.

    Show the answer

    Answer: Its unit of work, which loads objects and saves their changes together

    A session tracks the objects it loaded and what changed in them, and saves those changes together. Open it with with Session(engine) as session:, so its connection returns to the pool even after an error.

  17. Question 17 of 17 In which of these does an ORM such as SQLAlchemy's usually help?

    Choose every answer that is right.

    Show the answer

    Answer:

    • An application that some customers install on PostgreSQL and others on MySQL
    • An application with many related tables, where code works with objects and saves changes in units of work

    An ORM pays off when objects and their relationships are the natural model and the same code must work on several databases. Heavy aggregation and bulk loading are better written as SQL, through SQLAlchemy Core or the driver, where you control exactly what runs.

Interview questions

What is PEP 249 (DB-API 2.0), and why do Python database drivers feel alike?

PEP 249 is the specification that Python database drivers implement. It defines a module-level connect() and three attributes, apilevel, threadsafety and paramstyle; connection objects with cursor(), commit(), rollback() and close(); cursor objects with execute(), executemany(), fetchone(), fetchmany(), fetchall(), description and rowcount; and a standard exception hierarchy under Error, with DatabaseError, IntegrityError, OperationalError and ProgrammingError among others. It also says that auto-commit, if a database has it, must be off when a connection opens. Because sqlite3, psycopg, mysqlclient and the others all follow it, code and habits carry over from one database to another. What it leaves open are the connection arguments, the placeholder style (five are allowed) and details such as when a transaction starts, so moving between drivers still needs care.

Key takeaways

  • PEP 249 gives every driver the same shape: connect(), cursors, execute(), the fetch methods, commit(), rollback() and one exception hierarchy under Error.
  • Check paramstyle: sqlite3 uses ? and :name, psycopg %s and %(name)s, and in a psycopg query with parameters a literal % becomes %%; never fill placeholders in yourself.
  • psycopg on PostgreSQL opens a transaction at the first execute(), returns numeric as Decimal, refuses statements after an error until rollback(), and closes the connection at the end of with.
  • SQLAlchemy Core gives one interface over many drivers, with :name placeholders in text() that it rewrites for the driver, generated SQL and a connection pool; echo=True shows what it sends.
  • ORM relationships load lazily, which causes N+1 queries in loops: use selectinload() for collections, joinedload() for many-to-one, and lazy="raise" to catch forgotten loads.
  • Use an ORM for object-heavy applications and SQL for reports and bulk work, and always close sessions and connections.

Terms in this lesson

connection pool
A set of open database connections that a program keeps and lends out, as a SQLAlchemy engine does, so that each request does not pay for opening a new connection.
DB-API 2.0
The interface of PEP 249 that Python database drivers implement: connect(), connections with commit() and rollback(), cursors with execute() and the fetch methods, and one family of exception classes under Error.
lazy loading
Loading related data only when code first uses it, such as an author's books on the first read of author.books. It saves work when the data is not needed, and costs one query per object in a loop.
N+1 query problem
One query for a list of N rows, then one more query for each row's related data: N + 1 round trips where one or two would do. Lazy loading inside a loop causes it, and eager loading fixes it.
object-relational mapper (ORM)
A library that maps classes to tables and objects to rows, such as SQLAlchemy's ORM, so that code reads and changes objects while the library writes the SQL.

References

Related tools

SQL Formatter Readable, consistently indented SQL for 20 dialects — or a safe one-line copy. SQLite Online (SQL Playground) A full SQLite database in your browser — open, query, import, and export with a pass. Python Online Compiler Real CPython in your browser — with stdin, files and a stop button.

Report a problem with this lesson