Server: Python client

Prerequisites

A running server, and pip install "psycopg[binary]".

Connect and query

import psycopg

# Context managers close the connection/cursor cleanly.
with psycopg.connect(
    "host=127.0.0.1 port=5433 user=postgres dbname=main sslmode=disable"
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, body FROM notes ORDER BY id DESC LIMIT 50")
        for row in cur.fetchall():
            print(row)
    # exiting the `with conn` block commits

Breakdown: sslmode=disable matches the cleartext listener. The with blocks handle resource cleanup and the outer one commits on a clean exit — psycopg opens an implicit transaction, which the engine runs for real.

Parameter binding

Use %s placeholders with a tuple — this is psycopg's server-side binding, not Python string formatting. Never build SQL with f-strings.

with conn.cursor() as cur:
    cur.execute("SELECT id, body FROM notes WHERE id = %s", (1,))
    note = cur.fetchone()

    cur.execute(
        "INSERT INTO notes (body, ts) VALUES (%s, %s)",
        ("hello", "2026-06-26T00:00:00Z"),
    )
conn.commit()

Breakdown: %s is psycopg's placeholder regardless of the value type; the driver sends them as bind parameters over the extended protocol. The trailing comma in (1,) makes it a one-element tuple.

Transactions

with psycopg.connect("host=127.0.0.1 port=5433 user=postgres dbname=main sslmode=disable") as conn:
    try:
        with conn.cursor() as cur:
            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
        conn.commit()   # blocks until the engine's WAL is durable
    except psycopg.errors.SerializationFailure:
        conn.rollback()  # another writer won the race — safe to retry

Breakdown: a write conflict surfaces as a serialization failure; rolling back and retrying the whole transaction is the correct response, because the engine enforces snapshot isolation with first-committer-wins.

Pooling

from psycopg_pool import ConnectionPool

pool = ConnectionPool("host=127.0.0.1 port=5433 user=postgres dbname=main sslmode=disable", max_size=10)
with pool.connection() as conn, conn.cursor() as cur:
    cur.execute("SELECT count(*) FROM notes")
    print(cur.fetchone())

For serverless bursts, add a transaction-mode pooler in front of the server — see Connection pooling.

Next