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

By

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, also linked from the Exercise box beside the lesson. Clone it into your python-course folder. In VS Code's terminal:

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

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

On a Mac (zsh):

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:

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:

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:

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:

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:

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:

    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

Related