SQLite - Your First Query, Filtering and Ordering

Context

A database keeps tables of records, every row of a table carrying the same named and typed columns, and answers questions written in SQL, a language for saying which rows and columns you want back. SQLite is a database engine that runs inside your own Python program.

The rows a price model trains on usually sit in a database table long before any script reads them: listings, sales, user events, logged predictions. A query cuts that table down to what the model needs, the listings with a known price in two neighborhoods, cheapest first, and only those rows ever reach your script.

The worst ways this goes wrong are quiet. A plain SQLite table stores a scraped price of '350k' in a numeric column, a price filter drops every listing whose price is missing, a "three cheapest" list comes back led by a listing with no price, and one forgotten line loses every row a script inserted. Module 1 parsed CSV files into records by hand, and this lesson hands such records to a database that checks their types and answers questions about them.

Goal

• Connect to a SQLite database file from Python, run a query, and read its rows back as tuples with fetchall and fetchone.
• Build a STRICT table, insert rows through ? placeholders with executemany, and commit them.
• Compute new columns in a SELECT, and predict when SQLite's division drops the fraction.
• Filter rows with WHERE, parenthesizing AND and OR correctly and testing for NULL explicitly.
• Sort and cut a result with ORDER BY and LIMIT so a top-n query returns the same rows on every run.

Prerequisites

Optional, since the lesson builds everything it uses from an empty folder. They are here if you want to watch a query run before writing one.

• YouTube - "Python SQLite Tutorial: Complete Overview - Creating a Database, Table, and Running Queries" by Corey Schafer. The same standard-library sqlite3 module, from creating a table to querying it.
• Official docs - The Python sqlite3 module, "Tutorial". Connect, create a table, insert, commit and query, the steps this lesson takes with a typed table.

Table of Contents

• Databases, Tables and SQLite
• STRICT Tables and Inserting Rows
• SELECT - Choosing and Computing Columns
• WHERE - Filtering Rows
• ORDER BY and LIMIT

Theory

Nothing here is installed: sqlite3 is in Python's standard library. Save each block as a script in ~/dev/bootcamp and run it with python3, under the name its first comment gives where it gives one. Every script that names housing.db opens the same file, so what one commits the next reads.

Databases, Tables and SQLite

What. A relational database stores data as tables, each a set of rows sharing the same named, typed columns, and answers questions written in SQL. SQLite is a database engine that keeps a whole database in one file, or only in memory when asked, and runs inside your program. Why. A query is answered where the data lives, so a script receives only the rows it asked for instead of loading a whole file and filtering it in Python. When. Reach for SQLite whenever a project's data is a table that several scripts read and write, a training set, a log of model runs, a cache of scraped listings, and no one wants to run a server for it.

A table's layout, its column names and the type each one holds, is its schema, declared when the table is created, and each row is one record, one house listing in listings. SQL (Structured Query Language) describes the result you want, not the steps that produce it.

Most databases run as a server, a separate process your program reaches over a port, Module 0's client and server arrangement, and Module 6 meets one. SQLite has no server: the engine is a library inside your Python process.

sqlite3.connect(path) opens the file, creating an empty database when none exists, and returns a connection. con.execute(sql) makes a cursor, the object that runs one statement and holds its result rows, runs the SQL on it and returns it, a shortcut for con.cursor() then cur.execute(sql). fetchall() returns every remaining row as a list of tuples, fetchone() the next row as one tuple or None once none is left, and con.close() closes the connection. sqlite3.OperationalError is what sqlite3 raises for a statement the database cannot run, a missing table, a misspelled keyword such as SELEC, or an unknown column.

import sqlite3

print(sqlite3.sqlite_version)        # 3.47.1
con = sqlite3.connect("housing.db")  # creates the file if missing
cur = con.execute("SELECT 2 + 3, 'Riverside', 3.5, NULL")
print(cur.fetchall())                # [(5, 'Riverside', 3.5, None)]
cur = con.execute("SELECT 2 + 3")
print(cur.fetchone())                # (5,)
print(cur.fetchone())                # None, no row is left
try:
    con.execute("SELECT * FROM listings")
except sqlite3.OperationalError as err:
    print(err)                       # no such table: listings
con.close()

Each SQL value arrives as its Python counterpart, a whole number as int, a decimal as float, text as str, and NULL, SQL's marker for a missing value, as None. A row is always a tuple, a one-column row included, and the file exists now but holds no table yet.

DON'T:

