Skip to content
Tutorials Center
My learning

Topics

Lesson 63 of 72 ยท Projects

Project: A Mini Bookshop

Build a small Flask online shop with Flask-SQLAlchemy: a book catalog, a session-based cart, an atomic checkout that protects stock, and orders with relationships.

Shops are a classic web-app exercise because they combine nearly everything you have learned: a database with related tables, state that survives between requests, forms, validation and a multi-step operation that must either fully succeed or not happen at all. In this project you build a mini bookshop with a catalog, a shopping cart, a checkout and an order page.

The interesting engineering questions are the ones real shops face. Where does the cart live before the customer logs in? Which price counts, the one on the page when the book was added or the one at checkout? What happens when two customers buy the last copy at the same moment? By the end you will have an answer for each, and a runnable app that proves it.

Forms in this project omit CSRF tokens to keep the code focused on shop logic. A real shop must protect every POST route; Project: Users and Login shows a complete token check you can copy.

The data model

Three tables are enough. A Book has a price and a stock level. An Order represents one checkout. An OrderItem is one line of an order: which book, how many, and at what price. The line is a separate table because an order contains many books (one-to-many), and each line points at one book (many-to-one). Together they form a many-to-many relationship between books and orders, with extra data on the link.

erDiagram
    BOOK ||--o{ ORDER_ITEM : "appears in"
    ORDERS ||--|{ ORDER_ITEM : contains
    BOOK {
        int id
        string title
        string author
        int price_cents
        int stock
    }
    ORDERS {
        int id
        string email
    }
    ORDER_ITEM {
        int id
        int order_id
        int book_id
        int quantity
        int unit_price_cents
    }

Order lines connect orders and books, and store the price paid.

Two design decisions matter here. Money is stored as integer cents. Floating-point numbers cannot represent 0.10 exactly, and rounding errors in a shop are bugs with real consequences. The line stores unit_price_cents, a snapshot of the price at purchase time. If you raise a book's price next month, old orders must still show what the customer paid. The order total is then computed from the lines, so there is one source of truth.

The first example defines the models with Flask-SQLAlchemy 3 in SQLAlchemy 2 style, creates an in-memory database, and builds an order by hand. It also counts SQL statements to show why loading related rows deserves care.

Python Models, relationships and query counts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first order still totals $81.00 even though the book's current price changed to $30.00: the snapshot works. The lazy version runs five statements, because every order triggers a query for its items and each new book triggers another query; selectinload needs only three. This is the N+1 problem. selectinload fetches all related rows in one extra query per relationship level, so the count stays constant no matter how many orders you load (with thousands of orders the lazy version would run thousands of statements) (see Relationships). The last lines show cascade="all, delete-orphan" at work: deleting an order deletes its lines.

The cart lives in the session

Visitors should be able to shop before they sign up, so the cart cannot belong to a user row. It belongs to the browser, in the signed session cookie. Store as little as possible: a mapping from book id to quantity. Do not store titles or prices there. A cookie is under the customer's control (they cannot forge the signature, but the data is readable, and old carts linger), so prices are always read from the database at display and checkout time.

Two Flask details trip people up:

  • JSON keys are strings. The session is serialized as JSON, whose object keys are always strings, so {3: 2} comes back as {"3": 2}. The helpers read_cart and write_cart convert in both directions.
  • Mutating nested data is not detected. session["cart"][3] = 2 changes the dictionary without telling Flask to resend the cookie. Always build a new dictionary and assign it to session["cart"], as write_cart does.

Checkout must be atomic

Checkout is the dangerous part. For each line you must reduce stock, record the line, and finally create the order, and either all of it happens or none of it. Two rules make that safe:

  1. Reduce stock with a conditional UPDATE. The statement UPDATE book SET stock = stock - 2 WHERE id = 1 AND stock >= 2 checks and decrements in a single step inside the database. If the result touched zero rows, the stock was not enough. The alternative, reading book.stock into Python and subtracting, lets two customers both see "1 left" and both buy it.
  2. Use one transaction. Do all the work, then commit() once. On any failure, rollback() undoes the earlier decrements too.
flowchart TD
    A["POST /checkout"] --> B{"Cart empty?"}
    B -- yes --> C["Flash and redirect to catalog"]
    B -- no --> D{"Valid email?"}
    D -- no --> E["400, show cart again"]
    D -- yes --> F["For each line:<br/>UPDATE stock WHERE stock >= qty"]
    F --> G{"Every update<br/>changed a row?"}
    G -- no --> H["ROLLBACK, flash sold-out message"]
    G -- yes --> I["Create order and lines,<br/>COMMIT"]
    I --> J["Clear cart, redirect to order page"]

The conditional update is the stock check; the single commit makes the multi-line purchase all-or-nothing.

The complete shop

The next listing is the whole application. Notice a few choices:

  • catalog supports a ?q= search using Book.title.icontains(q, autoescape=True), which escapes % and _ so user input cannot act as wildcards.
  • cart_add validates the quantity and compares the total wanted (already in the cart plus new) against stock.
  • order_detail only shows orders whose id is in this browser's session, because there are no accounts. Everyone else gets 404, which does not reveal that the order exists. With user accounts you would compare the order's owner instead (see Roles and Permissions).
  • Templates live in a DictLoader, as in the previous projects. A money filter turns cents into dollars.
Python Catalog, cart, checkout and orders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the printed lines against the code. The search for % finds nothing because it is escaped. The cart merges two additions of the same book into one line (quantity 4). Asking for three copies of a book with two in stock is refused before it ever reaches the cart. The customer removed the third book, so checkout buys four copies of book 1 and its stock drops from 5 to 1; the other stock levels are untouched.

The final scenario is the important one. The customer's cart holds book 1 and book 4, and a rival grabs one of book 4's two copies after the cart was filled. At checkout the update for book 1 succeeds and the update for book 4 touches zero rows. The code rolls back, so book 1's stock is exactly what it was before the attempt and no order was created. That is the all-or-nothing behaviour the single transaction buys you.

Where to go from here

  • Payments. Create the order as pending, send the customer to a payment provider, and mark it paid from a webhook. Never trust the browser's claim that it paid.
  • Accounts. Add a user_id to Order, attach the session cart to the user at login, and replace the session order_ids check with an ownership check.
  • Reserved stock. Real shops often hold stock for a few minutes while the customer pays. That needs a reservations table and an expiry job.
  • Migrations. Evolve the schema with Flask-Migrate once the database holds real data.
  • Tests. Everything above runs through the test client, so turning the scenario into pytest functions is mostly copy and paste. See Testing Flask Apps with pytest.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does OrderItem store unit_price_cents instead of reading the price from Book?

    An order records what the customer actually paid. Copying the price at purchase time makes the history independent of later price changes.
  2. 2. What does UPDATE book SET stock = stock - 2 WHERE id = 1 AND stock >= 2 give you that a Python book.stock -= 2 does not?

    The database evaluates the condition and applies the change together. When no row matches, the result reports zero rows and you know the stock was insufficient.
  3. 3. Why does write_cart build a new dictionary instead of changing session["cart"] in place?

    Assigning to session[...] marks the session as modified. Changing a nested object does not, unless you set session.modified = True.
  4. 4. What happens when the second line of a checkout cannot be satisfied?

    All the updates belong to one transaction. rollback() discards the first decrement together with everything else.
  5. 5. Why does order_detail return 404 instead of 403 for someone else's order?

    A 403 confirms the resource exists. For private data, answering 404 leaks less.

Summary

  • Model the shop with Book, Order and OrderItem; store money as integer cents and snapshot the price on each order line.
  • Keep only ids and quantities in the session cart, convert key types, and always assign a new dictionary to session["cart"].
  • Read prices and stock from the database at checkout, never from the cart or the browser.
  • Decrement stock with a conditional UPDATE ... WHERE stock >= qty inside one transaction, and roll back on any failure.
  • Load related rows with selectinload to avoid the N+1 query pattern.
  • Restrict order pages to their owner and answer 404 for everyone else.

Next: Practice Exercises with Solutions, a set of graded tasks covering routing, templates, forms, databases and APIs.