Python MySQL - Connection Pooling

What You'll Learn Here

What is connection pooling — reusing connections for better performance
Why use connection pooling — performance and scalability benefits
How pools work — understanding the lifecycle
Implementing pools — using MySQL connector pooling
Configuration — setting pool size and timeouts
Real-world example — building a scalable web application

What is Connection Pooling?

Connection pooling is a technique where a set of database connections are created and maintained in a "pool" for reuse. Instead of creating a new connection every time you need one, you borrow one from the pool and return it when you're done.

🚗 Think of it like a car rental service.

Instead of buying a new car every time you need to drive somewhere, you rent one from a pool of available cars. When you're done, you return it so someone else can use it.

Connection pooling works exactly the same way! You borrow a connection from the pool, use it for your database operations, and return it when you're done.

App Request ➡️ Connection Pool ➡️ Get Connection ➡️ Use Database ➡️ Return Connection
Connections are reused, not recreated

Connection Pool vs. Single Connection

# ============================================================
# WITHOUT CONNECTION POOLING
# ============================================================

# Every request creates a new connection
def get_user_data(user_id):
    connection = mysql.connector.connect(...)  # Creates new connection
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    result = cursor.fetchone()
    connection.close()  # Closes connection
    return result

# For 1000 requests, this creates and closes 1000 connections
# 🐢 Very slow and resource-intensive!

# ============================================================
# WITH CONNECTION POOLING
# ============================================================

# Create pool once
pool = mysql.connector.pooling.MySQLConnectionPool(...)

def get_user_data(user_id):
    connection = pool.get_connection()  # Borrow from pool
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    result = cursor.fetchone()
    connection.close()  # Returns connection to pool
    return result

# For 1000 requests, only 5-10 connections are created and reused
# 🚀 Fast and efficient!

Key point: Connection pooling reuses connections instead of creating new ones for every request.

Quick Check: What is connection pooling? (Answer: A technique where database connections are reused instead of being created and destroyed for each request)

Why Use Connection Pooling?

The Benefits of Connection Pooling

Speed

Creating a connection takes time. Reusing connections is much faster.

Efficiency

Reduces CPU and memory usage on both application and database servers.

Scalability

Handles more concurrent users with fewer resources.

Resource Management

Prevents connection leaks and manages connection limits.

Performance

Significantly improves application response times.

Reliability

Handles connection failures gracefully with automatic recovery.

# ============================================================
# PERFORMANCE COMPARISON
# ============================================================

import time

# Simulate connection creation time (100ms)
# Simulate query execution time (10ms)

# Without pooling - 100 requests
# Time = 100 * (100ms + 10ms) = 11,000ms = 11 seconds

# With pooling - 5 connections reused for 100 requests
# Time = 5 * 100ms + 100 * 10ms = 500ms + 1000ms = 1.5 seconds

# Saving: 11 - 1.5 = 9.5 seconds!
# That's 86% faster!

print("""
┌─────────────────────────────┬─────────────────────────────┐
│ Without Pooling             │ With Pooling               │
├─────────────────────────────┼─────────────────────────────┤
│ Each request creates a new  │ Connections are reused     │
│ connection                  │                            │
├─────────────────────────────┼─────────────────────────────┤
│ 100 connections for 100     │ 5 connections for 100      │
│ requests                    │ requests                   │
├─────────────────────────────┼─────────────────────────────┤
│ 11 seconds                  │ 1.5 seconds                │
├─────────────────────────────┼─────────────────────────────┤
│ High CPU & Memory usage     │ Low CPU & Memory usage     │
└─────────────────────────────┴─────────────────────────────┘
""")

Key benefits:

Faster performance — connections are ready to use
Lower resource usage — fewer connections created
Better scalability — handles more users
Connection management — automatic cleanup
Reduced latency — no connection creation delay

Quick Check: What is the main benefit of connection pooling? (Answer: It improves performance by reusing connections instead of creating new ones)

How Connection Pools Work

Understanding the Pool Lifecycle

A connection pool has a simple but important lifecycle. Understanding it helps you use pools effectively.

# ============================================================
# CONNECTION POOL LIFECYCLE
# ============================================================

# 1. POOL CREATION
# The pool is created with a set number of connections
pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name="my_pool",
    pool_size=10,
    **db_config
)
# This creates 10 connections ready to use

# 2. GETTING A CONNECTION
# When you need a connection, you borrow one from the pool
connection = pool.get_connection()
# If a connection is available, you get it immediately
# If all connections are busy, you wait