• DON'T expect sqlite3.connect to notice a misspelled file name. sqlite3.connect("housng.db") creates a new, empty database, and the first query fails with no such table: listings, an error that names the table and never the file.
• DON'T treat what fetchone() returns as the value. It is the tuple (5,), or None when nothing matched, and then fetchone()[0] raises TypeError: 'NoneType' object is not subscriptable.

STRICT Tables and Inserting Rows

What. CREATE TABLE declares a table's columns and their types, and STRICT at its end makes SQLite refuse any value that does not fit its column. INSERT INTO adds rows, and con.commit() makes them permanent. Why. A plain SQLite table stores whatever it is handed, so a price scraped as '350k' sits in a numeric column until a sort or a model trips over it, and an insert never committed vanishes at close without an error. When. Declare every table STRICT, and commit as soon as a batch of inserts is complete.

A STRICT table accepts six type names: INT and INTEGER for whole numbers, REAL, TEXT, BLOB for raw bytes, and ANY for anything. PRIMARY KEY marks the column that identifies each row, unique and, in a STRICT table, never NULL: an INTEGER PRIMARY KEY handed None takes a number one above the largest in use, and any other key handed None is refused. NOT NULL makes a value mandatory, so price INTEGER, declared without it, can hold a missing price.

DROP TABLE IF EXISTS removes a table if it is there, so a build script opening with it can run again, where a second bare CREATE TABLE raises table listings already exists. Each ? placeholder marks where a value goes, the values travel beside the SQL in a tuple bound in order, and executemany runs the statement once per tuple. A triple-quoted string may run across several lines.

# 'build_housing_db.py', run it again to get these 12 rows back
import sqlite3

LISTINGS = [
    (1, "Downtown", 472000, 88.0, 3, "good", "2024-03-02"),
    (2, "Riverside", 318500, 74.5, 2, "fair", "2024-01-15"),
    (3, "Hillcrest", 389000, 120.0, 4, "excellent", "2024-02-20"),
    (4, "Eastgate", 228000, 61.0, 2, "poor", "2024-03-11"),
    # None is how Python sends NULL: listing 5 has no price
    (5, "Downtown", None, 95.0, 3, "good", "2024-02-08"),
    (6, "Northend", 274500, 102.5, 4, None, "2024-01-29"),
    (7, "Riverside", 420000, 110.0, 3, "good", "2024-03-18"),
    (8, "Lakeview", 351000, 97.0, 3, "excellent", "2024-02-14"),
    (9, "Southgate", 199000, 58.0, 1, "fair", "2024-01-07"),
    (10, "Hillcrest", 389000, 105.0, 3, "good", "2024-03-25"),
    (11, "Downtown", 512000, 101.0, 4, "excellent", "2024-01-22"),
    (12, "Eastgate", 300000, 83.0, 3, "fair", "2024-02-27"),
]

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS listings")
con.execute("""
    CREATE TABLE listings (
        id INTEGER PRIMARY KEY,
        neighborhood TEXT NOT NULL,
        price INTEGER,
        area_sqm REAL NOT NULL,
        rooms INTEGER NOT NULL,
        condition TEXT,
        listed_on TEXT NOT NULL
    ) STRICT
""")
# seven '?', filled from each tuple in turn
con.executemany("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", LISTINGS)
con.commit()      # makes the 12 inserts permanent
rows = con.execute("SELECT * FROM listings").fetchall()
print(len(rows))  # 12
con.close()

Run it now, and again whenever an experiment has changed listings. An INSERT opens a transaction, a group of changes kept or discarded together, and con.commit() keeps them. Until then, only the connection that made them sees them.

# 'insert_bad_price.py'
import sqlite3

ROW = (13, "Lakeview", "350k", 90.0, 3, "good", "2024-04-01")
con = sqlite3.connect("housing.db")
con.execute("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", ROW)
con.commit()

Traceback (most recent call last):
  File "/home/goku/dev/bootcamp/insert_bad_price.py", line 6, in <module>
    con.execute("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", ROW)
sqlite3.IntegrityError: cannot store TEXT value in INTEGER column listings.price

sqlite3.IntegrityError is raised for a row that breaks a rule its table declares, a type, a NOT NULL or a duplicate key, and this one names the column. Below, ":memory:" is a database that lives in RAM and vanishes at close, and typeof() names how SQLite stored a value.

import sqlite3

mem = sqlite3.connect(":memory:")
mem.execute("CREATE TABLE loose (price INTEGER, rooms INTEGER)")
mem.execute("CREATE TABLE tight (price INTEGER, rooms INTEGER) STRICT")
mem.execute("INSERT INTO loose VALUES (?, ?)", ("350k", 2.5))
mem.execute("INSERT INTO tight VALUES (?, ?)", ("350000", "3"))

