Psycopg C-optimized

Warnings

• breaking Migration from Psycopg 2 to Psycopg 3 involves significant breaking changes. Key areas include connection context managers (now close the connection by default), connection string format (`postgresql+psycopg://`), updated `COPY` API, and a completely redesigned asynchronous API.
  Fix: Consult the 'Differences from psycopg2' section in the official Psycopg 3 documentation for a comprehensive migration guide.
• gotcha Direct installation of `psycopg-c` using `pip install psycopg-c` is discouraged and may lead to issues. It should be installed as an extra feature of the main `psycopg` package using `pip install "psycopg[c]"` to ensure version compatibility and correct integration.
  Fix: Always install `psycopg-c` via the `psycopg` package: `pip install "psycopg[c]"`.
• gotcha The `psycopg[c]` installation requires local build tools (a C compiler, Python development headers, and PostgreSQL's `libpq` development headers). If these prerequisites are not met, the installation will fail with compilation errors. `psycopg[binary]` is an alternative that includes pre-compiled C extensions.
  Fix: Ensure all build prerequisites are installed on your system, or use `pip install "psycopg[binary]"` for a pre-compiled version if you cannot meet the build requirements.
• gotcha Asynchronous connections (`psycopg.AsyncConnection`) require careful use of `await`. While `async with` is used, the `connect()` method itself is an `async` factory, leading to the pattern `async with await psycopg.AsyncConnection.connect()` which can be a source of confusion.
  Fix: Remember the double `await` pattern: `async with await psycopg.AsyncConnection.connect() as aconn:`
• gotcha By default, `psycopg` starts a new transaction with each `execute()` call. Changes are not persisted until `conn.commit()` is explicitly called. If `commit()` is forgotten, changes will be discarded when the connection closes. Use context managers (`with conn:`) for explicit transaction management.
  Fix: Always ensure `conn.commit()` is called after successful data modifications, or wrap your operations in a `with conn:` block to ensure transactions are handled correctly (committed on success, rolled back on error). For explicit transaction blocks, use `with conn.transaction():`.
• gotcha Psycopg 3 primarily uses server-side parameter binding, which is more secure but has limitations. It does not work with all SQL statements (e.g., `SET`, `NOTIFY`) or when executing multiple SQL statements in a single `execute()` call if parameters are passed.
  Fix: Avoid using parameterized queries for commands like `SET` or `NOTIFY`. For multiple statements, execute them separately or ensure no parameters are passed when using a single `execute()` call. Be aware of these limitations if you encounter `SyntaxError` with server-side binding.

Install

• pip install "psycopg[c]" Recommended installation with C optimizations
• pip install psycopg Pure Python core package (no C optimizations)
• pip install "psycopg[binary]" Pre-compiled binary package (no build tools needed)

Imports

• connect

  import psycopg
  conn = psycopg.connect(...)

  The primary interaction is through the `psycopg` module; `psycopg-c` provides the underlying C extension.
• AsyncConnection

  from psycopg import AsyncConnection
  async with await AsyncConnection.connect(...) as aconn:

  `AsyncConnection.connect()` is an `async` factory method that returns an awaitable object, hence the `await` after `async with`.

Quickstart

This quickstart demonstrates a basic synchronous connection to a PostgreSQL database, creating a table, inserting a record, and querying it using `psycopg.connect` and context managers for connection and cursor. It uses environment variables for connection parameters for security and flexibility.

import psycopg
import os

# Ensure environment variables are set for connection details
DB_HOST = os.environ.get('PG_HOST', 'localhost')
DB_PORT = os.environ.get('PG_PORT', '5432')
DB_NAME = os.environ.get('PG_DATABASE', 'testdb')
DB_USER = os.environ.get('PG_USER', 'user')
DB_PASSWORD = os.environ.get('PG_PASSWORD', 'password')

conninfo = f"host={DB_HOST} port={DB_PORT} dbname={DB_NAME} user={DB_USER} password={DB_PASSWORD}"

try:
    with psycopg.connect(conninfo) as conn:
        with conn.cursor() as cur:
            # Create a table
            cur.execute("""
                CREATE TABLE IF NOT EXISTS my_data (
                    id SERIAL PRIMARY KEY,
                    value TEXT
                )
            """)

            # Insert data
            cur.execute("INSERT INTO my_data (value) VALUES (%s)", ("Hello Psycopg C!",))
            conn.commit()

            # Query data
            cur.execute("SELECT id, value FROM my_data ORDER BY id DESC LIMIT 1")
            record = cur.fetchone()
            print(f"Inserted and retrieved: {record}")

except psycopg.Error as e:
    print(f"Database error: {e}")
    # In a real application, you might want to rollback on error
    # if 'conn' is available and not in autocommit mode.
except Exception as e:
    print(f"An unexpected error occurred: {e}")