# 3. USING THE CONNECTION
# Use the connection for your database operations
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

# 4. RETURNING THE CONNECTION
# When you're done, return the connection to the pool
connection.close()  # This returns it to the pool, doesn't close it

# 5. POOL CLOSING
# When the application shuts down, close all connections
pool.close()

# ============================================================
# WHAT HAPPENS UNDER THE HOOD
# ============================================================

print("""
1. Pool Created → 10 connections established
2. Request 1 → Gets connection 1
3. Request 2 → Gets connection 2
4. Request 3 → Gets connection 3
5. Request 1 done → Returns connection 1
6. Request 4 → Gets connection 1 (reused!)
7. If all 10 connections busy → Request waits
8. When connections returned → Waiting requests get them
""")

Pool lifecycle steps:

Creation — pool is initialized with connections
Get — borrow a connection from the pool
Use — perform database operations
Return — give the connection back to the pool
Close — close all connections when done

Quick Check: What happens when you call connection.close() on a pooled connection? (Answer: The connection is returned to the pool, not closed)

Implementing Connection Pools

Creating and Using Connection Pools

# ============================================================
# BASIC CONNECTION POOL IMPLEMENTATION
# ============================================================

import mysql.connector
from mysql.connector import pooling

# ============================================================
# STEP 1: CREATE THE POOL
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "myapp_db",
    "pool_name": "my_pool",
    "pool_size": 5
}

pool = mysql.connector.pooling.MySQLConnectionPool(**db_config)
print(f"Pool created: {pool.pool_name} with size {pool.pool_size}")

# ============================================================
# STEP 2: USE THE POOL
# ============================================================

def execute_query(query, params=None):
    """Execute a query using a connection from the pool"""
    connection = None
    cursor = None
    
    try:
        # Get a connection from the pool
        connection = pool.get_connection()
        cursor = connection.cursor()
        
        # Execute the query
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        
        # For SELECT queries, return results
        if query.strip().upper().startswith("SELECT"):
            return cursor.fetchall()
        else:
            # For INSERT, UPDATE, DELETE, commit
            connection.commit()
            return cursor.rowcount
            
    except Exception as e:
        if connection:
            connection.rollback()
        print(f"Error: {e}")
        raise
        
    finally:
        # Return connection to the pool
        if cursor:
            cursor.close()
        if connection:
            connection.close()

# ============================================================
# STEP 3: TEST THE POOL
# ============================================================

# Insert a student
result = execute_query(
    "INSERT INTO students (first_name, last_name, age) VALUES (%s, %s, %s)",
    ("Rahul", "Sharma", 22)
)
print(f"Inserted: {result} row(s)")

# Select students
result = execute_query("SELECT * FROM students")
print(f"Found: {len(result)} students")

# ============================================================
# STEP 4: CLOSE THE POOL (when application shuts down)
# ============================================================

# pool.close()  # Uncomment to close all connections

Implementation steps:

Create the pool with pool_name and pool_size
Get connections using pool.get_connection()
Use the connection for database operations
Return the connection with connection.close()
Close the pool when done

Quick Check: What method do you use to get a connection from the pool? (Answer: pool.get_connection())

Pool Configuration

Configuring Your Connection Pool

# ============================================================
# CONNECTION POOL CONFIGURATION OPTIONS
# ============================================================

# ============================================================
# OPTION 1: BASIC CONFIGURATION
# ============================================================

pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name="my_pool",
    pool_size=5,
    host="localhost",
    user="root",
    password="secret",
    database="myapp_db"
)

# ============================================================
# OPTION 2: CONFIGURATION WITH DICTIONARY
# ============================================================

config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "myapp_db",
    "pool_name": "my_pool",
    "pool_size": 10,
    "pool_reset_session": True,  # Reset session state when connection is returned
    "use_pure": True,  # Use pure Python implementation
    "charset": "utf8mb4",
    "autocommit": False
}

pool = mysql.connector.pooling.MySQLConnectionPool(**config)

# ============================================================
# OPTION 3: ADVANCED CONFIGURATION
# ============================================================

config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "myapp_db",
    "pool_name": "my_pool",
    "pool_size": 20,  # Max connections in pool
    "pool_reset_session": True,  # Reset session on reuse
    "connection_timeout": 10,  # Connection timeout in seconds
    "charset": "utf8mb4",
    "use_pure": True,
    "autocommit": False,
    "raise_on_warnings": False,
    "use_unicode": True
}

# ============================================================
# POOL SIZE BEST PRACTICES
# ============================================================