loose = mem.execute("SELECT price, typeof(price), rooms, typeof(rooms) FROM loose")
print(loose.fetchall())  # [('350k', 'text', 2.5, 'real')]
tight = mem.execute("SELECT price, typeof(price), rooms, typeof(rooms) FROM tight")
print(tight.fetchall())  # [(350000, 'integer', 3, 'integer')]
try:
    mem.execute("INSERT INTO tight VALUES (?, ?)", (350000, 2.5))
except sqlite3.IntegrityError as err:
    print(err)           # cannot store REAL value in INTEGER column tight.rooms
mem.close()

The plain table kept text and a decimal in two INTEGER columns and said nothing. STRICT still converts when nothing is lost, '350000' and '3' became whole numbers, and refuses what would lose something, the 2.5 rooms.

A forgotten commit is just as quiet. The script below shows it, then uses the connection as a context manager, the with form from Lesson 1.5.2: with con: commits every change still uncommitted on the connection when its block ends cleanly and rolls them all back when an exception leaves it, and it closes nothing.

import sqlite3

INSERT = "INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)"
GOOD = (13, "Lakeview", 365000, 99.0, 3, "good", "2024-04-02")
BAD = (14, "Lakeview", "365k", 99.0, 3, "good", "2024-04-02")

def count(con):
    return len(con.execute("SELECT * FROM listings").fetchall())

con = sqlite3.connect("housing.db")
con.execute(INSERT, GOOD)
print(count(con))  # 13, this connection sees its own insert
con.close()        # closed without commit()

con = sqlite3.connect("housing.db")
print(count(con))  # 12, the insert is gone
try:
    with con:
        con.execute(INSERT, GOOD)
        con.execute(INSERT, BAD)
except sqlite3.IntegrityError as err:
    print(err)     # cannot store TEXT value in INTEGER column listings.price
print(count(con))  # 12, GOOD was rolled back with BAD
con.close()        # 'with con:' left the connection open

DON'T:

• DON'T declare a table without STRICT and trust its column types. The plain table stored '350k' and 2.5 in INTEGER columns and raised nothing.
• DON'T forget con.commit(). The script counted 13 rows, the next connection found 12, and nothing raised.
• DON'T expect with con: to close the connection the way with open(...) closes a file. con.close() is still yours to call.
• DON'T give a STRICT column a type outside the six. CREATE TABLE t (price FLOAT) STRICT raises unknown datatype for t.price: "FLOAT".

SELECT - Choosing and Computing Columns

What. SELECT names the columns a query returns, * standing for all of them, and computes new columns from expressions, named with AS. SELECT DISTINCT drops repeated rows. Why. Comparing listings of different sizes takes a handful of columns, some derived, such as price per square meter, and computing them in the query means only finished columns reach Python. When. Name the columns you need rather than * in any script meant to last, and compute a derived column in SQL whenever it comes from one row's own columns.

Columns come back in the order named, and an expression stands wherever a column can, arithmetic with +, -, * and / or a function such as ROUND(x, n), which rounds to n places. AS names a result column, the name ORDER BY can sort on and cur.description reports, a tuple per column with the name first.

import sqlite3

con = sqlite3.connect("housing.db")
cur = con.execute("""
    SELECT id, neighborhood, ROUND(price / area_sqm, 1) AS price_per_sqm
    FROM listings
""")
print([column[0] for column in cur.description])
for row in cur.fetchall():
    print(row)
con.close()

['id', 'neighborhood', 'price_per_sqm']
(1, 'Downtown', 5363.6)
(2, 'Riverside', 4275.2)
(3, 'Hillcrest', 3241.7)
(4, 'Eastgate', 3737.7)
(5, 'Downtown', None)
(6, 'Northend', 2678.0)
(7, 'Riverside', 3818.2)
(8, 'Lakeview', 3618.6)
(9, 'Southgate', 3431.0)
(10, 'Hillcrest', 3704.8)
(11, 'Downtown', 5069.3)
(12, 'Eastgate', 3614.5)

Arithmetic on NULL gives NULL, so listing 5 has no price per square meter, and nothing says so. An INTEGER divided by an INTEGER is an INTEGER with the fraction dropped, unlike Python's /, and one REAL operand, such as area_sqm or 1.0, makes the result REAL. DISTINCT compares whole result rows and treats every NULL as the same value.

import sqlite3

con = sqlite3.connect("housing.db")
halves = con.execute("SELECT 7 / 2, 7 / 2.0")
print(halves.fetchone())      # (3, 3.5)
print(7 / 2)                  # 3.5
cur = con.execute("SELECT id, price / rooms, price * 1.0 / rooms FROM listings")
by_id = {row[0]: row for row in cur.fetchall()}
print(by_id[1])               # (1, 157333, 157333.33333333334)
# ['id', 'price / rooms', 'price * 1.0 / rooms']
print([column[0] for column in cur.description])

