17. Final project
Put everything you learned in Section 2 into a single program, in Python or in TypeScript, that:
- creates a sandbox and uses the token for every call;
- reads
GET /schemato learn the tables, primary keys and foreign keys, and downloads catalog and authors page by page; - rebuilds the tables in a local SQLite with the same keys (
books.author_id → authors.id); - creates a customer and an order with several lines (a transaction), then moves it to
paid; - produces a report with a local
GROUP BYand compares it with the server’sGET /stats: they must match.
Theory: syncing, not copying by hand
Section titled “Theory: syncing, not copying by hand”Many real applications do exactly this: they read from an API and keep a queryable local copy (cache, reporting, offline apps). The points to watch:
- Complete pagination: follow
links.nextuntil it isnull; stopping at the first page is the most common mistake. - Same keys: keep the remote
ids as local primary keys, so the FKs stay valid and local JOINs work. - Targeted expansion:
?expand=categorygives you the category’sslugin a single call, instead of 80 calls to/categories. - Idempotent sync: running the script again must not duplicate anything (here:
DROP TABLE IF EXISTSand re-creation; in productionINSERT … ON CONFLICT DO UPDATE). - Verification: a number computed locally compared with the same number computed by the server is the simplest and most effective test.
curl, explained option by option
Section titled “curl, explained option by option”Before writing code, the sequence by hand, to see every step:
BASE=https://corso-api-json.giuseppebosi.com/api/v1TOKEN=$(curl -s -X POST $BASE/sandbox | python3 -c 'import sys, json; print(json.load(sys.stdin)["token"])')AUTH="Authorization: Bearer $TOKEN"
# 1. lo schema: quali tabelle, quante righe, quali chiavicurl -s $BASE/schema -H "$AUTH" | python3 -c 'import sys, jsonfor nome, t in json.load(sys.stdin)["tables"].items(): fk = ", ".join(c["column"] + "→" + c["references"]["table"] for c in t["foreign_keys"]) print("%-12s %4d righe PK %s FK %s" % (nome, t["rows"], t["primary_key"], fk))'
# 2. tutte le pagine del catalogo, con la categoria espansafor page in 1 2; docurl -s "$BASE/books?expand=category&limit=50&page=$page" -H "$AUTH" | python3 -c 'import sys, jsond = json.load(sys.stdin); print("pagina %d/%d: %d libri, next=%s" % (d["meta"]["page"], d["meta"]["pages"], len(d["items"]), d["links"]["next"]))'done- BASE=… / AUTH=…
- shell variables so you do not repeat URL and header;
$BASEand"$AUTH"get substituted - # …
- shell comments: ignored
- python3 -c '…' over several lines
- a small Python program passed as a string: it reads the JSON and prints one line per table
- "%-12s %4d" % (…)
%formatting, valid in every Python 3 version:%-12s= text 12 wide, left-aligned;%4d= integer 4 wide- for page in 1 2
- two pages of 50 cover the 80 books; in the real script the loop stops when
links.nextisnull
authors 20 righe PK ['id'] FKbook_tags 126 righe PK ['book_id', 'tag_id'] FK tag_id→tags, book_id→booksbooks 80 righe PK ['id'] FK category_id→categories, author_id→authorscategories 10 righe PK ['id'] FK parent_id→categoriescustomers 40 righe PK ['id'] FKorder_items 305 righe PK ['id'] FK book_id→books, order_id→ordersorders 120 righe PK ['id'] FK customer_id→customersreviews 243 righe PK ['id'] FK customer_id→customers, book_id→bookstags 10 righe PK ['id'] FKpagina 1/2: 50 libri, next=https://…/books?expand=category&limit=50&page=2pagina 2/2: 30 libri, next=NoneThen customer, order, payment and comparison — exactly what the script automates:
curl -s -X POST $BASE/customers -H "$AUTH" -H 'Content-Type: application/json' \ -d '{"name": "Mario Verdi", "email": "mario.verdi@example.com", "city": "Bologna"}'# → {"id":41,"name":"Mario Verdi",…}curl -s -X POST $BASE/orders -H "$AUTH" -H 'Content-Type: application/json' \ -d '{"customer_id": 41, "items": [{"book_id": 12, "quantity": 1}, {"book_id": 3, "quantity": 2}], "shipping_address": {"street": "Via Zamboni 7", "city": "Bologna", "zip": "40126"}}'# → {"id":121,"status":"pending","total":96.81,"items":[…]}curl -s -X PATCH $BASE/orders/121 -H "$AUTH" -H 'Content-Type: application/json' -d '{"status": "paid"}'# → {"id":121,"status":"paid",…}curl -s $BASE/stats | python3 -c 'import sys, json; [print(c) for c in json.load(sys.stdin)["books_by_category"]]'curl -s -X DELETE $BASE/sandbox -H "$AUTH" # pulizia: 204Python and TypeScript: the complete program
Section titled “Python and TypeScript: the complete program”The same program in both languages; the output follows the code.
# pip install httpx → python3 progetto.py"""Progetto finale: sincronizza il catalogo in un SQLite locale, crea un ordine in sandbox,produce un report con JOIN locali e lo confronta con /stats."""import sqlite3
import httpx
BASE = "https://corso-api-json.giuseppebosi.com/api/v1"
# (a) sandboxtoken = httpx.post(f"{BASE}/sandbox").json()["token"]api = httpx.Client(base_url=BASE, headers={"Authorization": f"Bearer {token}"}, timeout=15)
# (b) schema e catalogoschema = api.get("/schema").json()["tables"]print("tabelle remote:", ", ".join(f"{t} ({v['rows']})" for t, v in schema.items()))
def tutte(path, **params): """Generatore: scorre tutte le pagine finché links.next è null.""" page = 1 while True: d = api.get(path, params={**params, "page": page, "limit": 100}).json() yield from d["items"] if not d["links"]["next"]: return page += 1
# (c) SQLite locale con le stesse chiavidb = sqlite3.connect("libreria_locale.db")db.executescript("""DROP TABLE IF EXISTS books; DROP TABLE IF EXISTS authors;CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT, country TEXT);CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL, category TEXT, author_id INTEGER REFERENCES authors(id));""")db.executemany("INSERT INTO authors VALUES (?,?,?)", [(a["id"], a["name"], a["country"]) for a in tutte("/authors")])db.executemany("INSERT INTO books VALUES (?,?,?,?,?)", [(b["id"], b["title"], b["price"], b["category"]["slug"], b["author_id"]) for b in tutte("/books", expand="category")])print("libri locali:", db.execute("SELECT COUNT(*) FROM books").fetchone()[0])
# (d) un cliente e un ordine, poi pagamentocliente = api.post("/customers", json={"name": "Mario Verdi", "email": "mario.verdi@example.com", "city": "Bologna"}).json()ordine = api.post("/orders", json={"customer_id": cliente["id"], "items": [{"book_id": 12, "quantity": 1}, {"book_id": 3, "quantity": 2}], "shipping_address": {"street": "Via Zamboni 7", "city": "Bologna", "zip": "40126"}}).json()ordine = api.patch(f"/orders/{ordine['id']}", json={"status": "paid"}).json()print(f"ordine {ordine['id']}: {ordine['status']}, totale {ordine['total']} €")
# (e) report: JOIN locale vs aggregazione remotaprint("\nLibri e prezzo medio per categoria (JOIN locale):")locale = {r[0]: (r[1], r[2]) for r in db.execute( "SELECT category, COUNT(*), ROUND(AVG(price),2) FROM books GROUP BY category ORDER BY 2 DESC")}remoto = {r["slug"]: (r["books"], r["avg_price"]) for r in api.get("/stats").json()["books_by_category"]}for cat, (n, media) in locale.items(): print(f" {cat:<13} {n:>2} libri media {media:>6.2f} € remoto: {remoto[cat]} {'OK' if remoto[cat] == (n, media) else 'DIVERSO'}")api.delete("/sandbox")What you should see:
tabelle remote: authors (20), book_tags (126), books (80), categories (10), customers (40), order_items (305), orders (120), reviews (243), tags (10)libri locali: 80ordine 121: paid, totale 96.81 €
Libri e prezzo medio per categoria (JOIN locale): saggistica 14 libri media 17.04 € remoto: (14, 17.04) OK ragazzi 12 libri media 16.72 € remoto: (12, 16.72) OK cucina 9 libri media 18.39 € remoto: (9, 18.39) OK narrativa 8 libri media 22.78 € remoto: (8, 22.78) OK storici 7 libri media 19.96 € remoto: (7, 19.96) OK storia 7 libri media 24.31 € remoto: (7, 24.31) OK informatica 7 libri media 19.11 € remoto: (7, 19.11) OK scienza 6 libri media 22.11 € remoto: (6, 22.11) OK fantascienza 6 libri media 27.67 € remoto: (6, 27.67) OK gialli 4 libri media 15.70 € remoto: (4, 15.7) OK// node --experimental-strip-types --experimental-sqlite progetto.ts (Node 22.5+; package.json con "type": "module")import { DatabaseSync } from "node:sqlite";
const BASE = "https://corso-api-json.giuseppebosi.com/api/v1";
// (a) sandboxconst { token } = await (await fetch(`${BASE}/sandbox`, { method: "POST" })).json();const api = async (method: string, path: string, body?: unknown) => { const r = await fetch(BASE + path, { method, headers: { Authorization: `Bearer ${token}`, "Content-Type": "application/json" }, body: body === undefined ? undefined : JSON.stringify(body) }); if (!r.ok && r.status !== 204) throw new Error(`${method} ${path}: ${r.status} ${await r.text()}`); return r.status === 204 ? null : r.json();};
// (b) schema e catalogoconst schema = (await api("GET", "/schema")).tables;console.log("tabelle remote:", Object.entries(schema).map(([t, v]: any) => `${t} (${v.rows})`).join(", "));async function tutte(path: string, params: Record<string, string> = {}) { const out: any[] = []; for (let page = 1; ; page++) { // finché links.next è null const d = await api("GET", `${path}?${new URLSearchParams({ ...params, page: String(page), limit: "100" })}`); out.push(...d.items); if (!d.links.next) return out; }}
// (c) SQLite locale con le stesse chiaviconst db = new DatabaseSync("libreria_locale.db");db.exec(`DROP TABLE IF EXISTS books; DROP TABLE IF EXISTS authors; CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT, country TEXT); CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL, category TEXT, author_id INTEGER REFERENCES authors(id));`);const insA = db.prepare("INSERT INTO authors VALUES (?,?,?)");for (const a of await tutte("/authors")) insA.run(a.id, a.name, a.country);const insB = db.prepare("INSERT INTO books VALUES (?,?,?,?,?)");for (const b of await tutte("/books", { expand: "category" })) insB.run(b.id, b.title, b.price, b.category.slug, b.author_id);console.log("libri locali:", (db.prepare("SELECT COUNT(*) AS n FROM books").get() as any).n);
// (d) un cliente e un ordine, poi pagamentoconst cliente = await api("POST", "/customers", { name: "Mario Verdi", email: "mario.verdi@example.com", city: "Bologna" });let ordine = await api("POST", "/orders", { customer_id: cliente.id, items: [{ book_id: 12, quantity: 1 }, { book_id: 3, quantity: 2 }], shipping_address: { street: "Via Zamboni 7", city: "Bologna", zip: "40126" } });ordine = await api("PATCH", `/orders/${ordine.id}`, { status: "paid" });console.log(`ordine ${ordine.id}: ${ordine.status}, totale ${ordine.total} €`);
// (e) report: JOIN locale vs aggregazione remotaconsole.log("\nLibri e prezzo medio per categoria (JOIN locale):");const remoto = new Map<string, [number, number]>((await api("GET", "/stats")).books_by_category.map((r: any) => [r.slug, [r.books, r.avg_price]]));for (const r of db.prepare("SELECT category, COUNT(*) AS n, ROUND(AVG(price),2) AS media FROM books GROUP BY category ORDER BY n DESC").all() as any[]) { const [n, media] = remoto.get(r.category)!; console.log(` ${r.category.padEnd(13)} ${String(r.n).padStart(2)} libri media ${r.media.toFixed(2).padStart(6)} € remoto: (${n}, ${media}) ${n === r.n && media === r.media ? "OK" : "DIVERSO"}`);}await api("DELETE", "/sandbox");What you should see:
tabelle remote: authors (20), book_tags (126), books (80), categories (10), customers (40), order_items (305), orders (120), reviews (243), tags (10)libri locali: 80ordine 121: paid, totale 96.81 €
Libri e prezzo medio per categoria (JOIN locale): saggistica 14 libri media 17.04 € remoto: (14, 17.04) OK ragazzi 12 libri media 16.72 € remoto: (12, 16.72) OK cucina 9 libri media 18.39 € remoto: (9, 18.39) OK narrativa 8 libri media 22.78 € remoto: (8, 22.78) OK storici 7 libri media 19.96 € remoto: (7, 19.96) OK storia 7 libri media 24.31 € remoto: (7, 24.31) OK informatica 7 libri media 19.11 € remoto: (7, 19.11) OK scienza 6 libri media 22.11 € remoto: (6, 22.11) OK fantascienza 6 libri media 27.67 € remoto: (6, 27.67) OK gialli 4 libri media 15.70 € remoto: (4, 15.7) OKTen OKs: your local database and the server tell the same story.
Try it now
Section titled “Try it now”The pieces of the project, one by one:
Response headers
Response headers
Response headers
Response headers
Response headers
Response headers
Extensions (pick one)
Section titled “Extensions (pick one)”Try it online, nothing to install:
The GET request straight in your browser: https://corso-api-json.giuseppebosi.com/api/v1/schema
Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.
- Incremental sync: add
ordersandorder_itemsto the local database and, with a localJOINacross four tables, reproduceGET /stats/sales?group_by=city. Compare. - Robustness: replace the direct calls with chapter 16’s
LibreriaClient(timeouts, retries, types). - Caching: keep the
ETags of/books/{id}and, on a second run, download again only the books that changed (chapter 14). - CLI: turn the script into a command with arguments (
--citta Bologna --libri 12,3) usingargparseornode:util.parseArgs.
Outline for extension 1
Local tables customers(id, name, city), orders(id, customer_id, status, created_at), order_items(order_id, book_id, quantity, unit_price). Then:
SELECT cu.city, COUNT(DISTINCT o.id) AS orders, SUM(oi.quantity) AS books_sold, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenueFROM orders o JOIN customers cu ON cu.id = o.customer_idJOIN order_items oi ON oi.order_id = o.idWHERE o.status != 'cancelled' GROUP BY cu.city ORDER BY cu.cityRemember that your sandbox also contains the order you created: to compare with the public dataset, call /stats/sales without a token.
What you can do now
Section titled “What you can do now”You have gone through Section 2 from a GET with curl to a program that talks to an API across several tables linked by keys, handles transactions, errors, caching and limits, and checks its own work. The same ideas — resources, query strings, expand, sandboxes, 409 and 422, ETags, Retry-After, JOINs and aggregations — you will find again, unchanged, in every serious API you meet: the names change, not the substance.
Useful references to carry on: all the endpoints, the dataset, curl cheatsheet, glossary.