PYTHON / DATABASES WITH PYTHON
Relational concepts and SQL from Python
Model a problem as related tables with keys and constraints, then let SQL do the joining and aggregating instead of Python loops.
What you will learn
- Split entities into tables with a primary key that identifies each row
- Link a one-to-many relationship with a foreign key on the many side
- Represent many-to-many with a junction table and a composite primary key
- Push filtering, joining and aggregation into one SQL statement
Understanding Relational concepts and SQL from Python
A relational table is not a list of Python objects; it is a set of rows that all share one fixed, typed shape, and nothing in a row points at another row the way an object reference does. Instead, rows refer to each other by value: a book row stores the integer 1 in author_id, and the database finds the author whose primary key equals 1. That is why identity matters so much in this model. A primary key is the promise that one row means one real thing, and a foreign key is the promise that a referenced thing exists.
SQL is declarative and set-at-a-time, which is the biggest mental shift coming from Python. You do not write a loop that walks books and looks up an author per book; you describe the relationship (JOIN book ON book.author_id = author.id), the grouping, and the ordering, and the engine picks how to execute it, using indexes you never mention. The same job written as a Python loop over two fetched tables produces the same answer, but it drags every row across the connection and throws away every optimisation the engine could have made.
The reason relational design insists on one fact in one place is that duplicated data can disagree with itself. If an author's name is copied into every book row, a rename means updating many rows, and one missed row silently becomes a second author. Moving the name into an author table and referring to it by key makes the name unrepresentable in two versions, and declaring NOT NULL, UNIQUE and REFERENCES makes the engine reject the bad state instead of trusting your Python code to remember the rule.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE author (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
year INTEGER,
author_id INTEGER NOT NULL REFERENCES author(id)
);
INSERT INTO author (id, name) VALUES (1, 'Le Guin'), (2, 'Borges'), (3, 'Lem');
INSERT INTO book (title, year, author_id) VALUES
('A Wizard of Earthsea', 1968, 1),
('The Dispossessed', 1974, 1),
('Ficciones', 1944, 2),
('Solaris', 1961, 3);
""")
rows = con.execute("""
SELECT a.name, COUNT(b.id) AS n, MIN(b.year) AS first_year
FROM author AS a
JOIN book AS b ON b.author_id = a.id
GROUP BY a.id
ORDER BY n DESC, a.name
""").fetchall()
for name, n, first_year in rows:
print(f"{name:8} {n} book(s), earliest {first_year}")
try:
con.execute("INSERT INTO book (title, year, author_id) VALUES ('Ghost', 2000, 99)")
except sqlite3.IntegrityError as exc:
print("rejected:", exc)
con.close()Relationships are stored as key values across tables, and SQL lets you describe the result you want over whole sets of those rows rather than walking them one at a time.
Worked examples
Many-to-many through a junction table
Shows how a student/course relationship needs a third table, and how a LEFT JOIN reports a student with no rows on the other side.
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE course (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolment (
student_id INTEGER NOT NULL REFERENCES student(id),
course_id INTEGER NOT NULL REFERENCES course(id),
grade INTEGER,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO student VALUES (1, 'Ada'), (2, 'Kai'), (3, 'Mira');
INSERT INTO course VALUES (10, 'Databases'), (20, 'Compilers');
INSERT INTO enrolment VALUES (1, 10, 88), (1, 20, 91), (2, 10, 75);
""")
sql = """
SELECT s.name, c.title, e.grade
FROM student AS s
LEFT JOIN enrolment AS e ON e.student_id = s.id
LEFT JOIN course AS c ON c.id = e.course_id
ORDER BY s.name, c.title
"""
for name, title, grade in con.execute(sql):
print(name, "|", title, "|", grade)
count = con.execute(
"SELECT COUNT(*) FROM enrolment WHERE student_id = 1"
).fetchone()[0]
print("Ada's enrolments:", count)
con.close()Example explained
Line 1enrolment holds two foreign keys, so one student can appear in many courses and one course in many students.
Line 2PRIMARY KEY (student_id, course_id) makes the pair unique, so the same student cannot be enrolled twice in one course.
Line 3grade sits on enrolment because it is a fact about the pair, not about the student or the course alone.
Line 4Mira has no enrolment row, so the LEFT JOIN fills the other columns with SQL NULL, which Python receives as None.
One statement instead of a Python loop
Compares an N+1 query loop with a single grouped query that returns the same answer.
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customer (id INTEGER PRIMARY KEY, city TEXT NOT NULL);
CREATE TABLE "order" (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customer(id),
total REAL NOT NULL
);
INSERT INTO customer VALUES (1, 'Oslo'), (2, 'Oslo'), (3, 'Lima');
INSERT INTO "order" VALUES (1, 1, 20.0), (2, 1, 5.5), (3, 2, 12.0), (4, 3, 40.0);
""")
# Row-at-a-time: one query per customer
queries = 1
totals = {}
for cid, city in con.execute("SELECT id, city FROM customer"):
queries += 1
s = con.execute('SELECT COALESCE(SUM(total), 0) FROM "order" WHERE customer_id = ?',
(cid,)).fetchone()[0]
totals[city] = totals.get(city, 0) + s
print("loop:", sorted(totals.items()), "queries:", queries)
# Set-at-a-time: one query for the whole answer
rows = con.execute('''
SELECT c.city, SUM(o.total) AS revenue
FROM customer AS c
JOIN "order" AS o ON o.customer_id = c.id
GROUP BY c.city
ORDER BY c.city
''').fetchall()
print("sql: ", rows, "queries: 1")
con.close()Example explained
Line 1The loop issues one query per customer, so query count grows with the number of rows in customer.
Line 2GROUP BY c.city collapses the joined rows by city inside the engine, so no per-row Python work is needed.
Line 3SUM(o.total) is computed where the data lives; only two result rows cross the connection.
Line 4order is quoted because it is an SQL keyword; unquoted it would be a syntax error.
Important notes
SQL NULL means 'no value', so col = NULL is never true; test with IS NULL, and expect Python to hand you None for those columns.
A query's row order is undefined unless you write ORDER BY, even when small tables happen to come back in insertion order.
Common mistakes
Storing a repeated label such as an author or category name directly in every row: renaming it then requires updating many rows, and one missed row becomes a second, silently different entity.
Packing several related values into one column as 'sci-fi,classic': the engine can no longer join, count or index them, so every query degrades into a substring match that misses partial names.
Assuming SQLite checks a REFERENCES clause by default: without PRAGMA foreign_keys = ON the declaration is only documentation, and rows referring to non-existent parents are accepted.
Try it yourself
Change, predict, then run
Build an in-memory database with artist and album tables, where album has an artist_id foreign key, insert three artists and five albums spread unevenly across them, and print each artist with their album count using a single grouped query ordered by count descending.
Open the Python workspaceCheck your understanding
A books table keeps its tags in one TEXT column as 'sci-fi,classic'. Why does this design fight the relational model?
- The tag set is no longer an addressable value the engine can index, join or count, so every tag question becomes a substring scan
- TEXT columns are too short to hold more than one tag reliably
- Python's sqlite3 returns TEXT columns as bytes, so splitting them fails
- Commas are reserved inside SQL string literals and must be escaped
Show answer
Relational operations work on single values per column; a packed list hides the individual tags from indexes, joins and GROUP BY, so 'sci-fi' can only be found by LIKE scanning, which also matches 'hard-sci-fiction'. The escaping option is tempting but wrong: a comma is an ordinary character inside a string literal, so the string stores fine, and that is exactly why the flaw goes unnoticed.