pairs = con.execute("SELECT DISTINCT neighborhood, condition FROM listings")
print(len(pairs.fetchall()))  # 11, one per distinct pair
conditions = con.execute("SELECT DISTINCT condition FROM listings")
print(conditions.fetchall())  # [('good',), ('fair',), ('excellent',), ('poor',), (None,)]
con.close()

DON'T:

• DON'T divide one INTEGER column by another and expect a fraction. price / rooms gave 157333 for listing 1 where Python gives 157333.33333333334, so multiply by 1.0 first.
• DON'T leave a computed column without AS. Its name is then the expression's text, 'price / rooms', which any code reading columns by name must repeat exactly.
• DON'T unpack SELECT * rows into names in a script meant to last. lid, hood, price, area, rooms, cond, listed = row raises ValueError: too many values to unpack (expected 7) the day the table gains an eighth column.

WHERE - Filtering Rows

What. WHERE keeps the rows whose condition is true and drops the rest before the remainder of the query sees them. Conditions combine with AND, OR and NOT, and a comparison that meets NULL is neither true nor false. Why. A filter decides which rows a model trains on, and a wrong one keeps or drops rows without raising anything, a common way a training set changes size without anyone deciding it should. When. Use WHERE for every subset, one neighborhood, a price range, the rows with a known target, and test each filter against the rows where its column is NULL.

A comparison uses =, !=, <, <=, > or >=, with text in single quotes. AND binds tighter than OR, as * does than +, so the first two queries below differ only in their parentheses. IN (...) is true when a value equals any listed item, an OR chain without the precedence trap, and BETWEEN a AND b includes both ends. LIKE matches a pattern where % is any run of characters, none included, and _ exactly one, ignoring the case of A to Z, which = does not.

import sqlite3

con = sqlite3.connect("housing.db")
QUERIES = [
    "SELECT id FROM listings WHERE neighborhood = 'Riverside' OR neighborhood = 'Downtown' AND price < 400000",
    "SELECT id FROM listings WHERE (neighborhood = 'Riverside' OR neighborhood = 'Downtown') AND price < 400000",
    "SELECT id FROM listings WHERE neighborhood IN ('Riverside', 'Downtown') AND price < 400000",
    "SELECT id, price FROM listings WHERE price BETWEEN 300000 AND 351000",
    "SELECT id, neighborhood FROM listings WHERE neighborhood LIKE '%gate'",
    "SELECT id, condition FROM listings WHERE condition LIKE '_oo_'",
    "SELECT id FROM listings WHERE neighborhood LIKE 'downtown'",
    "SELECT id FROM listings WHERE neighborhood = 'downtown'",
]
for sql in QUERIES:
    print(con.execute(sql).fetchall())
con.close()

[(2,), (7,)]
[(2,)]
[(2,)]
[(2, 318500), (8, 351000), (12, 300000)]
[(4, 'Eastgate'), (9, 'Southgate'), (12, 'Eastgate')]
[(1, 'good'), (4, 'poor'), (5, 'good'), (7, 'good'), (10, 'good')]
[(1,), (5,), (11,)]
[]

The first query read as Riverside at any price, or Downtown under 400000. A comparison with NULL is itself NULL, unknown, and WHERE keeps only rows whose condition is true, so price = NULL matches nothing, NOT flips true and false but leaves unknown as unknown, and the tests for a missing and a present value are IS NULL and IS NOT NULL. The same rule takes listing 5 out of both halves of a split:

import sqlite3

con = sqlite3.connect("housing.db")
# (None, None), unknown both times
print(con.execute("SELECT NULL = NULL, NULL > 300000").fetchone())
equals = con.execute("SELECT id FROM listings WHERE price = NULL")
print(equals.fetchall())      # []
is_null = con.execute("SELECT id FROM listings WHERE price IS NULL")
print(is_null.fetchall())     # [(5,)]
above = con.execute("SELECT id FROM listings WHERE price > 300000").fetchall()
rest = con.execute("SELECT id FROM listings WHERE price <= 300000").fetchall()
print(len(above), len(rest))  # 7 4
con.close()

Values from Python reach a query through placeholders, never an f-string. Pasted into SQL text, a value is read as SQL: an apostrophe ends the quoted text early, and typed text can rewrite the condition, the attack called SQL injection. A placeholder sends the value separately, to be used as a value and never read as SQL, and one value is the one-item tuple (hood,) from Lesson 1.4.2.

