Reading Rows: SELECT, WHERE and ORDER BY
SELECT asks a table for the columns you name, WHERE keeps only the rows that pass its test (with AND, OR and LIKE), ORDER BY sorts them and LIMIT caps how many come back, and in Python fetchone and fetchall read the answer, with sqlite3.Row giving rows you read by column name.
Apprentice · SQLite · SQL · Database · Updated
After this lesson you can
- Read rows with SELECT, choosing columns and filtering with WHERE, AND, OR and LIKE
- Sort and limit results with ORDER BY and LIMIT, and read them with fetchone, fetchall and sqlite3.Row
Key takeaways
- SELECT names the columns you want and FROM names the table, and WHERE keeps only the rows that pass its test.
- AND needs both tests to pass, OR needs either one, and LIKE matches text with % standing for any letters.
- ORDER BY sorts the rows, smallest first unless you add DESC, and LIMIT caps how many come back. Without ORDER BY, no order is promised.
- fetchall gives a list of every row, and fetchone gives the next row, or None when there's none, which is how an API spots a missing item.
- With conn.row_factory = sqlite3.Row, a row is read by column name, and dict(row) turns it into a dictionary ready to send as JSON.
Every list screen and every search box in an app asks a database a question: the orders still open, the shoes in size 9, the newest posts first. That question is a SELECT, and it's the SQL you'll write most often, because an API spends most of its time reading.
A customer at Fern & Fig asks for "something pet-safe under $20". The table could hand over every plant and let Python sort it out with a loop and if. That works for six plants. With six thousand, the program reads them all to keep a few, and every row is a tuple where row[3] means nothing to the next person reading the code. The database can do the choosing itself.
SELECT some columns, WHERE some rows
Here is a catalog of six plants, and a question for it:
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, light TEXT NOT NULL, price REAL NOT NULL, pet_safe INTEGER NOT NULL DEFAULT 0)")
conn.executemany(
"INSERT INTO plants (name, light, price, pet_safe) VALUES (?, ?, ?, ?)",
[
("Snake Plant", "low", 24.0, 0),
("Spider Plant", "bright", 12.5, 1),
("Boston Fern", "medium", 18.0, 1),
("Golden Pothos", "low", 15.0, 0),
("Parlor Palm", "low", 29.0, 1),
("Peperomia", "medium", 14.0, 1),
],
)
rows = conn.execute("SELECT name, price FROM plants WHERE price < ?", (20,)).fetchall()
print(rows)
[('Spider Plant', 12.5), ('Boston Fern', 18.0), ('Golden Pothos', 15.0), ('Peperomia', 14.0)]
Read it as a sentence: "select the name and price from plants where the price is less than 20". SELECT names the columns you want back, in the order you want them (* means all of them), FROM names the table, and WHERE keeps only the rows that pass its test. The tests use = (equal), <> (not equal), <, <=, > and >=, and the value comes in through a ? placeholder, as in every INSERT.
AND joins two tests that must both pass; OR joins two where either will do:
print(conn.execute("SELECT name FROM plants WHERE light = ? AND pet_safe = ?", ("low", 1)).fetchall())
print(conn.execute("SELECT name FROM plants WHERE light = ? OR price < ?", ("bright", 15)).fetchall())
[('Parlor Palm',)]
[('Spider Plant',), ('Peperomia',)]
Only one low-light plant is pet-safe. The second question finds Spider Plant for its bright light and Peperomia for its $14.00 price. Each row is a tuple, so one column comes back as ('Parlor Palm',), with a comma that makes it a tuple of one.
LIKE: searching text
A search box doesn't get whole names. LIKE (read "like") matches text against a pattern, where % (read "percent") stands for any letters, or none:
print(conn.execute("SELECT name FROM plants WHERE name LIKE ?", ("%fern%",)).fetchall())
print(conn.execute("SELECT name FROM plants WHERE name LIKE ?", ("%plant",)).fetchall())
[('Boston Fern',)]
[('Snake Plant',), ('Spider Plant',)]
%fern% means "anything, then fern, then anything", and %plant means names that end in "plant". SQLite's LIKE ignores capital letters for the letters A to Z, so fern found Fern. The % signs go in the value, not in the SQL.
ORDER BY and LIMIT
Without ORDER BY, a database promises no order at all: rows often come back in the order they went in, but nothing guarantees it. ORDER BY sorts them, smallest first (ASC, for ascending, is the default) or largest first with DESC (descending). LIMIT keeps only the first few:
print(conn.execute("SELECT name, price FROM plants ORDER BY price DESC LIMIT 3").fetchall())
[('Parlor Palm', 29.0), ('Snake Plant', 24.0), ('Boston Fern', 18.0)]
The three most expensive plants. The parts always go in this order: SELECT, FROM, WHERE, ORDER BY, LIMIT.
One row or many, by name
fetchall() gives a list of every row. fetchone() gives the next row, and None when there isn't one, which is how an API knows to answer 404:
print(conn.execute("SELECT name, price FROM plants WHERE id = ?", (3,)).fetchone())
print(conn.execute("SELECT name, price FROM plants WHERE id = ?", (99,)).fetchone())
('Boston Fern', 18.0)
None
Tuples are hard to read: row[1] is the price only if you remember the SELECT. Set the connection's row_factory (the thing that builds each row) to sqlite3.Row, and each row can be read by its column's name, and turned into a dictionary with dict():
conn.row_factory = sqlite3.Row
row = conn.execute("SELECT id, name, price FROM plants WHERE id = ?", (3,)).fetchone()
print(row["name"], row["price"])
print(dict(row))
print([dict(r) for r in conn.execute("SELECT name, price FROM plants WHERE light = ? ORDER BY name", ("medium",))])
Boston Fern 18.0
{'id': 3, 'name': 'Boston Fern', 'price': 18.0}
[{'name': 'Boston Fern', 'price': 18.0}, {'name': 'Peperomia', 'price': 14.0}]
A list of dictionaries is exactly what a FastAPI route can return as JSON, so this is the shape your data module will hand to its routes.
Do it: list the pet-safe plants
Fern & Fig's website has a "Safe for pets" page, cheapest first, and it needs the list from the database.
conn: an open connection to a database whoseplantstable has the columnsid,name,light,priceandpet_safe(1 for yes, 0 for no).
Your job: write the function pet_safe_plants(conn).
- Return the plants whose
pet_safeis 1, as a list of dictionaries. - Each dictionary has two keys,
nameandprice, and nothing else. - Put them in order of price, cheapest first.
- When no plant is pet-safe, return an empty list,
[].
Worked through: with the six plants from this lesson (Snake Plant, Spider Plant, Boston Fern, Golden Pothos, Parlor Palm and Peperomia), four are pet-safe, and cheapest first they are:
[{"name": "Spider Plant", "price": 12.5}, {"name": "Peperomia", "price": 14.0}, {"name": "Boston Fern", "price": 18.0}, {"name": "Parlor Palm", "price": 29.0}]
Practiced live on the lesson page: write the Python right there and check it.
Check yourself
2 questions to try on the lesson page.
So what
Most of what an API does is answer questions about its data, and SELECT with WHERE, ORDER BY and sqlite3.Row lets the database do the finding and hand back rows ready to send as JSON.