# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
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)
```

```text
[('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:

```python
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())
```

```text
[('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:

```python
print(conn.execute("SELECT name FROM plants WHERE name LIKE ?", ("%fern%",)).fetchall())
print(conn.execute("SELECT name FROM plants WHERE name LIKE ?", ("%plant",)).fetchall())
```

```text
[('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:

```python
print(conn.execute("SELECT name, price FROM plants ORDER BY price DESC LIMIT 3").fetchall())
```

```text
[('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:

```python
print(conn.execute("SELECT name, price FROM plants WHERE id = ?", (3,)).fetchone())
print(conn.execute("SELECT name, price FROM plants WHERE id = ?", (99,)).fetchone())
```

```text
('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()`:

```python
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",))])
```

```text
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 whose `plants` table has the columns `id`, `name`, `light`, `price` and `pet_safe` (1 for yes, 0 for no).

**Your job:** write the function `pet_safe_plants(conn)`.

- Return the plants whose `pet_safe` is 1, as a list of dictionaries.
- Each dictionary has two keys, `name` and `price`, 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:

```text
[{"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.

## Key terms

- [SELECT](https://www.ask-codey.com/glossary/select-database)
- [WHERE](https://www.ask-codey.com/glossary/where-database)
- [ORDER BY](https://www.ask-codey.com/glossary/order-by)
- [LIKE Predicate](https://www.ask-codey.com/glossary/like-predicate)
- [Query](https://www.ask-codey.com/glossary/query)

## Related

- [Tables and Rows: CREATE TABLE and INSERT](https://www.ask-codey.com/lessons/fastapi-sql-create-and-insert)
- [Changing Rows: UPDATE and DELETE](https://www.ask-codey.com/lessons/fastapi-sql-update-and-delete)
- [Placeholders, and Why SQL Is Never Built from Strings](https://www.ask-codey.com/lessons/fastapi-sql-placeholders-and-injection)

Source: https://www.ask-codey.com/lessons/fastapi-sql-select