import sqlite3

con = sqlite3.connect("housing.db")
name = "St Mary's"
try:
    con.execute(f"SELECT id FROM listings WHERE neighborhood = '{name}'")
except sqlite3.OperationalError as err:
    print(err)                   # near "s": syntax error

hood = "Hillcrest' OR '1' = '1"  # typed into a search box
sql = f"SELECT id FROM listings WHERE neighborhood = '{hood}'"
print(sql)                       # SELECT id FROM listings WHERE neighborhood = 'Hillcrest' OR '1' = '1'
rows = con.execute(sql).fetchall()
print(len(rows))                 # 12
safe = "SELECT id FROM listings WHERE neighborhood = ?"
rows = con.execute(safe, (hood,)).fetchall()
print(rows)                      # []
con.close()

DON'T:

• DON'T write price = NULL. It matches no row, listing 5 included, and price IS NULL is the test.
• DON'T expect price > 300000 and price <= 300000 to split the table. They returned 7 and 4 of 12, and the listing with no price was in neither.
• DON'T mix AND and OR without parentheses. The bare query let listing 7, priced 420000, through a 400000 cap.
• DON'T build SQL with an f-string around a value. An apostrophe broke the statement, and Hillcrest' OR '1' = '1 returned all 12 rows.
• DON'T pass one value as ("Hillcrest"). Without the trailing comma it is a string, and sqlite3 counts its 9 characters as 9 values and raises sqlite3.ProgrammingError.
• DON'T put a text value in double quotes. SQLite reads "condition" as the column of that name, so condition = "condition" matched all 11 listings that have one.

ORDER BY and LIMIT

What. ORDER BY sorts the result by one or more keys, ascending unless a key says DESC, and LIMIT n keeps the first n rows of that order, after skipping OFFSET k rows when an offset is given. Why. "The three most expensive listings" is a sort followed by a cut, and a cut made without a sort, or through a tie, keeps rows nothing in the query chose. When. Add ORDER BY whenever the order of the rows matters downstream, and always before LIMIT.

A sort key is a column, an expression or an alias from the SELECT list, and each further key orders the rows the earlier ones leave tied. SQLite sorts NULL below every value, first ascending and last descending, and NULLS LAST or NULLS FIRST after a key moves it. LIMIT 4 OFFSET 4 skips four rows and keeps the next four. A cut between equal keys is decided by nothing the query says: listings 3 and 10 both cost 389000, and ORDER BY price DESC LIMIT 4 kept 3 only because the engine produced it first.

import sqlite3

con = sqlite3.connect("housing.db")
QUERIES = [
    "SELECT id, neighborhood, price FROM listings ORDER BY neighborhood, price DESC LIMIT 4",
    # the three cheapest, as first written, then with NULL moved
    "SELECT id, price FROM listings ORDER BY price LIMIT 3",
    "SELECT id, price FROM listings ORDER BY price NULLS LAST LIMIT 3",
    # the four most expensive, cut through a tie, then settled
    "SELECT id, price FROM listings ORDER BY price DESC LIMIT 4",
    "SELECT id, price FROM listings ORDER BY price DESC, listed_on DESC LIMIT 4",
    "SELECT id, price FROM listings ORDER BY price DESC, listed_on DESC LIMIT 4 OFFSET 4",
]
for sql in QUERIES:
    print(con.execute(sql).fetchall())
con.close()

[(11, 'Downtown', 512000), (1, 'Downtown', 472000), (5, 'Downtown', None), (12, 'Eastgate', 300000)]
[(5, None), (9, 199000), (4, 228000)]
[(9, 199000), (4, 228000), (6, 274500)]
[(11, 512000), (1, 472000), (7, 420000), (3, 389000)]
[(11, 512000), (1, 472000), (7, 420000), (10, 389000)]
[(3, 389000), (8, 351000), (2, 318500), (12, 300000)]

A query without ORDER BY promises no order, and SQLite returns rows in whatever order it reaches them. PRAGMA is SQLite's command for reading or changing an engine setting, and this one reverses that order on purpose, so code relying on an order nobody asked for breaks visibly.

import sqlite3

con = sqlite3.connect("housing.db")
QUERY = "SELECT id, price FROM listings LIMIT 3"
print(con.execute(QUERY).fetchall())  # [(1, 472000), (2, 318500), (3, 389000)]
con.execute("PRAGMA reverse_unordered_selects = ON")
print(con.execute(QUERY).fetchall())  # [(12, 300000), (11, 512000), (10, 389000)]
con.close()

DON'T:

