How to Use Connection Pooling in Python for Efficient Database Access

Use connection pooling in Python by creating a pool of reusable database connections with libraries like psycopg2.pool for PostgreSQL or mysql.connector.pooling for MySQL. This avoids opening and closing connections repeatedly, improving performance and resource use.

Syntax

Connection pooling involves creating a pool object that manages multiple database connections. You request a connection from the pool, use it, then return it to the pool for reuse.

For example, with psycopg2.pool.SimpleConnectionPool:

• minconn: minimum connections in the pool
• maxconn: maximum connections in the pool
• dsn: database connection string
• getconn(): get a connection from the pool
• putconn(conn): return connection to the pool

python

from psycopg2 import pool

# Create a connection pool
connection_pool = pool.SimpleConnectionPool(minconn=1, maxconn=5, dsn="dbname=test user=postgres password=secret")

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

# Use the connection (e.g., create cursor, execute queries)
# ...

# Return the connection to the pool
connection_pool.putconn(conn)

# Close all connections when done
connection_pool.closeall()

Example

This example shows how to create a connection pool with psycopg2, get a connection, execute a simple query, and return the connection to the pool.

python

from psycopg2 import pool

# Initialize connection pool
connection_pool = pool.SimpleConnectionPool(1, 3, dsn="dbname=test user=postgres password=secret")

try:
    # Get connection from pool
    conn = connection_pool.getconn()
    cursor = conn.cursor()

    # Execute a query
    cursor.execute("SELECT version();")
    version = cursor.fetchone()
    print(f"PostgreSQL version: {version[0]}")

    # Close cursor
    cursor.close()

finally:
    # Return connection to pool
    connection_pool.putconn(conn)

# Close all connections
connection_pool.closeall()

Output

PostgreSQL version: PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

Common Pitfalls

• Not returning connections to the pool with putconn() causes connection leaks and exhaustion.
• Using too many connections in the pool can overload the database server.
• Forgetting to close all connections with closeall() can leave open connections after the program ends.
• Mixing pooled and non-pooled connections can cause confusion and bugs.

python

from psycopg2 import pool

# Wrong: Not returning connection to pool
connection_pool = pool.SimpleConnectionPool(1, 2, dsn="dbname=test user=postgres password=secret")
conn = connection_pool.getconn()
cursor = conn.cursor()
cursor.execute("SELECT 1")
print(cursor.fetchone())
cursor.close()
# Missing: connection_pool.putconn(conn) leads to connection leak

# Right: Always return connection
connection_pool.putconn(conn)

Output

(1,)

Quick Reference

Key points to remember when using connection pooling in Python:

• Use a pool class from your database library (e.g., psycopg2.pool.SimpleConnectionPool for PostgreSQL).
• Set sensible minimum and maximum pool sizes based on your app and database capacity.
• Always get connections from the pool and return them after use.
• Close all connections when your app shuts down.

Key Takeaways

Use connection pooling to reuse database connections and improve performance.

Always return connections to the pool with putconn() to avoid leaks.

Set appropriate pool size limits to balance load and resource use.

Close all connections with closeall() when done to free resources.