print("""
┌─────────────────────────────┬─────────────────────────────────┐
│ Application Type            │ Recommended Pool Size           │
├─────────────────────────────┼─────────────────────────────────┤
│ Low traffic / Simple apps   │ 5-10                           │
├─────────────────────────────┼─────────────────────────────────┤
│ Medium traffic web apps     │ 10-20                          │
├─────────────────────────────┼─────────────────────────────────┤
│ High traffic web apps       │ 20-50                          │
├─────────────────────────────┼─────────────────────────────────┤
│ Enterprise / Heavy usage    │ 50-100                         │
├─────────────────────────────┼─────────────────────────────────┤
│ Formula                     │ (Max concurrent users) * 2      │
└─────────────────────────────┴─────────────────────────────────┘

Note: Don't set pool size too high as MySQL has a limit
on maximum connections (default is 151).
""")

Configuration options:

pool_name — unique name for the pool
pool_size — maximum connections in the pool
pool_reset_session — reset session state on reuse
connection_timeout — timeout for getting a connection
charset — character set for the connection
autocommit — auto-commit transactions

Quick Check: What is the recommended pool size for a medium traffic web application? (Answer: 10-20 connections)

Real-World Example: Scalable Application

Building a Scalable Web Application

# ============================================================
# SCALABLE APPLICATION WITH CONNECTION POOLING
# ============================================================

import mysql.connector
from mysql.connector import pooling
import threading
import time
import logging

# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class AppDatabase:
    """Application database with connection pooling"""
    
    _instance = None
    
    def __new__(cls, db_config):
        """Singleton pattern - only one pool per application"""
        if cls._instance is None:
            cls._instance = super().__new__(cls)
            cls._instance._initialize(db_config)
        return cls._instance
    
    def _initialize(self, db_config):
        """Initialize the connection pool"""
        try:
            self.pool = mysql.connector.pooling.MySQLConnectionPool(**db_config)
            logger.info(f"Connection pool created: {self.pool.pool_name} (size: {self.pool.pool_size})")
        except Exception as e:
            logger.error(f"Failed to create pool: {e}")
            raise
    
    def get_connection(self):
        """Get a connection from the pool"""
        try:
            return self.pool.get_connection()
        except Exception as e:
            logger.error(f"Failed to get connection: {e}")
            raise
    
    def execute_query(self, query, params=None, fetch_all=True):
        """Execute a query and return results"""
        connection = None
        cursor = None
        
        try:
            connection = self.get_connection()
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            if query.strip().upper().startswith("SELECT"):
                if fetch_all:
                    return cursor.fetchall()
                else:
                    return cursor.fetchone()
            else:
                connection.commit()
                return cursor.rowcount
                
        except Exception as e:
            if connection:
                connection.rollback()
            logger.error(f"Query execution failed: {e}")
            raise
            
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()
    
    def close(self):
        """Close all connections in the pool"""
        try:
            self.pool.close()
            logger.info("Connection pool closed")
        except Exception as e:
            logger.error(f"Failed to close pool: {e}")

# ============================================================
# SIMULATING CONCURRENT REQUESTS
# ============================================================

def simulate_request(db, request_id):
    """Simulate a web request"""
    try:
        logger.info(f"Request {request_id}: Starting")
        
        # Insert a new user
        user_id = db.execute_query(
            "INSERT INTO users (username, email) VALUES (%s, %s)",
            (f"user_{request_id}", f"user_{request_id}@email.com")
        )
        logger.info(f"Request {request_id}: Inserted user (ID: {user_id})")
        
        # Get the user
        user = db.execute_query(
            "SELECT * FROM users WHERE id = %s",
            (user_id,),
            fetch_all=False
        )
        logger.info(f"Request {request_id}: Got user: {user}")
        
        # Update the user
        db.execute_query(
            "UPDATE users SET last_login = NOW() WHERE id = %s",
            (user_id,)
        )
        logger.info(f"Request {request_id}: Updated last login")
        
        logger.info(f"Request {request_id}: Completed")
        
    except Exception as e:
        logger.error(f"Request {request_id}: Failed - {e}")

# ============================================================
# DEMONSTRATION
# ============================================================

# Configuration
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "myapp_db",
    "pool_name": "app_pool",
    "pool_size": 10,
    "pool_reset_session": True
}

# Create database instance (singleton)
db = AppDatabase(db_config)

# Simulate 20 concurrent requests
print("\n" + "=" * 50)
print("SIMULATING 20 CONCURRENT REQUESTS")
print("=" * 50)