• DON'T put LIMIT on a query without ORDER BY and expect particular rows. The same query returned listings 1, 2 and 3, then 12, 11 and 10.
• DON'T ask for the cheapest listings with a bare ORDER BY price. The listing with no price sorts first and takes a slot, so filter it with IS NOT NULL or sort it NULLS LAST.
• DON'T cut through a tie. Name a second key, such as listed_on DESC, so which of two equal prices makes the cut is your decision, not the engine's.

Lab Exercises

Lab Exercise 1: A Valuations Table

Several models estimate each listing's price. In housing.db, build a STRICT table valuations with an INTEGER PRIMARY KEY named id and four required columns, listing_id (a whole number), model (text), estimate (a whole number) and valued_on (text), in a script that can run any number of times. Insert these rows with one executemany call and commit them.

VALUATIONS = [
    (1, 1, "baseline", 455500, "2024-03-05"),
    (2, 1, "gbm", 468250, "2024-03-05"),
    (3, 7, "baseline", 431000, "2024-03-20"),
    (4, 7, "gbm", 418900, "2024-03-20"),
]

In the same script, insert an estimate that arrived as the text '415k', catching and printing the error. Inside with con:, insert listing 10's gbm estimate of 391200 on 2024-03-26 with None as its id, and print the ids a second connection sees. Print each valuation's id, model and estimate in thousands, as estimate / 1000 and rounded to one decimal as estimate_k, then the distinct model names.

Lab Exercise 2: Filtering and Ordering the Listings

Work on listings as build_housing_db.py leaves it, sending every value through a ? placeholder. Print the Riverside and Eastgate listings under 400000. Count the listings whose condition is 'good' and those whose condition is not, print both beside 12, and print the id both queries missed. Print the three cheapest listings that have a price, then the next three, with page size and offset as parameters. Finally, print every listing's id and price per square meter to one decimal, lowest first, the listing with no price last.

import sqlite3

VALUATIONS = [
    (1, 1, "baseline", 455500, "2024-03-05"),
    (2, 1, "gbm", 468250, "2024-03-05"),
    (3, 7, "baseline", 431000, "2024-03-20"),
    (4, 7, "gbm", 418900, "2024-03-20"),
]
INSERT = "INSERT INTO valuations VALUES (?, ?, ?, ?, ?)"

con = sqlite3.connect("housing.db")
con.execute("DROP TABLE IF EXISTS valuations")
con.execute("""
    CREATE TABLE valuations (
        id INTEGER PRIMARY KEY,
        listing_id INTEGER NOT NULL,
        model TEXT NOT NULL,
        estimate INTEGER NOT NULL,
        valued_on TEXT NOT NULL
    ) STRICT
""")
con.executemany(INSERT, VALUATIONS)
con.commit()

try:
    con.execute(INSERT, (5, 10, "baseline", "415k", "2024-03-26"))
except sqlite3.IntegrityError as err:
    print(err)

with con:                              # commits on a clean exit
    con.execute(INSERT, (None, 10, "gbm", 391200, "2024-03-26"))
other = sqlite3.connect("housing.db")  # a second, separate connection
print(other.execute("SELECT id FROM valuations").fetchall())
other.close()

cur = con.execute("""
    SELECT id, model, estimate / 1000, ROUND(estimate / 1000.0, 1) AS estimate_k
    FROM valuations
""")
for row in cur.fetchall():
    print(row)
print(con.execute("SELECT DISTINCT model FROM valuations").fetchall())
con.close()

cannot store TEXT value in INTEGER column valuations.estimate
[(1,), (2,), (3,), (4,), (5,)]
(1, 'baseline', 455, 455.5)
(2, 'gbm', 468, 468.3)
(3, 'baseline', 431, 431.0)
(4, 'gbm', 418, 418.9)
(5, 'gbm', 391, 391.2)
[('baseline',), ('gbm',)]

import sqlite3

con = sqlite3.connect("housing.db")
# 'price < ?' is never true for a NULL price
capped = con.execute("""
    SELECT id, neighborhood, price FROM listings
    WHERE neighborhood IN (?, ?) AND price < ?
""", ("Riverside", "Eastgate", 400000))
print(capped.fetchall())

good = con.execute("SELECT id FROM listings WHERE condition = ?", ("good",))
other = con.execute("SELECT id FROM listings WHERE condition != ?", ("good",))
print(len(good.fetchall()), len(other.fetchall()), 12)
print(con.execute("SELECT id FROM listings WHERE condition IS NULL").fetchall())

