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
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.DuplicateEmailis already written.data.py: the market's 7 vendors and 10 products, andseed(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 yourcreate_tablesand filled withseed. You don't need to change it.requirements.txtandpytest.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:
- The tables.
create_tables(conn)turns on foreign keys first, then makesvendors(id,name,stall,email,organic) with a unique index onemail, andproducts(id,vendor_id,name,price,season), wherevendor_idis a foreign key tovendors (id)that deletes a vendor's products with it, and has an index of its own. - A join.
products_with_vendor(conn)gives every product as a dictionary withproduct,price,vendorandstall, sorted by product name, and products with the same name by stall. - 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[]. - Grouped counts.
product_counts(conn)gives every vendor as{"vendor": ..., "products": ...}, sorted by stall, Pepper Patch included with0. - A page and its total.
vendor_page(conn, page, size)gives{"vendors": [...], "total": 7}: that page's vendors, each withnameandstall, sorted by stall. - A unique email.
add_vendor(conn, vendor)adds a vendor and returns its new id, or raisesDuplicateEmailwhen 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,REFERENCESandON DELETE CASCADE. Indexes and Unique Values showsCREATE INDEXandCREATE UNIQUE INDEX. The column names must match the README's exactly, becauseseedfills them by name. - Say which
name. Both tables have anamecolumn, so give the tables aliases,FROM products AS p JOIN vendors AS v, as Joining Tables does, and pickp.nameorv.name. - Every vendor, even with nothing to sell.
product_countsstarts from vendors with aLEFT JOIN, groups by vendor, and countsp.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.
seedindata.pyinserts from a dictionary with named placeholders such as:email;add_vendorcan passvendorthe same way. Catchsqlite3.IntegrityErroraround the insert and raiseDuplicateEmailin 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.
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.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 mainPaste 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.