Python Database Connections and Queries

The connection owns the session: the network link or open file, the login, and the current transaction. It provides cursor(), commit(), rollback() and close().

A cursor executes statements with execute() and executemany(), and holds the results of the last query for fetching. You can have several cursors on one connection, and they share its transaction.

conn = sqlite3.connect("swim.db")
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM attendance")
print(cur.fetchone()[0])
cur.close()
conn.close()

sqlite3 adds shortcuts, such as conn.execute(), which creates a cursor for you; that's what most examples in this course use. Other drivers, such as psycopg for PostgreSQL and MySQL Connector, expect you to create cursors explicitly, and some, such as psycopg, let you use them in with blocks. Think of the cursor as a pointer into one result set, and the connection as the session it belongs to.

After a SELECT, read the rows with one of three methods:

fetchall() on a table with ten million rows tries to hold them all in memory at once. Process large results in batches instead:

cur.execute("SELECT session, swimmers FROM attendance ORDER BY session")
while True:
    batch = cur.fetchmany(500)
    if not batch:
        break
    for session, swimmers in batch:
        process(session, swimmers)

Iterating directly over the cursor (for row in cur:) also fetches progressively in most drivers. Better still, let the database do the work: SELECT SUM(swimmers) returns one number instead of every row for Python to add up.

Two different responsibilities are easy to confuse:

• Transaction handling decides whether changes are kept (commit()) or discarded (rollback()).
• Resource closure ends the session and releases the file or network connection (close()).

In sqlite3, with conn: handles the transaction only: it commits on success and rolls back on an exception, but leaves the connection open. To close it as well, wrap it with contextlib.closing():

from contextlib import closing
with closing(sqlite3.connect("swim.db")) as conn:   # closes at the end
    with conn:                                       # commits or rolls back
        conn.execute("INSERT INTO attendance VALUES (?, ?)", ("Friday", 12))

Closing without committing discards pending changes in sqlite3. Leaving connections open wastes resources, may keep a SQLite file locked so other programs can't write to it, and on servers can exhaust the database's connection limit. Long-running applications use connection pools that hand out and reclaim connections, usually provided by SQLAlchemy or the driver.

Different failures need different responses:

try:
    with conn:
        conn.execute("INSERT INTO swimmers (email) VALUES (?)", (email,))
except sqlite3.IntegrityError:
    print(f"{email} is already registered")

Retry carefully. Re-running a read after a dropped connection is safe. Re-running a write whose response was lost may apply it twice, for example registering a swimmer or charging a fee twice, unless the operation is idempotent (repeating it has no extra effect), for instance by using a unique key. Log the failing operation's context, such as the session name, but never the full connection string with its password.

Task: read attendance in batches and guarantee both transaction handling and connection closure.

import sqlite3
from contextlib import closing

def weekly_total(path):
    with closing(sqlite3.connect(path)) as conn:
        with conn:
            conn.execute("CREATE TABLE IF NOT EXISTS attendance (session TEXT, swimmers INTEGER)")
            conn.executemany("INSERT INTO attendance VALUES (?, ?)", [("Mon", 9), ("Wed", 14), ("Fri", 11)])
        cur = conn.execute("SELECT swimmers FROM attendance")
        total = 0
        while rows := cur.fetchmany(2):
            total += sum(swimmers for (swimmers,) in rows)
        return total

print(weekly_total(":memory:"))   # 34

The inner with commits the inserts; closing() closes the connection even if an error occurs. For the worked check: does with sqlite3.connect(...) by itself close the connection? No. It only manages the transaction.

PEP 249 defines connections, cursors and the standard exceptions, and the sqlite3 documentation explains its context manager. Continue with HTTP requests and REST APIs. Practise with the Python exercises and Python quiz.