Chapter 30 — Exercises

Python + SQLAlchemy against mercado. Enable create_engine(..., echo=True) to see the SQL. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Models & CRUD

30.1 Define ORM models for Customer and Order with a relationship between them. (answer in Appendix)

30.2 Through a Session, fetch a customer by email and print their name.

30.3 Insert a new customer, update a field, and delete a row — committing each. (answer in Appendix)

30.4 ⭐ Explain what change tracking does when you modify an object and commit().

Group B — Relationships & N+1

30.5 Loop over all customers and access customer.orders. With echo=True, count the queries. What pattern do you see? (answer in Appendix)

30.6 Explain the N+1 problem in your own words and why it's invisible in small test data. (answer in Appendix)

30.7 Fix the N+1 using selectinload; confirm the query count drops. Then try joinedload and note the difference.

30.8 ⭐ When would you prefer joinedload (a JOIN) vs selectinload (a second IN query)?

Group C — ORM vs SQL

30.9 Write a complex report (top 10 customers by revenue) as raw SQL via the session. Why is raw SQL reasonable here? (answer in Appendix)

30.10 ⭐ Take a multi-table aggregation you wrote in Part II and attempt it in the ORM, then in raw SQL. Which is clearer?

Group D — Seeing the SQL

30.11 Turn on echo=True and run a few ORM operations. Paste the generated SQL for one and confirm it's parameterized. (answer in Appendix)

30.12 ⭐ Why is "I use an ORM so I don't need SQL" a dangerous belief? Give two concrete failures it leads to.

Group E — Progressive project

30.13 Add ORM models for two related entities in your domain and do CRUD through a Session.

30.14 Trigger and then fix an N+1 in your domain (count the queries before/after).

30.15 ⭐ Write one complex report as raw SQL and explain why the ORM wasn't the right tool.

Self-check. If you can build models, spot an N+1 by watching the query log, fix it with eager loading, and know when to drop to raw SQL, you can use an ORM well — as a convenience on top of SQL you understand. Next: moving data at scale.