Tables, Rows, and a First Query: Python Foundations

Outcome

By the end of this lesson you can create a table whose constraints refuse bad data, insert records into it, and ask SQL for exactly the rows you want rather than fetching everything and filtering afterwards.

Why it matters

Everything so far has lived in memory and disappeared when the program ended. A review assistant that re-fetches and re-parses the whole batch to answer "what did we shortlist last week" is doing a great deal of work to answer a question a database answers instantly.

A database enforces rules at the storage boundary, including for writers that bypass this Python process. A NOT NULL column refuses a record with a missing field at insertion, and a primary key refuses a duplicate identifier. Python can check the same conditions earlier for a clearer error, but database constraints keep them true across every writer.

SQLite is in the Python standard library, needs no server, and stores a whole database in one file. It runs in this page, and it is the right choice for the capstone.

Concept

A table has named, typed columns; a row is one record. A primary key uniquely identifies a row, and the database refuses a second row with the same value.

CREATE TABLE name (column TYPE constraints, ...) defines the shape. The constraints worth using from the start:

PRIMARY KEY on the natural identifier, here the notice id.
NOT NULL on any column your program cannot work without.
DEFAULT value for a column that has a sensible fallback.

SQLite's types are a light touch compared to other databases: TEXT, INTEGER, REAL, and BLOB, and it will accept a value of the "wrong" type in most columns. Declare the type you intend anyway. It documents the schema, other databases enforce it, and D1, the database this learning platform itself runs on, is SQLite-compatible, so the same statements carry over.

INSERT INTO table VALUES (?, ?, ?) adds a row. Those question marks are parameter placeholders, and the values are passed separately as a tuple. Use them from your very first insert. Building SQL by joining strings is the single most damaging habit in database code, and lesson three is entirely about why.

SELECT columns FROM table WHERE condition ORDER BY column DESC LIMIT n is the query. Read it in the order the database applies it: FROM chooses the table, WHERE removes rows, ORDER BY sorts what remains, LIMIT takes the top few, and SELECT decides which columns come back. That is Module 8's select-filter-sort-limit sequence, written as one statement.

Do the filtering in SQL, not in Python afterwards. Over five rows it makes no difference. Over half a million, the database can use an index to avoid reading rows it will discard, while a Python filter has to receive every row first, across the process boundary, before discarding most of them.

In Python: sqlite3.connect(path) opens a database, ":memory:" creates a temporary one that never touches disk, connection.execute(sql, params) runs a statement, .fetchall() returns the rows as a list of tuples, and connection.commit() makes changes permanent.

Read the code

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute(
    "CREATE TABLE notices ("
    " notice_id TEXT PRIMARY KEY,"
    " agency TEXT NOT NULL,"
    " amount INTEGER,"
    " status TEXT NOT NULL DEFAULT 'Active')"
)
connection.executemany(
    "INSERT INTO notices (notice_id, agency, amount) VALUES (?, ?, ?)",
    [
        ("A-1", "GSA", 310000),
        ("A-2", "DLA", 720000),
        ("A-3", "GSA", None),
    ],
)

rows = connection.execute(
    "SELECT notice_id, amount FROM notices WHERE amount IS NOT NULL ORDER BY amount DESC"
).fetchall()

print(rows)
print(connection.execute("SELECT COUNT(*) FROM notices").fetchone()[0])
print(connection.execute("SELECT status FROM notices WHERE notice_id = 'A-1'").fetchone()[0])

executemany runs one statement repeatedly with different parameters, which is how a batch is loaded.

The third record has no amount, and that is allowed: amount has no NOT NULL, because "not published" is a real state this project must represent. agency does have it, because a notice with no agency is not usable at all. Those two decisions are the data dictionary from Module 6 turned into constraints the database enforces.

The query says WHERE amount IS NOT NULL, not WHERE amount != NULL. In SQL, NULL means unknown, and any comparison with an unknown value is itself unknown rather than true or false, so amount != NULL matches nothing at all, silently. IS NULL and IS NOT NULL are the only tests that work.

