Gradient AtlasML Materials

Data science roadmap › Python for Data Science › Chapter 18

Database Connectivity (PDBC)

Talking to relational databases from Python.

18.1 PDBC Concepts and the DB-API

A .txt/.csv file (16) is fine for small, single-program data — but a real relational database (an RDBMS: SQLite, PostgreSQL, MySQL, ...) adds what a plain file can't: many programs reading/writing safely at once, complex queries across related tables, transactions (18.5), and data too large to hold in memory at all. Database connectivity is simply a Python program talking to one of these over a driver library.

Rather than every database vendor inventing its own unrelated Python API, PEP 249 defines the DB-API — a single common shape every driver module implements: connect(), a Cursor with .execute()/.fetchone()/.fetchall(), and so on. Learn the DB-API once, and switching the underlying database is mostly a matter of swapping which driver's connect() gets called (18.6) — not rewriting the querying code.

sqlite3 ships in the standard library (9.6), needs no server or install, and follows the DB-API exactly — making it the natural module to learn the DB-API itself with:

In [1]12 lines
import sqlite3

conn = sqlite3.connect(":memory:")   # an in-memory database - gone once the connection closes
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
cur.execute("INSERT INTO users (name, age) VALUES ('Ada', 28)")
conn.commit()

cur.execute("SELECT name, age FROM users")
print(cur.fetchall())   # [('Ada', 28)]

conn.close()

[('Ada', 28)]

In practice — this is the layer under every Python web backend. Django's ORM, Flask paired with SQLAlchemy, and FastAPI talking to Postgres all sit on top of exactly this same DB-API shape — a Connection, a Cursor, .execute(), .fetchall(). Learning the raw DB-API first (rather than jumping straight to an ORM, 18.7) is what makes it possible to read what an ORM is actually doing underneath, and to drop down to raw SQL yourself when a query is too complex or too performance-sensitive for the ORM to express well.

18.2 Connections and Cursors

Two objects do all the work in the demo above:

Object Represents
Connection (sqlite3.connect(...)) the actual link to the database — owns .commit()/.rollback() (18.5) and .close()
Cursor (conn.cursor()) a "current position" through query results — the thing .execute() and the fetch...() methods (18.3) live on

One Connection can hand out several independent Cursors, each stepping through its own result set. Both should be closed when done — .close() on the cursor first, then the connection — though letting a short-lived script simply end also releases them.

In [2]16 lines
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
conn.executemany("INSERT INTO users (name) VALUES (?)", [("Ada",), ("Grace",), ("Alan",)])
conn.commit()

cur_a = conn.cursor()   # two independent cursors, same connection
cur_b = conn.cursor()

cur_a.execute("SELECT name FROM users ORDER BY id")
cur_b.execute("SELECT name FROM users ORDER BY id")

print(cur_a.fetchone())   # ('Ada',) -> cur_a's own position: row 1
print(cur_a.fetchone())   # ('Grace',) -> cur_a's own position: row 2
print(cur_b.fetchone())   # ('Ada',) -> cur_b started fresh, unaffected by cur_a

conn.close()

('Ada',)
('Grace',)
('Ada',)

18.3 Executing Queries and Fetching Results

.execute(sql) runs one statement; .executemany(sql, rows) runs it once per row — far faster than a Python loop calling .execute() repeatedly, since the driver batches the work. Fetching back a SELECT's results has three levels of granularity:

Call Gets
.fetchone() the next single row, or None once exhausted
.fetchmany(n) the next n rows, as a list
.fetchall() every remaining row, as a list
In [3]15 lines
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

cur.executemany(   # one call, many rows
    "INSERT INTO users (name, age) VALUES (?, ?)",
    [("Ada", 28), ("Grace", 34), ("Alan", 41)],
)
conn.commit()
print(cur.rowcount)   # 3 -> rows affected by the LAST statement

cur.execute("SELECT name, age FROM users ORDER BY age")
print(cur.fetchone())      # ('Ada', 28) -> just the next row
print(cur.fetchmany(1))   # [('Grace', 34)] -> the next 1 row(s), as a list
print(cur.fetchall())       # [('Alan', 41)] -> everything still remaining

3
('Ada', 28)
[('Grace', 34)]
[('Alan', 41)]

A Cursor is also, itself, a lazy iterator (14.1, 14.2) — looping over it directly pulls one row at a time, same idea as iterating a file object (16.5), without a separate fetchall() call. .description exposes column metadata from the last query:

In [4]10 lines
print([col[0] for col in cur.description])   # ['name', 'age'] -> column names, from the last query

cur.execute("SELECT name, age FROM users ORDER BY age")
for row in cur:   # the cursor is its own iterator - no fetchall() needed
    print(row)
