Exercise: Farmers Market Vendors

Write the Saturday Farmers Market's data code in one file, with a vendors table and a products table linked by a foreign key, a join, grouped counts that include a vendor with no products, one page of vendors with the total, and a unique email that raises your own DuplicateEmail, and let pytest check each on a fresh database.

Journeyman · SQLite · SQL · Database · Updated

By

The Saturday Farmers Market's website is moving its data into a database: the vendors, the products each one sells, and the answers its pages show, from "who sells strawberries?" to "page 2 of the vendor directory". You'll write all of the market's data code in one file: two tables linked by a foreign key, a join, grouped counts, a page with its total, and a unique email. It's every idea from this chapter in one place, and tests check each answer on a fresh database.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-fastapi-farmers-market-vendors, 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-farmers-market-vendors.git

Git makes a folder, exercise-fastapi-farmers-market-vendors, 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.

The folder holds:

  • market.py: the tables and six functions, for you to finish. DuplicateEmail is already written.
  • data.py: the market's 7 vendors and 10 products, and seed(conn), which adds them to your tables. Pepper Patch sells nothing yet, and three vendors sell strawberries.
  • try_it.py: makes the database in memory, fills it, and prints what each of your functions gives back. Ready to use.
  • tests/test_market.py: the tests. Before each one, a fresh database is made in memory with your create_tables and filled with seed. You don't need to change it.
  • requirements.txt and pytest.ini: pytest at an exact version, and where pytest finds the tests and your code.

What to do

Each function takes conn, an open sqlite3 connection, and gives back plain Python values. Every value from a parameter goes into the SQL with a placeholder, ? or :name. The README's The rules lists every column and every key exactly; in short:

  1. The tables. create_tables(conn) turns on foreign keys first, then makes vendors (id, name, stall, email, organic) with a unique index on email, and products (id, vendor_id, name, price, season), where vendor_id is a foreign key to vendors (id) that deletes a vendor's products with it, and has an index of its own.
  2. A join. products_with_vendor(conn) gives every product as a dictionary with product, price, vendor and stall, sorted by product name, and products with the same name by stall.
  3. A join with a filter. vendors_selling(conn, product) gives the names of the vendors that sell a product with exactly that name, sorted by stall, or [].
  4. Grouped counts. product_counts(conn) gives every vendor as {"vendor": ..., "products": ...}, sorted by stall, Pepper Patch included with 0.
  5. A page and its total. vendor_page(conn, page, size) gives {"vendors": [...], "total": 7}: that page's vendors, each with name and stall, sorted by stall.
  6. A unique email. add_vendor(conn, vendor) adds a vendor and returns its new id, or raises DuplicateEmail when the email is already used.

Check your work

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

pytest

At first all 14 tests stop with an error: create_tables makes no tables yet, so the market's data has nowhere to go. That's expected. Write create_tables first and the first four tests pass; the rest then fail one by one until each function is done. A failing test's message says what it called, what it expected and what came back, for example vendors_selling(conn, "Strawberries") should give ['Green Basket', 'Sunny Acres', 'Cedar Creek Farm'], but it gave None.

To see your answers yourself, run python try_it.py: it prints what each function gives back.

Hints

  • The tables come first. Related Tables and Foreign Keys shows PRAGMA foreign_keys = ON, REFERENCES and ON DELETE CASCADE. Indexes and Unique Values shows CREATE INDEX and CREATE UNIQUE INDEX. The column names must match the README's exactly, because seed fills them by name.
  • Say which name. Both tables have a name column, so give the tables aliases, FROM products AS p JOIN vendors AS v, as Joining Tables does, and pick p.name or v.name.
  • Every vendor, even with nothing to sell. product_counts starts from vendors with a LEFT JOIN, groups by vendor, and counts p.id, not *, as Counting, Grouping and Paging explains.
  • The page. Its offset is (page - 1) * size, and a second query, SELECT COUNT(*) FROM vendors, gives the total.
  • The repeat. seed in data.py inserts from a dictionary with named placeholders such as :email; add_vendor can pass vendor the same way. Catch sqlite3.IntegrityError around the insert and raise DuplicateEmail in its place.

When you're done

Every test is green: pytest's last line says 14 passed. And python try_it.py prints, among its lines:

vendors_selling Strawberries: ['Green Basket', 'Sunny Acres', 'Cedar Creek Farm']
   {'vendor': 'Pepper Patch', 'products': 0}
vendor_page 2 of size 3: {'vendors': [{'name': 'Hilltop Honey', 'stall': 7}, {'name': 'Riverbend Bakery', 'stall': 9}, {'name': 'Cedar Creek Farm', 'stall': 11}], 'total': 7}
add_vendor Blue Door Flowers: 8
add_vendor sunny@example.com again: DuplicateEmail sunny@example.com

Look at what market.py has become: every query the market's website needs, in one file, each function taking a connection and handing back plain lists and dictionaries. That's the shape the next chapter puts behind an API: routes that call functions like these and never write SQL themselves.

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-farmers-market-vendors), 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-farmers-market-vendors.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-farmers-market-vendors, 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

Tables that point at each other, joins that put them back together, counts, pages and unique values are what nearly every API's data code is made of, and you've now written all of them in one tidy file.

Key terms

Related