pg8000: Pure-Python PostgreSQL Driver

Warnings

• breaking A SQL injection vulnerability (CVE-2025-61385) was found in `pg8000.native.literal` that allowed remote attackers to execute arbitrary SQL commands via a specially crafted Python list input.
  Fix: Upgrade to pg8000 version 1.31.5 or newer.
• deprecated The `pg8000.DBAPI` module and types like `pg8000.types.Bytea` were deprecated in favor of direct `pg8000.connect` and Python's native `bytes` type.
  Fix: Use `import pg8000.dbapi; pg8000.dbapi.connect()` for DB-API 2.0 or `pg8000.native.Connection()` for the native API. Use Python's `bytes` type directly for binary data.
• gotcha In autocommit mode, `cursor.fetchall()` might fail with 'portal does not exist' for result sets larger than the internal cache (default 100 rows) because the database portal is closed prematurely.
  Fix: Explicitly manage transactions by calling `conn.commit()` or `conn.rollback()` as per DB-API 2.0, or use server-side cursors for very large result sets. Avoid relying solely on autocommit for large fetches.
• gotcha Older versions of pg8000 could suffer from memory leaks due to an unbounded cache of prepared statements, especially when executing many unique queries or frequent DDL operations.
  Fix: Upgrade to recent versions of pg8000. Avoid constructing SQL queries by directly embedding arguments; always use parameterized queries. Close connections when no longer needed.
• gotcha DB-API 2.0 mandates implicit transactions for DML/DDL statements. Changes will not persist until `conn.commit()` is explicitly called, unless the `autocommit` connection parameter is set to `True`.
  Fix: Always call `conn.commit()` after operations that modify the database, or configure `autocommit=True` during connection if that behavior is desired.
• gotcha Network-related connection issues will raise an `InterfaceError` with the message 'network error', rather than exposing the underlying socket/OS exception.
  Fix: Catch `pg8000.dbapi.InterfaceError` specifically when handling potential network connectivity problems.

Install

• pip install pg8000 Install pg8000

Imports

• connect

  import pg8000.dbapi
  conn = pg8000.dbapi.connect(...)

  The `DBAPI` submodule was deprecated in v1.9.0; `pg8000.dbapi.connect` is the standard DB-API 2.0 entry point.
• Connection (Native API)

  import pg8000.native
  conn = pg8000.native.Connection(...)

  For using pg8000's native API, which offers a more streamlined interface.
• Bytea (type)

  value = b'binary_data'

  The `pg8000.types.Bytea` type was deprecated in v1.9.0 in favor of Python's built-in `bytes` type.

Quickstart

This quickstart demonstrates how to establish a connection using the DB-API 2.0 compliant interface, perform DDL and DML operations with parameterized queries, and fetch results. It also includes basic error handling and uses environment variables for secure credential management.

import pg8000.dbapi
import os

# Database connection details from environment variables
DB_USER = os.environ.get("PG_USER", "postgres")
DB_PASSWORD = os.environ.get("PG_PASSWORD", "password")
DB_HOST = os.environ.get("PG_HOST", "localhost")
DB_PORT = int(os.environ.get("PG_PORT", "5432"))
DB_DATABASE = os.environ.get("PG_DATABASE", "testdb")

conn = None
try:
    # Establish a DB-API 2.0 compliant connection
    conn = pg8000.dbapi.connect(
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        port=DB_PORT,
        database=DB_DATABASE
    )
    cursor = conn.cursor()

    # Create a table (if it doesn't exist)
    cursor.execute("CREATE TABLE IF NOT EXISTS mytable (id SERIAL PRIMARY KEY, name VARCHAR(100))")
    conn.commit() # Commit the DDL operation

    # Insert data using a parameterized query
    cursor.execute("INSERT INTO mytable (name) VALUES (%s)", ("pg8000_test",))
    conn.commit() # Commit the DML operation

    # Query data
    cursor.execute("SELECT id, name FROM mytable WHERE name = %s", ("pg8000_test",))
    result = cursor.fetchone()
    print(f"Fetched: {result}")

except pg8000.dbapi.Error as e:
    print(f"Database error: {e}")
    if conn:
        conn.rollback() # Rollback on database errors
except Exception as e:
    print(f"An unexpected error occurred: {e}")
finally:
    if conn:
        conn.close() # Ensure connection is closed