Chapter 29 — Key Takeaways

The big idea

Applications connect to PostgreSQL as clients (Chapter 2) via a driver — psycopg2 (or psycopg3) in Python. The seam between code and database is where the most dangerous bugs live; getting it right means parameterized queries, transactions, pooling, and a clean data-access layer.

The model

  • Connection = your session; cursor = executes SQL and holds results (execute, fetchone/many/all).
  • Close cursors/connections — use context managers (with) so they close automatically.

The #1 rule: parameterize, never concatenate

  • Always use %s placeholders with values passed separately; never build SQL with f-strings/+/.format() on external input.
  • String-concatenated input → SQL injection: input executed as SQL → auth bypass, data theft, dropped tables. (Case Study 1: ' OR '1'='1 logged in as anyone.)
  • Parameterization makes injection structurally impossible (value travels separately, never parsed as SQL).
  • Parameterize every external value (not just strings). Parameters are for values, not identifiers (table/column names need psycopg2.sql).

Transactions from code

  • Control with commit()/rollback(); prefer a context manager that commits on success, rolls back on exception → atomic multi-step ops (Chapter 26).
  • RETURNING (Ch. 13) gets generated ids inline.

Connection pooling

  • A connection is bounded and expensive; opening one per request exhausts the server (too many clients). Use a pool (psycopg2.pool, framework pool, or PgBouncer) — a small reused set serving many requests. Always return/close connections (even on error) to avoid leaks. (Case Study 2.)

Error handling

  • Catch specific exceptions (UniqueViolation, ForeignKeyViolation, …); retry DeadlockDetected/SerializationFailure (Chapters 26–27). Don't swallow errors silently.

Repository / data-access layer

Gather an entity's queries behind a class (find_by_email, create) — isolates SQL in one place (parameterized, reviewable, testable), gives the app a clean API.

You can now…

  • ☐ Connect from Python and execute/fetch.
  • ☐ Write parameterized queries and explain why they stop injection.
  • ☐ Manage transactions from code for atomicity.
  • ☐ Use a pool and clean up connections; handle/retry errors.
  • ☐ Structure access with the repository pattern.

Looking ahead

Chapter 30 — ORMs & SQLAlchemy. Object-relational mappers that generate SQL from objects — how they help, the N+1 query trap, eager vs. lazy loading, and why an ORM is a convenience for people who know SQL, not a replacement for it.

One sentence to carry forward: Always parameterize (never concatenate) external input, wrap multi-step changes in a transaction, pool your connections and always return them — these four habits make application database access safe and scalable.