status was never inserted, so the DEFAULT supplied it.

Predict the output

Predict the three printed lines.

Check your prediction

[('A-2', 720000), ('A-1', 310000)]
3
Active

The query returns two rows because A-3 has no amount, but the table still holds three: COUNT(*) counts rows, not non-null amounts. That difference between "rows in the table" and "rows my query returned" is Module 8's denominator point, arriving in a new place.

The third line is Active, supplied by the DEFAULT clause without any insert mentioning it.

Modify the code

Change the query's condition to WHERE amount != NULL and predict the first printed line.

What changes, and why

[]

An empty list, no error, no warning.

NULL means unknown, so amount != NULL asks "is this value different from a value we do not know", and the answer is itself unknown. WHERE keeps only rows where the condition is definitely true, so every row is dropped, including the two with perfectly good amounts.

This is the most common SQL mistake there is, and it always fails this way: silently, returning nothing, looking like a table with no matching data. Whenever a query returns zero rows and you are sure it should not, check for a comparison against NULL first.

Debug the bug

An assistant was asked for the largest opportunities over 100,000. It produced this and said the database does the filtering.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE notices (notice_id TEXT, amount INTEGER)")
connection.executemany(
    "INSERT INTO notices VALUES (?, ?)",
    [("A-1", 310000), ("A-2", 90000), ("A-3", 720000)],
)

rows = connection.execute("SELECT * FROM notices").fetchall()
large = [row for row in rows if row[1] > 100000]
large.sort(key=lambda row: row[1], reverse=True)

print(large)

What's actually wrong

It prints the right answer, [('A-3', 720000), ('A-1', 310000)], and the claim in the last sentence is false: the database did no filtering at all.

SELECT * fetched every row, and Python then filtered and sorted them. Over three rows that is invisible. Over half a million it means transferring half a million rows to discard most of them, and the database cannot use an index because it was never told what you wanted.

SELECT * has a second cost. It returns whatever columns the table happens to have, so row[1] means "amount" only until someone adds a column in the middle, at which point every positional access silently reads the wrong field. Naming the columns you want makes the code say what it depends on.

There is also no primary key on this table, so INSERT will happily create a second A-1 and the duplicate will survive until something downstream produces a doubled total.

The version that says what it means:

rows = connection.execute(
    "SELECT notice_id, amount FROM notices WHERE amount > ? ORDER BY amount DESC",
    (100000,),
).fetchall()

One statement, filtering and ordering in the database, named columns, and the threshold passed as a parameter rather than pasted into the string. Note the trailing comma in (100000,): without it Python has a plain number in parentheses rather than a one-item tuple, and sqlite3 raises.

Try it yourself

The table is created and seeded. Write one SELECT that returns the Oregon opportunities worth at least 100,000, largest first, and print them.

Loading this exercise…

Practical challenge (optional)

Optional: try inserting a second row with a notice_id that already exists, and read the error carefully. Then try inserting a row with no agency into the notices table from Read the Code. Write one sentence for each about what the database refused and why that refusal is more reliable than the same check written in Python. A constraint holds for every row that will ever exist, including rows inserted by code you have not written yet.

AI collaboration

Checkpoint

What does a PRIMARY KEY constraint guarantee?
Why does WHERE amount != NULL return no rows?
Name two reasons to avoid SELECT * in application code.
Why filter in SQL rather than in Python after fetching?

Answers

That no two rows in the table share that value, enforced by the database for every insert, including ones from code written later.
NULL means unknown, so any comparison with it is unknown rather than true, and WHERE keeps only rows where the condition is definitely true. Use IS NOT NULL.
It returns columns you did not ask for, so positional access breaks silently when the schema changes; and it hides what the code actually depends on from anyone reading it.
The database can use an index to avoid reading rows it will discard, and only the rows you want cross the boundary into your program. Python filtering has to receive everything first.

Summary and next step

Tables carry constraints the database enforces, NULL is unknown and needs IS NULL, name your columns, pass values as parameters, and let the database do the filtering. Next: splitting the data across tables that each describe one thing, and joining them back together.