Chapter 29 — Exercises

Python + psycopg2 against mercado. (pip install psycopg2-binary.) (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Connect & query

29.1 Write a Python script that connects to mercado and prints all products over $200 (name, price). Close the cursor and connection. (answer in Appendix)

29.2 Rewrite 29.1 using with context managers so resources close automatically.

29.3 Use fetchone, fetchmany(3), and fetchall and describe when you'd use each. (answer in Appendix)

Group B — Parameterized queries (critical)

29.4 Write a parameterized query that finds a customer by an email passed as a variable. Why %s and not an f-string? (answer in Appendix)

29.5 Show the wrong (string-concatenation) version and explain exactly how '; DROP TABLE products; --' would exploit it. (answer in Appendix)

29.6 Parameterize a query with two values (customer_id and status), once by position and once by name.

29.7 ⭐ Parameters are for values, not identifiers. Why can't you parameterize a table or column name with %s, and what's the safe approach (hint: psycopg2.sql)?

Group C — Transactions

29.8 Write a transaction (with a context manager) that inserts an order and an order_item atomically, using RETURNING to get the new order_id. (answer in Appendix)

29.9 Demonstrate rollback: force an error after the first insert and confirm neither row persists.

29.10 ⭐ Why does psycopg2 roll back uncommitted work when a connection closes without commit()?

Group D — Pooling & errors

29.11 Use psycopg2.pool.SimpleConnectionPool to borrow/return a connection. Why return instead of close? (answer in Appendix)

29.12 Catch a UniqueViolation when inserting a duplicate email and return a friendly message.

29.13 ⭐ Which database errors should the application retry rather than report? (Tie to Chapters 26–27.)

Group E — Repository pattern

29.14 Write a CustomerRepository with find_by_email and create methods — all parameterized. (answer in Appendix)

29.15 ⭐ Why does isolating SQL in a repository help with both maintainability and injection-safety review?

Group F — Progressive project

29.16 Connect to your project DB from Python and run a parameterized query.

29.17 Build a repository for one entity (find/create/update), all parameterized; wrap a multi-step op in a transaction.

29.18 ⭐ Add error handling for a likely constraint violation and (stretch) a connection pool.

Self-check. If every value from outside your code goes into a parameter (never the SQL string), your multi-step ops are transactional, and your SQL lives in a repository — you're integrating safely. Next: ORMs.