Python, properly · Chapter 40 of 42

Databases from Python

By the end of this chapter you can store and query data from Python in SQLite and in Postgres, pass values to SQL safely with parameters, see an SQL injection happen and stop it, and make several changes count as one with a transaction. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Real-world Python
  • 4 tasks
  • Python
  • In the all labs plan

The first 2 chapters of Python, properly are free. This one comes with the all labs plan.

Why you need this

DabbaDrop's data lives in CSV exports and JSON files. Every script reads the whole file, nobody else sees its changes, and two scripts writing at once overwrite each other. Farah wants one place for the truth: the website, Bhavna's screen and Rafiq's reports reading and writing the same data at the same time. That is a database.

You can already read files and clean data (chapters 17 and 18). What you need now is to talk to a database from Python: send it SQL, give it values without letting them break the SQL, and make sure a half-finished change never lands. The Master Postgres lab teaches SQL itself; this chapter is about the Python side.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    menu_db.py: the menu in SQLite

    Bhavna wants the menu in a database the kitchen tools can query. build_menu(db_path) reads /workspace/data/menu.json and writes it into the SQLite file db_path as the table dishes (id TEXT PRIMARY KEY, name TEXT, category TEXT, price INTEGER, available INTEGER), with available as 1 or 0. It starts the table afresh, so running it twice still leaves one row per dish, and returns the number of rows in dishes.

    cheap_veg(db_path, max_price) returns (name, price) tuples for the available veg dishes costing max_price or less, cheapest first, then by name, with max_price passed as a parameter: cheap_veg(db, 40) returns [('Roti', 15), ('Butter Naan', 40), ('Masala Chaas', 40)].

  2. Task 2

    pg_orders.py: September's orders in Postgres

    Rafiq wants September's orders in Postgres. load_orders(conninfo) connects with psycopg, creates the table orders (order_id text PRIMARY KEY, customer_id text, total integer, status text, payment text) if it is not there, and inserts every order from /workspace/data/orders.csv cleaned: the total as whole rupees (Rs 240 and ₹240 are 240), the status in lowercase, and each order once (D0101 is in the file twice). It returns how many orders it inserted.

    revenue_by_payment(conninfo) returns a dict with, for each payment method, the sum of the totals of the delivered orders only. The check runs both on a fresh database of its own and reads the table back.

  3. Task 3

    stock.py: all or nothing

    The kitchen has two tables (reset_stock in the file makes them): stock (dish_id, left_count), where a CHECK keeps left_count from going below 0, and kitchen_orders (id, dish_id, qty).

    order_dish(conninfo, dish_id, qty) inserts a row into kitchen_orders, then takes qty from that dish's left_count, and returns the new order's id. Both happen or neither does: when there is not enough left, or the dish is not in stock at all, raise OutOfStock (already in the file) and leave both tables exactly as they were.

    The check orders on a fresh database: 3 of 4, then 2 of the 1 left, a dish not in stock, and all 10 of another.

  4. Task 4 · interview question

    Interview: find_customer.py, the injection

    find_by_name(conn, name) returns the customers with exactly that name from an SQLite customers table, as (customer_id, name) tuples ordered by customer_id. It works for Tara Shetty, but it crashes on Neel D'Souza, and the name x' OR '1'='1 returns every customer.

    Fix it so that any name, whatever it contains, is only ever a name. The check runs it on the 61 real customers (Pari Pawar signed up twice) and on three attacks, which must find nobody.

What you will learn

  • The DB-API: connect, execute, fetchone and fetchall, commit, the same shape in sqlite3 and psycopg.
  • Parameters: ? and %s placeholders, executemany, and why string formatting is never the answer.
  • SQL injection: what it is, seen happening, and how parameters stop it.
  • Transactions: commit, rollback, all or nothing.

More in Real-world Python

Build "Databases from Python" yourself

DabbaDrop, a Mumbai tiffin startup, runs on Excel and WhatsApp. You are its first Python developer. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Python, properly are free. This one comes with the all labs plan.