threads = []
start_time = time.time()

for i in range(20):
    thread = threading.Thread(target=simulate_request, args=(db, i+1))
    threads.append(thread)
    thread.start()

# Wait for all threads to complete
for thread in threads:
    thread.join()

end_time = time.time()
print(f"\nAll requests completed in {end_time - start_time:.2f} seconds")

# Close the pool when done
db.close()

This example demonstrates:

Singleton pattern for shared pool
Concurrent request handling
Connection reuse across multiple requests
Proper error handling and logging
Graceful pool cleanup

Quick Check: Why is connection pooling important for concurrent applications? (Answer: It allows many requests to share a limited number of connections efficiently)

Best Practices

Connection Pooling Guidelines

# ============================================================
# BEST PRACTICES FOR CONNECTION POOLING
# ============================================================

print("1. SET APPROPRIATE POOL SIZE")
print("   - Too small → bottlenecks and waiting")
print("   - Too large → wasted resources")
print("   - Formula: (max concurrent users) * 2")

print("\n2. ALWAYS RETURN CONNECTIONS")
print("   - Use try/finally or with statements")
print("   - connection.close() returns to pool")
print("   - Never leave connections borrowed")

print("\n3. HANDLE CONNECTION ERRORS")
print("   - Connection can be closed by server")
print("   - Check if connection is still alive")
print("   - Reconnect if needed")

print("\n4. USE SINGLETON PATTERN")
print("   - One pool per application")
print("   - Share across all modules")
print("   - Avoid creating multiple pools")

print("\n5. CLOSE THE POOL PROPERLY")
print("   - Call pool.close() on shutdown")
print("   - Prevents connection leaks")
print("   - Database can clean up properly")

print("\n6. MONITOR POOL USAGE")
print("   - Track active connections")
print("   - Monitor wait times")
print("   - Adjust pool size as needed")

print("\n7. USE CONNECTION TIME"OUTS)
print("   - Set timeout for getting connections")
print("   - Prevent infinite waiting")
print("   - Handle timeout errors gracefully")

print("\n8. RESET SESSIONS")
print("   - pool_reset_session=True")
print("   - Clean up session state")
print("   - Avoid cross-request contamination")

Summary of best practices:

Set appropriate pool size — balance performance and resources
Always return connections — use try/finally
Handle connection errors — check and reconnect
Use singleton pattern — one pool per application
Close the pool properly — on application shutdown
Monitor pool usage — track and adjust

Quick Check: What is the most common mistake with connection pools? (Answer: Forgetting to return connections to the pool)

Try It Yourself

Experiment with connection pooling in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL Connection Pooling - Practice

print("=" * 40)
print("CONNECTION POOLING - PRACTICE")
print("=" * 40)

# Simulated Connection Pool
class ConnectionPool:
    """Simulate a connection pool for learning"""
    
    def __init__(self, pool_size=3):
        self.pool_size = pool_size
        self.available_connections = []
        self.active_connections = []
        self.total_creations = 0
        
        # Create initial connections
        for i in range(pool_size):
            conn_id = f"CONN-{i+1}"
            self.available_connections.append(conn_id)
            self.total_creations += 1
        
        print(f"Pool created with {pool_size} connections")
    
    def get_connection(self):
        """Get a connection from the pool"""
        if self.available_connections:
            conn = self.available_connections.pop()
            self.active_connections.append(conn)
            print(f"Borrowed: {conn} (Active: {len(self.active_connections)}, Available: {len(self.available_connections)})")
            return conn
        else:
            print("No connections available! Waiting...")
            return None
    
    def return_connection(self, conn):
        """Return a connection to the pool"""
        if conn in self.active_connections:
            self.active_connections.remove(conn)
            self.available_connections.append(conn)
            print(f"Returned: {conn} (Active: {len(self.active_connections)}, Available: {len(self.available_connections)})")
        else:
            print(f"Connection {conn} not found in active pool")
    
    def get_stats(self):
        """Get pool statistics"""
        return {
            "available": len(self.available_connections),
            "active": len(self.active_connections),
            "total_created": self.total_creations,
            "pool_size": self.pool_size
        }

print("\n1. CREATING CONNECTION POOL")
print("-" * 40)

pool = ConnectionPool(pool_size=3)
print(f"Stats: {pool.get_stats()}")

print("\n2. BORROWING CONNECTIONS")
print("-" * 40)

# Borrow 3 connections
conn1 = pool.get_connection()
conn2 = pool.get_connection()
conn3 = pool.get_connection()