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
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.txtandpytest.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:
create_tables(conn): makes theplantstable if it isn't there yet, with seven columns:id,name,light,water,price,pet_safeandcare_tip. Its docstring gives each column's type and rules.add_plant(conn, plant): adds one plant from a dictionary like the ones inplants.json, commits, and returns its new id.pet_safe_plants(conn): returns the pet-safe plants, cheapest first.find_plants(conn, word): returns the plants with the word anywhere in their name, sorted by name.reprice(conn, plant_id, price): changes one plant's price, commits, and says whether the plant was there.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. TheCREATE TABLE IF NOT EXISTSin 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, andplant.get("care_tip")givesNonewhen a plant has no tip.TrueandFalsego into anINTEGERcolumn as 1 and 0. - The cursor knows.
cursor.lastrowidis the new row's id after anINSERT, andcursor.rowcountis how many rows anUPDATEorDELETEchanged (Changing Rows: UPDATE and DELETE).rowcount == 1is alreadyTrueorFalse. - Rows by name.
conn.row_factory = sqlite3.Row, thendict(row)for each row, as Reading Rows: SELECT, WHERE and ORDER BY builds its list of dictionaries.SELECTonly the three columns the dictionaries need. - Search with LIKE.
WHERE name LIKE ?with the valuef"%{word}%"; SQLite'sLIKEalready 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.
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.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 mainPaste 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.