{"id":7520,"library":"psycopg2-pool","title":"psycopg2-pool","description":"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.","status":"active","version":"1.2","language":"en","source_language":"en","source_url":"https://github.com/Changaco/psycopg2-pool","tags":["database","postgresql","psycopg2","connection-pooling"],"install":[{"cmd":"pip install psycopg2-pool","lang":"bash","label":"Install psycopg2-pool (with psycopg2)"},{"cmd":"pip install psycopg2-pool[binary]","lang":"bash","label":"Install psycopg2-pool (with psycopg2-binary)"}],"dependencies":[{"reason":"Core PostgreSQL adapter for which this library provides pooling. Alternatively, psycopg2-binary can be used.","package":"psycopg2","optional":false}],"imports":[{"symbol":"ConnectionPool","correct":"from psycopg2_pool import ConnectionPool"},{"symbol":"ThreadedConnectionPool","correct":"from psycopg2_pool import ThreadedConnectionPool"}],"quickstart":{"code":"import os\nfrom psycopg2_pool import ConnectionPool\nimport psycopg2\n\n# Configure your database connection details\n# Use environment variables for sensitive info in production\nDB_HOST = os.environ.get('DB_HOST', 'localhost')\nDB_NAME = os.environ.get('DB_NAME', 'testdb')\nDB_USER = os.environ.get('DB_USER', 'user')\nDB_PASSWORD = os.environ.get('DB_PASSWORD', 'password')\n\n# Initialize a connection pool with min/max connections\n# Pass psycopg2.connect keyword arguments directly.\npool = None # Initialize pool to None for safe cleanup\ntry:\n    pool = ConnectionPool(\n        minconn=1,\n        maxconn=10,\n        host=DB_HOST,\n        database=DB_NAME,\n        user=DB_USER,\n        password=DB_PASSWORD\n    )\n\n    # Example 1: Using 'with' statement for automatic connection return\n    with pool.getconn() as conn:\n        with conn.cursor() as cur:\n            cur.execute(\"SELECT 1 + 1 AS result;\")\n            result = cur.fetchone()[0]\n            print(f\"Query result (with statement): {result}\")\n            conn.commit() # Commit if changes were made\n\n    # Example 2: Explicitly getting and returning a connection\n    conn = pool.getconn()\n    try:\n        with conn.cursor() as cur:\n            cur.execute(\"CREATE TABLE IF NOT EXISTS test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));\")\n            conn.commit()\n            print(\"Table 'test_table' ensured (explicit putconn).\")\n    finally:\n        pool.putconn(conn) # Crucial: always return the connection!\n\nexcept psycopg2.Error as e:\n    print(f\"Database error: {e}\")\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")\nfinally:\n    # Close the pool when done (e.g., application shutdown)\n    if pool:\n        pool.close()\n        print(\"Connection pool closed.\")\n","lang":"python","description":"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."},"warnings":[{"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)`.","message":"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.","severity":"gotcha","affected_versions":"All"},{"fix":"Ensure `pool.close()` is called during application shutdown, for example, in a `finally` block or a signal handler.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"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.","cause":"All available connections in the pool are currently in use and have not been returned.","error":"psycopg2.OperationalError: no more connections in the pool"},{"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.","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.","error":"AttributeError: 'NoneType' object has no attribute 'cursor'"},{"fix":"First, ensure `psycopg2-pool` is installed: `pip install psycopg2-pool`. Then, correct your import statement to `from psycopg2_pool import ConnectionPool`.","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`.","error":"ImportError: cannot import name 'ConnectionPool' from 'psycopg2_pool'"}]}