PAGE = """
    SELECT id, price FROM listings WHERE price IS NOT NULL
    ORDER BY price, id LIMIT ? OFFSET ?
"""
print(con.execute(PAGE, (3, 0)).fetchall())
print(con.execute(PAGE, (3, 3)).fetchall())

ranked = con.execute("""
    SELECT id, ROUND(price / area_sqm, 1) AS price_per_sqm FROM listings
    ORDER BY price_per_sqm NULLS LAST
""")
for row in ranked.fetchall():
    print(row)
con.close()

[(2, 'Riverside', 318500), (4, 'Eastgate', 228000), (12, 'Eastgate', 300000)]
4 7 12
[(6,)]
[(9, 199000), (4, 228000), (6, 274500)]
[(12, 300000), (2, 318500), (8, 351000)]
(6, 2678.0)
(3, 3241.7)
(9, 3431.0)
(12, 3614.5)
(8, 3618.6)
(10, 3704.8)
(4, 3737.7)
(7, 3818.2)
(2, 4275.2)
(11, 5069.3)
(1, 5363.6)
(5, None)

Theory Assessment

Q1: What the Connection Hands Back

Against the lesson's housing.db, say what each of these returns, by type and value: con.execute("SELECT id FROM listings"), .fetchall() on that, .fetchone() on a query that matched nothing, and .fetchone() on SELECT 2 + 3. Then explain why connecting to housng.db, misspelled, and querying listings raises an error about a table, not a file.

Hint: One of the four is not rows at all. For the second half, ask what sqlite3.connect does when the file it is given does not exist.

Q2: One Scraped Row, Two Tables

A scraper produces (13, "Lakeview", "350k", 90.0, "3", "good", "2024-04-01") for listings. Say what happens to it in the lesson's STRICT table and in the same table without STRICT, naming what becomes of the price and the rooms in each. SQLite sorts every text value after every number, so say what the second outcome means for a script that ranks listings by price.

Hint: STRICT converts a value only when nothing is lost, and a plain table converts what it can and keeps the rest as it came. Ask which of '350k' and '3' survives becoming a whole number.

Q3: The Missing Listing and the Lost Parentheses

Explain why WHERE price > 300000 and WHERE price <= 300000 together return 11 of the 12 listings, why WHERE price = NULL does not find the twelfth, and what does. Then explain why WHERE neighborhood = 'Riverside' OR neighborhood = 'Downtown' AND price < 400000 let listing 7, priced 420000, through.

Hint: In SQL a comparison has three possible results, and WHERE keeps one of them. For the second half, AND and OR follow the kind of rule * and + do.

Q4: Two Cheapest, Two Mistakes

A teammate wants the two cheapest listings that have a price, across Downtown and Riverside, and writes the script below. Without running it, say exactly what it prints and every reason that differs from what was wanted. Then fix it twice, once by removing the listing with no price in WHERE, once by moving it in ORDER BY.

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT id, price FROM listings
    WHERE neighborhood = ? OR neighborhood = ? AND price IS NOT NULL
    ORDER BY price LIMIT 2
""", ("Downtown", "Riverside")).fetchall()
print(rows)

Hint: Rewrite the WHERE with the parentheses SQLite applies, list the listings that pass it, then sort them by price the way SQLite sorts a NULL.

rows = con.execute("""
    SELECT id, price FROM listings
    WHERE neighborhood IN (?, ?) AND price IS NOT NULL
    ORDER BY price LIMIT 2
""", ("Downtown", "Riverside")).fetchall()
print(rows)  # [(2, 318500), (7, 420000)]

rows = con.execute("""
    SELECT id, price FROM listings
    WHERE neighborhood IN (?, ?)
    ORDER BY price NULLS LAST LIMIT 2
