Python: psycopg

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DATABASE_URL=postgresql://app:${DB_PASSWORD}@old-postgres:5432/app
DATABASE_URL=postgresql://app:${DB_PASSWORD}@machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Python 3.15
• psycopg 3.3.6

Run

python3 -m venv .venv && . .venv/bin/activate
pip install -r requirements.txt
export DATABASE_URL=postgresql://app:${DB_PASSWORD}@machsql-host:5432/app
python main.py

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.py

"""psycopg 3: plain SQL over the PostgreSQL protocol."""

import os

import psycopg

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)")
    (todo_id,) = conn.execute(
        "INSERT INTO todo (title) VALUES (%s) RETURNING id", ("write the examples",)
    ).fetchone()
    (open_count,) = conn.execute("SELECT count(*) FROM todo WHERE NOT done").fetchone()
    print(f"inserted todo {todo_id}, {open_count} open")

requirements.txt

psycopg[binary]==3.3.6