I’m using psycopg2 with ThreadedConnectionPool in a Python script to manage PostgreSQL database connections. However, it seems like the connections are not being stored in the pool as expected. Below is my code:

from psycopg2 import pool


connection_pool = pool.ThreadedConnectionPool(
    minconn=0,
    maxconn=10,
    user='xx',
    password='xxx',
    host='xxxx',
    port='xxx',
    database='xxx'
)

def execute_query(query):
    conn = None
    try:
        print("Pool status before getting a connection")
        print(f"Used connections: {connection_pool._used}")
        print(f"Free connections: {connection_pool._pool}")

        # Get a connection from the pool
        conn = connection_pool.getconn()
        cursor = conn.cursor()

        # Execute the query
        cursor.execute(query)
        print("Query executed")

        # Fetch the results
        results = cursor.fetchall()

        # Close the cursor
        cursor.close()
        
        return results
    except Exception as error:
        print(f"Error: {error}")
        return None
    finally:
        # Release the connection back to the pool
        if conn:
            connection_pool.putconn(conn)
            print("Released connection back to the pool")
            # Print pool status after releasing the connection
            print(f"Used connections: {connection_pool._used}")
            print(f"Free connections: {connection_pool._pool}")

query = """SELECT schema_name FROM information_schema.schemata LIMIT 10;"""

# Execute the query and print the results
print(execute_query(query))
print(execute_query(query))
print(execute_query(query))
print(execute_query(query))

When I run the code, the connections don’t seem to be reused. The pool status doesn’t reflect any used connections, and it appears that each query execution is not storing the connection in the pool.

Why are the connections not being stored or reused in the connection pool? How can I ensure that connections are properly managed and reused by the ThreadedConnectionPool?

Python 3.8.13
Psycopg2 2.9.9