# Exercise: Plant Shop Catalog

Move Fern & Fig Plant Shop's catalog into SQLite by writing six functions with hand-written SQL, to make the table with its rules, add a plant, list the pet-safe plants cheapest first, search by part of a name, change a price and remove a plant, every value passed with a placeholder, and let pytest check each one on a fresh database in memory.

Apprentice · SQLite · SQL · Database · Updated November 19, 2026

By Gregor Dzierzon

Fern & Fig's catalog has outgrown its JSON file. Last Saturday two people at the counter saved a change at the same moment and one of them was lost, and the website's search reads all eight plants to find one. The owner wants the catalog in a database. You'll write the six functions that do the work, each one a few lines of Python around hand-written SQL: make the table with its rules, add a plant, list the pet-safe plants cheapest first, search by part of a name, change a price and remove a plant.

These six functions are the data code of a real API. In *Keeping the Data Code in One Place*, an API's routes call functions exactly like these and never write SQL themselves.

## Get the exercise code

Open this lesson's GitHub repository, [github.com/ask-codey/exercise-fastapi-plant-shop-catalog](https://github.com/ask-codey/exercise-fastapi-plant-shop-catalog), also linked from the **Exercise** box beside the lesson. Clone it into your `python-course` folder. In VS Code's terminal:

```bash
cd ~/python-course
git clone https://github.com/ask-codey/exercise-fastapi-plant-shop-catalog.git
```

Git makes a folder, `exercise-fastapi-plant-shop-catalog`, with the exercise code inside. *Get the Exercise Code with Git* walks through cloning step by step, if you'd like a reminder. Open that folder in VS Code (**File > Open Folder**), open its terminal with **Terminal > New Terminal**, and make the exercise's virtual environment. On Windows (PowerShell):

```powershell
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install -r requirements.txt
```

On a Mac (zsh):

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

The prompt starts with `(.venv)` once the virtual environment is on. The exercise's README says what each command does, and what to do if PowerShell won't run the second one. SQLite itself needs nothing: it comes with Python.

The folder holds:

- `catalog.py`: the six functions, for you to finish.
- `plants.json`: the shop's catalog, eight plants. Ready to use.
- `load_catalog.py`: moves the eight plants into a database file, `shop.db`, with your code, and asks it a few questions. Ready to use.
- `tests/test_catalog.py`: the tests, which open a fresh database in memory for every check. You don't need to change it.
- `requirements.txt` and `pytest.ini`: the one package the tests need, pytest, and where pytest finds the tests and your code.

## What to do

Every function in `catalog.py` takes `conn`, an open `sqlite3` connection, and stops with a `NotImplementedError` that says *Not built yet.* The docstring under each one says what it should do, with examples:

```python
def reprice(conn, plant_id, price):
    """Change the price of the plant with that id, commit, and return True when a plant changed,
    or False when no plant has that id.

    reprice(conn, 3, 19.5)    -> True, and the Boston Fern now costs 19.5
    reprice(conn, 99, 10.0)   -> False, and nothing changes
    """
    raise NotImplementedError("Not built yet.")
```

Replace each `raise NotImplementedError("Not built yet.")` line with your own code:

1. **`create_tables(conn)`**: makes the `plants` table if it isn't there yet, with seven columns: `id`, `name`, `light`, `water`, `price`, `pet_safe` and `care_tip`. Its docstring gives each column's type and rules.
2. **`add_plant(conn, plant)`**: adds one plant from a dictionary like the ones in `plants.json`, commits, and returns its new id.
3. **`pet_safe_plants(conn)`**: returns the pet-safe plants, cheapest first.
4. **`find_plants(conn, word)`**: returns the plants with the word anywhere in their name, sorted by name.
5. **`reprice(conn, plant_id, price)`**: changes one plant's price, commits, and says whether the plant was there.
6. **`remove_plant(conn, plant_id)`**: removes one plant, commits, and says whether it was there.

Most tests start from the eight plants in `plants.json`, given ids 1 to 8 in the file's order. These are the checks they make, with the answer each one expects:

```text
create_tables(conn)                     a plants table with id, name, light, water, price, pet_safe and
                                        care_tip, in that order
create_tables(conn) a second time       no error, and the row added in between is still there
the table, given no name, light or water    refused with sqlite3.IntegrityError
the table, given a price of 0           refused with sqlite3.IntegrityError
the table, given no pet_safe            pet_safe 0, care_tip None
add_plant(conn, Calathea)               9, and row 9 is Calathea, medium, weekly, 21.0, 1, "Use filtered water."
add_plant(conn, a plant with no care_tip)   its care_tip is None
add_plant(conn, Grandma's Fern)         the name is saved with its apostrophe
pet_safe_plants(conn)                   Spider Plant (12.5), Peperomia (14.0), Boston Fern (18.0) and
                                        Parlor Palm (29.0), each as {"id": ..., "name": ..., "price": ...}
pet_safe_plants(conn), an empty table   []
find_plants(conn, "fern")               [{"id": 3, "name": "Boston Fern", "price": 18.0}]
find_plants(conn, "PLANT")              Snake Plant, Spider Plant and ZZ Plant, in that order
find_plants(conn, "cactus")             []
find_plants(conn, "x' OR '1'='1")       [], with no error
reprice(conn, 3, 19.5)                  True, plant 3 costs 19.5, and the other seven prices don't change
reprice(conn, 99, 10.0)                 False
remove_plant(conn, 4)                   True, plant 4 is gone, and 7 plants are left
remove_plant(conn, 99)                  False, and all 8 plants are still there
```