# ('Ada', 28)
# ('Grace', 34)
# ('Alan', 41)

conn.close()

['name', 'age']
('Ada', 28)
('Grace', 34)
('Alan', 41)

A two-table JOIN — the "complex queries across related tables" 18.1 mentioned as a reason to reach for a database in the first place. A second table with a foreign key (orders.user_id, referencing users.id) lets a single query pull matching rows from both tables at once, something a plain file has no equivalent for:

In [5]19 lines
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, item TEXT)")   # user_id -> users.id
conn.executemany("INSERT INTO users (name) VALUES (?)", [("Ada",), ("Grace",)])
conn.executemany(
    "INSERT INTO orders (user_id, item) VALUES (?, ?)",
    [(1, "Laptop"), (1, "Mouse"), (2, "Keyboard")],
)
conn.commit()

cur = conn.execute("""
    SELECT users.name, orders.item
    FROM users
    JOIN orders ON users.id = orders.user_id
    ORDER BY orders.id
""")
print(cur.fetchall())   # [('Ada', 'Laptop'), ('Ada', 'Mouse'), ('Grace', 'Keyboard')]

conn.close()

[('Ada', 'Laptop'), ('Ada', 'Mouse'), ('Grace', 'Keyboard')]

In practice — this is what an analytics dashboard query looks like. "Show total orders per customer this month" or "which products were bought together" are exactly this shape: a JOIN (often several) pulling matching rows across orders, customers, and products tables. Reporting and analytics features in a real backend are almost always JOINs like this one, not separate queries stitched together in Python.

18.4 Parameterized Queries

Common mistake — building SQL with an f-string. It looks harmless right up until a value contains SQL syntax of its own — at which point the query's actual meaning changes, a classic SQL injection:

In [6]12 lines
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
conn.executemany("INSERT INTO users (name, age) VALUES (?, ?)", [("Ada", 28), ("Grace", 34)])
conn.commit()

name = "Ada"
print(conn.execute(f"SELECT age FROM users WHERE name = '{name}'").fetchall())   # [(28,)] - looks fine...

malicious = "x' OR '1'='1"   # ...but a hostile value breaks the query's actual meaning
print(f"SELECT age FROM users WHERE name = '{malicious}'")   # the ACTUAL SQL sent - "OR '1'='1'" is always true
print(conn.execute(f"SELECT age FROM users WHERE name = '{malicious}'").fetchall())
# [(28,), (34,)] -> leaked EVERY row, not just a matching one

[(28,)]
SELECT age FROM users WHERE name = 'x' OR '1'='1'
[(28,), (34,)]

The fix — a parameterized query — passes values through a placeholder (? in sqlite3; other drivers use %s, 18.6) instead of splicing them into the SQL text. The driver sends the value separately from the statement, so it's never interpreted as SQL syntax no matter what it contains:

In [7]4 lines
print(conn.execute("SELECT age FROM users WHERE name = ?", (malicious,)).fetchall())
# [] -> correctly finds nothing - the malicious string is just DATA here, never SQL

conn.close()

[]

In practice — this is on the OWASP Top 10. SQL injection has been one of the most common and most damaging real-world web vulnerabilities for over two decades — it's how attackers have dumped entire user/password databases from production systems. "Never build SQL with string formatting/concatenation, always use parameterized queries" is one of the very few absolute rules enforced in essentially every serious Python code review and security audit.

18.5 Transactions and Error Handling

A transaction groups several statements into one all-or-nothing unit — .commit() makes every change in it permanent; .rollback() undoes all of them, as if none had run. Wrapping risky statements in try/except (7.2) and rolling back on failure keeps the database from being left half-updated:

In [8]12 lines
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (name TEXT, balance INTEGER)")
conn.execute("INSERT INTO accounts VALUES ('Ada', 100)")
conn.commit()

try:
    conn.execute("UPDATE accounts SET balance = balance - 50 WHERE name = 'Ada'")
    raise RuntimeError("something went wrong before commit")
except RuntimeError:
    conn.rollback()   # undo the UPDATE - as far as the database is concerned, it never happened

print(conn.execute("SELECT balance FROM accounts WHERE name = 'Ada'").fetchone())   # (100,) -> unchanged

(100,)

Common mistake — expecting with conn: to close the connection. Unlike with open(...) (16.6), sqlite3's Connection context manager only wraps .commit()/.rollback() around the block — committing on a clean exit, rolling back on an exception — and leaves the connection itself open afterward. This is a deliberate design choice, not an oversight: closing a connection is a separate, heavier operation (tearing down the actual link to the database) from the commit/rollback lifecycle a single transaction represents, so sqlite3 keeps the two decoupled — one connection can run many transactions, one after another, without being reopened each time:

