Python - PostgreSQL

Overview

Estimated time: 25–35 minutes

Use psycopg (psycopg3) to connect to PostgreSQL, execute parameterized queries, and manage transactions robustly.

Learning Objectives

• Connect with DSN or env vars and run parameterized queries.
• Use transactions and cursors safely with context managers.
• Handle connection pooling and timeouts.

Examples

# pip install psycopg[binary]
import os
import psycopg

conninfo = os.environ.get("PG_CONN", "dbname=app user=app password=pass host=localhost")
with psycopg.connect(conninfo) as con:
    with con.cursor() as cur:
        cur.execute("CREATE TABLE IF NOT EXISTS users(id serial PRIMARY KEY, name text)")
        cur.execute("INSERT INTO users(name) VALUES (%s) RETURNING id", ("Ada",))
        user_id = cur.fetchone()[0]
        cur.execute("SELECT id, name FROM users WHERE id=%s", (user_id,))
        print(cur.fetchone())

Guidance & Patterns

• Use server-side prepared statements implicitly by reusing query shapes.
• Keep credentials out of source; prefer env vars and secret managers.

Best Practices

• Always parameterize; never format SQL with string interpolation.
• Set statement timeouts; handle integrity errors and retries where appropriate.

Exercises

1. Write a function that upserts a user by name and returns its id.
2. Wrap DB access in a small repository class with context-managed connections.