""", ("Downtown", "Riverside")).fetchall()
print(rows)  # [(2, 318500), (7, 420000)]

Assignment

Assignment: A Price-Band Report for Any List of Neighborhoods

The pricing team wants to see how listings spread across price bands, for whichever neighborhoods they name that day. Write band_report(con, hoods), taking an open connection and a Python list of neighborhood names of any length, that returns each listing in those neighborhoods as its id, neighborhood, price and band.

The band is computed in SQL with CASE: CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE value3 END takes the value of the first WHEN whose condition is true, or the ELSE value when none is, and stands in a SELECT like any expression, named with AS. The bands are 'unknown' for a missing price, 'under 300k', '300k to 400k' up to and including 400000, and 'over 400k'.

Rows come back most expensive first, unknown prices last, a tie broken by id, and the names reach the database only through placeholders, one ? per name. Print the report for ["Downtown", "Riverside", "Eastgate"], then for ["Hillcrest"]. Then break it on purpose: run the query for Downtown alone with the WHEN branch for a missing price deleted, and explain in a comment which band listing 5 lands in and why.

Hints:

• ", ".join(["?"] * len(hoods)) writes one ? per name, "?, ?, ?" for three, and that string of question marks is the only thing that goes into the SQL text, while the names travel as the parameters.
• A comparison with NULL is never true, so a missing price fails every WHEN price < ... test and falls through to whatever comes after them.

import sqlite3


def band_report(con, hoods):
    marks = ", ".join(["?"] * len(hoods))  # "?, ?, ?" for three names
    # the f-string adds question marks, never a value
    sql = f"""
        SELECT id, neighborhood, price,
               CASE
                   WHEN price IS NULL THEN 'unknown'
                   WHEN price < 300000 THEN 'under 300k'
                   WHEN price <= 400000 THEN '300k to 400k'
                   ELSE 'over 400k'
               END AS band
        FROM listings
        WHERE neighborhood IN ({marks})
        ORDER BY price DESC NULLS LAST, id
    """
    return con.execute(sql, hoods).fetchall()


con = sqlite3.connect("housing.db")
for row in band_report(con, ["Downtown", "Riverside", "Eastgate"]):
    print(row)
print(band_report(con, ["Hillcrest"]))
con.close()

(11, 'Downtown', 512000, 'over 400k')
(1, 'Downtown', 472000, 'over 400k')
(7, 'Riverside', 420000, 'over 400k')
(2, 'Riverside', 318500, '300k to 400k')
(12, 'Eastgate', 300000, '300k to 400k')
(4, 'Eastgate', 228000, 'under 300k')
(5, 'Downtown', None, 'unknown')
[(3, 'Hillcrest', 389000, '300k to 400k'), (10, 'Hillcrest', 389000, '300k to 400k')]

import sqlite3

con = sqlite3.connect("housing.db")
rows = con.execute("""
    SELECT id, price,
           CASE
               WHEN price < 300000 THEN 'under 300k'
               WHEN price <= 400000 THEN '300k to 400k'
               ELSE 'over 400k'
           END AS band
    FROM listings
    WHERE neighborhood = ?
    ORDER BY price DESC NULLS LAST, id
""", ("Downtown",)).fetchall()
print(rows)
# [(11, 512000, 'over 400k'), (1, 472000, 'over 400k'), (5, None, 'over 400k')]
# Listing 5's NULL price makes both comparisons unknown, never true, so CASE
# fell through to ELSE and reports the missing price as an expensive one.

con.close()

Additional Resources

Optional, and nothing later depends on either. They hold the exact rules behind two of this lesson's traps.

• Official docs - SQLite, "STRICT Tables". Every type a STRICT table accepts, and exactly when it converts a value instead of refusing it.
• Official docs - SQLite, "SELECT", section "The ORDER BY clause". Where NULL sorts, NULLS FIRST and NULLS LAST, and the rule that leaves the order of tied rows undefined.

Summary

A database takes two jobs out of your script. A STRICT table checks, refusing a value that does not fit and naming the column, so a bad value costs one failed insert instead of a quietly wrong result. SELECT, WHERE, ORDER BY and LIMIT choose, and only the rows you described come back to Python. Module 1 did both by hand, and what changed is where they run and what guarantees them.

Underneath most of this lesson is the fact that SQL has three answers, not two. A missing value is not zero and not an error, it is unknown, dropped by every comparison in a filter and kept only by IS NULL, so one NULL can change a count, a ranking or a label and leave no trace. The defense is to decide on purpose what each query does with missing values, and to name every order you rely on, ties included. The next lesson adds a second table, and row counts start to move just as quietly.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import sqlite3

con = sqlite3.connect("housing.db")  # file made if missing
con.execute("DROP TABLE IF EXISTS listings")
con.execute("""CREATE TABLE listings (
    id INTEGER PRIMARY KEY, neighborhood TEXT NOT NULL,
    price INTEGER, area_sqm REAL NOT NULL, rooms INTEGER NOT NULL,
    condition TEXT, listed_on TEXT NOT NULL) STRICT""")
con.executemany("INSERT INTO listings VALUES (?, ?, ?, ?, ?, ?, ?)", LISTINGS)
con.commit()                         # or wrap inserts in 'with con:'

rows = con.execute("""
    SELECT id, ROUND(price * 1.0 / area_sqm, 1) AS ppsqm
    FROM listings
    WHERE neighborhood IN (?, ?) AND price IS NOT NULL
    ORDER BY ppsqm DESC, id
    LIMIT 3
""", ("Riverside", "Downtown")).fetchall()
con.close()                          # 'with con:' never closes