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

By Gregor Dzierzon

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](https://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:

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

```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.

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:

```bash
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:

```text
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:

   ```bash
   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

- [Foreign Key](https://www.ask-codey.com/glossary/foreign-key)
- [JOIN](https://www.ask-codey.com/glossary/join-database)
- [LEFT OUTER JOIN](https://www.ask-codey.com/glossary/left-outer-join)
- [GROUP BY](https://www.ask-codey.com/glossary/group-by)
- [Pagination](https://www.ask-codey.com/glossary/pagination)
- [Unique Index](https://www.ask-codey.com/glossary/unique-index)

## Related

- [Related Tables and Foreign Keys](https://www.ask-codey.com/lessons/fastapi-sql-foreign-keys)
- [Joining Tables](https://www.ask-codey.com/lessons/fastapi-sql-joins)
- [Counting, Grouping and Paging](https://www.ask-codey.com/lessons/fastapi-sql-counting-and-paging)
- [Indexes and Unique Values](https://www.ask-codey.com/lessons/fastapi-sql-indexes-and-unique)

Source: https://www.ask-codey.com/lessons/exercise-fastapi-farmers-market-vendors