Three more tests check that `add_plant`, `reprice` and `remove_plant` commit their change before they return.

**Every value goes in with a `?` placeholder.** A name with an apostrophe and a search for `x' OR '1'='1` are among the tests, so SQL built with an f-string fails them. The one place an f-string belongs is the search's value, `f"%{word}%"`, which then goes through the placeholder.

## Check your work

Run the tests from the exercise folder, with the virtual environment on:

```bash
pytest
```

At first all 24 tests fail. That's expected: each function you finish turns its tests green. Each test opens a fresh database in memory, so the tests never touch `shop.db`. A failing test's message says what it did, what it expected and what came back, for example *reprice(conn, 99, 10.0) should give False, but it gave None.*

To see your code at work on a real database file, run `python load_catalog.py`. It makes `shop.db` fresh each time it runs.

## Hints

- **Start with `create_tables`.** The `CREATE TABLE IF NOT EXISTS` in *Tables and Rows: CREATE TABLE and INSERT* is almost exactly the one you need, column for column.
- **Read the plant dictionary key by key.** `plant["name"]` reads the name, and `plant.get("care_tip")` gives `None` when a plant has no tip. `True` and `False` go into an `INTEGER` column as 1 and 0.
- **The cursor knows.** `cursor.lastrowid` is the new row's id after an `INSERT`, and `cursor.rowcount` is how many rows an `UPDATE` or `DELETE` changed (*Changing Rows: UPDATE and DELETE*). `rowcount == 1` is already `True` or `False`.
- **Rows by name.** `conn.row_factory = sqlite3.Row`, then `dict(row)` for each row, as *Reading Rows: SELECT, WHERE and ORDER BY* builds its list of dictionaries. `SELECT` only the three columns the dictionaries need.
- **Search with LIKE.** `WHERE name LIKE ?` with the value `f"%{word}%"`; SQLite's `LIKE` already ignores capital letters for A to Z (*Placeholders, and Why SQL Is Never Built from Strings*).
- **Commit before you return.** A change that isn't committed is only a draft.

## When you're done

Every test is green: pytest's last line says `24 passed`. And `python load_catalog.py` prints:

```text
Moved 8 plants from plants.json into shop.db.

Safe for pets, cheapest first:
  Spider Plant: $12.50
  Peperomia: $14.00
  Boston Fern: $18.00
  Parlor Palm: $29.00

A search for fern: ['Boston Fern']
A search for x' OR '1'='1: []

The Boston Fern goes up to $19.50: True
Plant 99 goes up to $10.00: False
The Golden Pothos is discontinued: True
Plant 99 is discontinued: False
```

Then open the file with `python -m sqlite3 shop.db` (on a Mac, `python3`) and ask it yourself:

```text
sqlite> SELECT id, name, price, pet_safe FROM plants;
(1, 'Snake Plant', 24.0, 0)
(2, 'Spider Plant', 12.5, 1)
(3, 'Boston Fern', 19.5, 1)
(5, 'Parlor Palm', 29.0, 1)
(6, 'Fiddle-Leaf Fig', 55.0, 0)
(7, 'Peperomia', 14.0, 1)
(8, 'ZZ Plant', 32.0, 0)
sqlite> .quit
```

Plant 4 is gone and the Boston Fern costs $19.50: your code's changes were saved into the file. Notice that the ids didn't move up to fill the gap. Why does that matter, if a customer's order says "plant 5"?

## Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here. *Hand In Your Work with Git* walks through each step.

1. On GitHub, click **+**, then **New repository**. Name it (for example `exercise-fastapi-plant-shop-catalog`), choose **Public**, and click **Create repository**. Leave out the README: your folder already has one.
2. In VS Code's terminal, in your exercise folder, send your work to your new repo. Point your copy at your own repo, then commit and push:

   ```bash
   git remote set-url origin https://github.com/your-name/exercise-fastapi-plant-shop-catalog.git
   git add .
   git commit -m "My solution"
   git push -u origin main
   ```

3. Paste your repo's address, such as `https://github.com/your-name/exercise-fastapi-plant-shop-catalog`, into **Submit your exercise** on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

## So what

Making a table, adding, finding, changing and removing rows, every value through a placeholder, is the data work behind nearly every API, and you've now written all of it in SQL that you could read aloud.

## Key terms

- [CREATE TABLE](https://www.ask-codey.com/glossary/create-table)
- [INSERT](https://www.ask-codey.com/glossary/insert)
- [SELECT](https://www.ask-codey.com/glossary/select-database)
- [UPDATE](https://www.ask-codey.com/glossary/update)
- [DELETE](https://www.ask-codey.com/glossary/delete)
- [Parameterized Query](https://www.ask-codey.com/glossary/parameterized-query)

## Related

- [What a Database Is, and Why SQLite](https://www.ask-codey.com/lessons/fastapi-sqlite-what-a-database-is)
- [Tables and Rows: CREATE TABLE and INSERT](https://www.ask-codey.com/lessons/fastapi-sql-create-and-insert)
- [Reading Rows: SELECT, WHERE and ORDER BY](https://www.ask-codey.com/lessons/fastapi-sql-select)
- [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/exercise-fastapi-plant-shop-catalog
