psycopg2-pool

1.2 · active · verified Thu Apr 16

psycopg2-pool provides robust connection pooling for the psycopg2 PostgreSQL adapter. It helps manage a fixed number of database connections, improving performance by reusing existing connections and reducing the overhead of establishing new ones. It currently stands at version 1.2 and is a stable library with a low release cadence.

Common errors

```
psycopg2.OperationalError: no more connections in the pool
```
cause All available connections in the pool are currently in use and have not been returned.

fix Ensure all connections obtained with `pool.getconn()` are returned to the pool using `pool.putconn(conn)` (preferably via a `with` statement for automatic handling) or increase `maxconn` if your workload truly requires more concurrent connections.
```
AttributeError: 'NoneType' object has no attribute 'cursor'
```
cause Attempting to use a connection object that is `None` because `getconn()` failed, or after the connection has been returned to the pool and potentially invalidated or replaced.

fix Verify that `pool.getconn()` successfully returned a connection before trying to use it. If explicitly managing connections, ensure you're not using a reference to a connection after `pool.putconn()` has been called on it.
```
ImportError: cannot import name 'ConnectionPool' from 'psycopg2_pool'
```
cause The `psycopg2-pool` library is either not installed, or the import path is incorrect. The package name on PyPI is `psycopg2-pool`, but the Python import module is `psycopg2_pool`.

fix First, ensure `psycopg2-pool` is installed: `pip install psycopg2-pool`. Then, correct your import statement to `from psycopg2_pool import ConnectionPool`.

Warnings

gotcha Failing to return connections to the pool will lead to pool exhaustion and subsequent `psycopg2.OperationalError: no more connections in the pool` errors. Always use `with pool.getconn() as conn:` or ensure `pool.putconn(conn)` is called in a `finally` block.
Fix: Use the `with` statement: `with pool.getconn() as conn: ...` or wrap explicit `getconn` in a `try...finally` block: `conn = pool.getconn(); try: ... finally: pool.putconn(conn)`.
gotcha Not closing the connection pool when your application shuts down will leave database connections open, potentially leading to resource leaks or exhausting server-side connections. Call `pool.close()` when your application exits.
Fix: Ensure `pool.close()` is called during application shutdown, for example, in a `finally` block or a signal handler.
gotcha Be careful when mixing `psycopg2` and `psycopg2-binary`. While `psycopg2-pool` itself depends on `psycopg2`, many users directly install `psycopg2-binary` for convenience. Installing both can lead to conflicts or unexpected behavior.
Fix: Choose either `psycopg2` or `psycopg2-binary` and stick to one in your project's dependencies. If unsure, `psycopg2-binary` is often easier for local development, while `psycopg2` is preferred for production builds where you control system dependencies.
gotcha Connection pools provide isolated connections. A transaction started on one connection (e.g., `conn.begin()`) is local to that specific connection. If you get another connection from the pool, it will be a new, independent connection, and the previous transaction will not carry over.
Fix: Ensure that entire transactions are handled within the scope of a single connection obtained from the pool. Do not assume transactional context will persist across multiple `getconn()` calls, even if they appear consecutive.

Install

pip install psycopg2-pool Install psycopg2-pool (with psycopg2)
pip install psycopg2-pool[binary] Install psycopg2-pool (with psycopg2-binary)

Imports

ConnectionPool

from psycopg2_pool import ConnectionPool

ThreadedConnectionPool

from psycopg2_pool import ThreadedConnectionPool

Quickstart

This quickstart demonstrates how to initialize a `ConnectionPool`, obtain connections using both `with` statements (recommended) and explicit `getconn`/`putconn` calls, execute a simple query, and properly close the pool upon application shutdown. Ensure your PostgreSQL server is running and database credentials are set, ideally via environment variables.

import os
from psycopg2_pool import ConnectionPool
import psycopg2

# Configure your database connection details
# Use environment variables for sensitive info in production
DB_HOST = os.environ.get('DB_HOST', 'localhost')
DB_NAME = os.environ.get('DB_NAME', 'testdb')
DB_USER = os.environ.get('DB_USER', 'user')
DB_PASSWORD = os.environ.get('DB_PASSWORD', 'password')

# Initialize a connection pool with min/max connections
# Pass psycopg2.connect keyword arguments directly.
pool = None # Initialize pool to None for safe cleanup
try:
    pool = ConnectionPool(
        minconn=1,
        maxconn=10,
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )

    # Example 1: Using 'with' statement for automatic connection return
    with pool.getconn() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT 1 + 1 AS result;")
            result = cur.fetchone()[0]
            print(f"Query result (with statement): {result}")
            conn.commit() # Commit if changes were made

    # Example 2: Explicitly getting and returning a connection
    conn = pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("CREATE TABLE IF NOT EXISTS test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));")
            conn.commit()
            print("Table 'test_table' ensured (explicit putconn).")
    finally:
        pool.putconn(conn) # Crucial: always return the connection!

except psycopg2.Error as e:
    print(f"Database error: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")
finally:
    # Close the pool when done (e.g., application shutdown)
    if pool:
        pool.close()
        print("Connection pool closed.")

view raw JSON →