In [9]14 lines
with conn:   # commits automatically on a clean exit - no explicit .commit() needed
    conn.execute("UPDATE accounts SET balance = balance - 50 WHERE name = 'Ada'")
print(conn.execute("SELECT balance FROM accounts WHERE name = 'Ada'").fetchone())   # (50,)

try:
    with conn:   # rolls back automatically if the block raises
        conn.execute("UPDATE accounts SET balance = balance - 999999 WHERE name = 'Ada'")
        raise RuntimeError("oops")
except RuntimeError:
    pass
print(conn.execute("SELECT balance FROM accounts WHERE name = 'Ada'").fetchone())   # (50,) -> rolled back

conn.execute("SELECT 1")   # still works - "with conn:" never closed the connection
conn.close()

(50,)
(50,)

In practice — an e-commerce checkout. Placing an order typically has to debit inventory, record a payment, and create the order row together, as one unit: if the payment step fails, the inventory debit must roll back too, or the store ends up selling a product it no longer has. This is exactly the all-or-nothing guarantee commit()/rollback() provide, and it's why checkout code almost always runs inside one transaction rather than several independent statements.

18.6 Working With Specific Drivers

Everything in 18.1–18.5 was demonstrated against sqlite3, but the DB-API shape it follows is exactly what every other driver follows too:

Database Common driver Built-in? Placeholder style
SQLite sqlite3 yes (stdlib) ? (qmark)
PostgreSQL psycopg2 / psycopg no — pip install (9.7) %s (pyformat)
MySQL / MariaDB mysql-connector-python, PyMySQL no — pip install (9.7) %s (pyformat)

Switching databases mostly means changing which driver's connect() is called (with that database's own connection details — host, user, password) and the placeholder style in parameterized queries (18.4) — .execute(), .fetchall(), .commit(), and the rest of the DB-API stay the same. A PostgreSQL connection, for reference (not run here — it needs a real server and the psycopg2 package):

import psycopg2
conn = psycopg2.connect(host="localhost", dbname="mydb", user="ada", password="...")
cur = conn.cursor()
cur.execute("SELECT age FROM users WHERE name = %s", (name,))   # %s, not ?
print(cur.fetchall())
conn.close()

Common mistake — hardcoding credentials in source. The password="..." above is for illustration only — a real connection string like that, checked into source control, leaks the database password to anyone with repository access (and to its entire commit history, even after being "removed" later). Load credentials from an environment variable (os.environ["DB_PASSWORD"], 9.5) or a secrets manager instead — never as a literal in code.

In [10]3 lines
print(sqlite3.apilevel)        # 2.0 -> every DB-API-compliant driver declares this
print(sqlite3.paramstyle)   # qmark -> confirms the "?" placeholder style used all through 18.4
print(sqlite3.threadsafety)   # 3 -> a required module-level constant every DB-API driver exposes

2.0
qmark
3

18.7 Object-Relational Mapping (ORM)

Every demo so far wrote raw SQL as a string and got a tuple of columns back — a noticeable mismatch with a language where data is normally an object with named attributes. An ORM (Object-Relational Mapper) bridges that gap: it maps a database table to a Python class, and each row to an instance of it, so the database is queried and updated through ordinary Python (attribute access, method calls) instead of hand-written SQL strings.

Commonly used Python ORMs:

ORM Notes
SQLAlchemy the most widely used; a full toolkit — usable as just a DB-API wrapper (its "Core"), or with its ORM layer on top; works standalone or inside a web framework
Django ORM built into the Django web framework; tightly integrated with Django's models, migrations, and admin site — not typically used outside Django
Peewee small and deliberately simple; a lighter-weight alternative when SQLAlchemy's full feature set is more than a project needs

SQLAlchemy isn't installed in this environment, so the example below (pip install sqlalchemy, 9.7, to actually run it) is shown as a plain code cell for reference rather than executed here:

In [11]22 lines
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class User(Base):          # a Python class - maps to a "users" table
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)   # generates the CREATE TABLE for us

Session = sessionmaker(bind=engine)
session = Session()

session.add(User(name="Ada", age=28))   # no SQL written at all
session.commit()

ada = session.query(User).filter_by(name="Ada").first()
print(ada.name, ada.age)   # Ada 28 -> a Python object, not a raw tuple

Ada 28

Compare that to 18.1's raw sqlite3 version of the same task — no CREATE TABLE string, no INSERT INTO ... VALUES (?, ?), and the result comes back as ada.name instead of row[0]. The trade-off: an ORM adds a real layer of abstraction between the code and the SQL actually being sent, which can make performance-critical or highly complex queries harder to reason about — raw DB-API SQL (18.1–18.5) is still the more direct tool for those.