Chapter 29 — Further Reading

Drivers (💻 Developer)

  • psycopg2 docs (https://www.psycopg.org/docs/) — connections, cursors, parameter passing (%s), transactions, the connection pool, error classes. The authoritative reference for this chapter.
  • psycopg 3 (psycopg) docs (https://www.psycopg.org/psycopg3/) — the modern successor: async support, better pooling, similar API. Worth adopting for new projects.
  • DB-API 2.0 (PEP 249) — the Python database API all drivers implement; understanding it transfers across drivers.

SQL injection (everyone — the most important topic here)

  • OWASP: SQL Injection and the OWASP Top Ten — why injection has topped web-vulnerability lists for decades, with examples and prevention (parameterization). Read the prevention cheat sheet.
  • "Bobby Tables" (xkcd.com/327) and explainers — the famous, memorable illustration.
  • psycopg2: "Passing parameters to SQL queries" and psycopg2.sql — the right way to pass values and (carefully) identifiers.

Patterns & pooling (💻 Developer · 🏗️ DBA)

  • The repository pattern / data-access layer — Martin Fowler and many tutorials; isolating SQL for testability and review.
  • PgBouncer (https://www.pgbouncer.org/) — production connection pooling (Chapters 27, 38).
  • "Connection management best practices in PostgreSQL" — pool sizing, max_connections, transaction vs. session pooling.

Reference (this book)

  • Chapter 26–27: transactions, isolation, deadlock/serialization retry — the behavior your code must handle.
  • Chapter 30: SQLAlchemy (ORM) — the next layer up.
  • Chapter 32: Security — injection prevention, roles, encryption (deepens this chapter's #1 rule).

Do, don't just read

  • Build the Case Study 1 vulnerability in a throwaway local script, exploit it with ' OR '1'='1, then fix it with parameterization and watch the exploit fail.
  • Write a parameterized repository for one Mercado entity; review it for injection-safety.
  • Demonstrate atomicity with a context-manager transaction (insert order + item; force an error; confirm rollback).

Next: Chapter 30 — ORMs and SQLAlchemy: object-relational mapping and its trade-offs.