Connect from Python

Use psycopg with PLOMID — connect, parameterized queries, transactions, and cleanup.

Version
Latest
v0.1.0 · latest 1 min read
On this page
  1. Prerequisites
  2. Complete workflow
  3. Transactions and errors
  4. Pooling
  5. Vector queries

Status: Supported with psycopg 3 (verified end-to-end, including parameterized vector queries). psycopg2 uses the same wire path.

Prerequisites#

shsource
pip install "psycopg[binary]"

Server running locally with user plomid (see Quickstart). Credentials below come from environment variables — never hard-code them.

Complete workflow#

pythonsource
import os
import psycopg
 
conn = psycopg.connect(
    host=os.environ.get("PLOMID_HOST", "127.0.0.1"),
    port=int(os.environ.get("PLOMID_PORT", "5432")),
    dbname=os.environ.get("PLOMID_DB", "plomid"),
    user=os.environ["PLOMID_USER"],
    password=os.environ["PLOMID_PASSWORD"],
)
try:
    with conn.cursor() as cur:
        # Parameterized query — always use parameters, never f-strings.
        cur.execute("SELECT id, name FROM users WHERE id = %s;", (1,))
        print(cur.fetchone())
 
        # Write + transaction: commit on success, rollback on error.
        try:
            cur.execute(
                "INSERT INTO users (id, name) VALUES (%s, %s);",
                (3, "hedy"),
            )
            conn.commit()
        except Exception:
            conn.rollback()
            raise
finally:
    conn.close()

Transactions and errors#

  • psycopg opens a transaction implicitly: every write block ends with conn.commit() or conn.rollback(). A failed statement aborts the transaction — roll back before continuing.
  • Retry only on safe signals (connection loss, serialization-style conflicts after rollback). Never blindly retry non-idempotent writes; make them idempotent first (ON CONFLICT DO NOTHING, natural keys).
  • CREATE/DROP DATABASE cannot run inside the implicit transaction — set autocommit=True for those statements only.

Pooling#

pythonsource
from psycopg_pool import ConnectionPool
 
pool = ConnectionPool(
    "host=127.0.0.1 port=5432 dbname=plomid user=plomid password=secret",
    min_size=2,
    max_size=10,
)
try:
    with pool.connection() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT count(*) FROM users;")
            print(cur.fetchone())
finally:
    pool.close()

Size pools to concurrent active writers (the engine serializes mutations) — see Pooling and errors. Statement and idle-in-transaction timeouts bound each checkout; always return connections in finally.

Vector queries#

pythonsource
with conn.cursor() as cur:
    q = "[1.0, 0.0, 0.0]"
    cur.execute(
        "SELECT id FROM docs ORDER BY embedding <-> %s LIMIT %s;",
        (q, 5),
    )
    print(cur.fetchall())

Vector parameters are text ("[1.0, 0.0, 0.0]"). More patterns: Vector application integration.

Next: Pooling and errors · Build your first application.

Was this page helpful?