Psycopg Synchronous Connection

Demonstrate a synchronous PostgreSQL workflow with SQLSpec’s Psycopg adapter. Update the DSN via SQLSPEC_PSYCOPG_DSN to point at your database.

SQLSPEC_PSYCOPG_DSN=postgresql://user:pass@host/db \
  uv run python docs/examples/adapters/psycopg/connect_sync.py

Source

"""Psycopg configuration for synchronous SQLSpec sessions."""

import os

from sqlspec.adapters.psycopg import PsycopgSyncConfig
from sqlspec.core import SQL

__all__ = ("main",)


DSN = os.getenv("SQLSPEC_PSYCOPG_DSN", "postgresql://postgres:postgres@localhost:5432/postgres")
config = PsycopgSyncConfig(bind_key="docs_psycopg", pool_config={"conninfo": DSN, "min_size": 1, "max_size": 4})


def main() -> None:
    """Open a Psycopg session and fetch version metadata."""
    with config.provide_session() as session:
        result = session.execute(SQL("SELECT version() AS version"))
        row = result.one_or_none()
        if row:
            print({"adapter": "psycopg", "version": row["version"]})
        else:
            print({"adapter": "psycopg", "version": "unknown"})


if __name__ == "__main__":
    main()