Using Connection Pools with MariaDB Connector/Python

Connection Pool Parameters

The following parameters are used to define the connection pool:

Code Example: Create Connection Pools

The following example shows how to use connection pools with the example table created in Setup for Examples.

Create a connection pool by instantiating the ConnectionPool class in your Python code:

# Module Import
import mariadb

# Create Connection Pool
def create_connection_pool():
    """Creates and returns a Connection Pool"""

    # Create Connection Pool
    pool = mariadb.ConnectionPool(
      host="192.0.2.1",
      port=3306,
      user="db_user",
      password="USER_PASSWORD",
      pool_name="web-app",
      pool_size=20,
      pool_validation_interval=250)

    # Return Connection Pool
    return pool

Renaming the pool or increasing its size requires creating a new instance of the ConnectionPool class.

Code Example: Get Connections from the Pool

The following example shows how to use connection pools with the example table created in Setup for Examples.

Get a connection from the pool using the get_connection() method:

# Module Import
import mariadb

# Create Connection Pool
def create_connection_pool():
    """Creates and returns a Connection Pool"""

    # Create Connection Pool
    pool = mariadb.ConnectionPool(
      host="192.0.2.1",
      port=3306,
      user="db_user",
      password="USER_PASSWORD",
      pool_name="web-app",
      pool_size=20)

    # Return Connection Pool
    return pool

# Establish Pool Connection
try:
    pool=create_connection_pool()
    pconn = pool.get_connection()

    # Instantiate Cursor
    cur = pconn.cursor()

except mariadb.PoolError as e:
   # Report Error
   print(f"Error opening connection from pool: {e}")

   # Create New Connection as Alternate
   pconn = mariadb.connection(
      host="192.0.2.1", port=3306,
      user="db_user", password="db_user_password")

• The function that returns a connection pool must be defined before being called with regards to its ordering in the script.

• When all the connections in the pool are in use, the get_connection() method raises a PoolError exception.

• If the connection pool fails to provide a connection, create a new instance of the Connection class outside the connection pool. You can then optionally add the alternate connection to the pool if you want to use it further. For more information, see Adding Existing Connection.

• Create a cursor by calling the cursor() method on the connection:

• Perform basic operations by executing SQL statements with the cursor.

The cursor provides two methods for executing SQL statements:

Code Example: Return Connections to the Pool

The following example shows how to use connection pools with the example table created in Setup for Examples.

Return the connection by calling the close() method on the connection:

# Close Connection
pconn.close()

• Closing a connection from a connection pool does not actually close the connection. Instead, the connection returns the connection to the pool.

• Once the connection is returned to the pool, other application threads can use it.

Code Example: Add Existing Connections to the Pool

The following example shows how to use connection pools with the example table created in Setup for Examples.

In cases where you have created a separate connection, you can add it to a connection pool and let the pool maintain the connection separately, rather than closing it outright.

Add an existing connection to the pool using the add_connection() method:

try:
    pool.add_connection(pconn)

except mariadb.PoolError as e:

    # Report Error
    print(f"Error adding connection to pool: {e}")

• When the connection pool has reached the maximum pool size, the add_connection() method raises a PoolError exception.

Code Example: Configure Connection Pools

The following example shows how to use connection pools with the example table created in Setup for Examples.

In addition to configuring the pool when instantiating the ConnectionPool class, you can also configure an existing pool using the set_config() method:

import mariadb

# Initialize Pool
pool = mariadb.ConnectionPool(
    pool_name = "pool1",
    pool_size = 3,
    pool_reset_connection = False,
)

# Set Configuration
pool.set_config(
    host="192.0.2.1",
    port=3306,
    user="db_user",
    password="USER_PASSWORD")

# Configure an Individual Value
pool.set_config(database = "test")

# Because the pool was created without connect options, we have to add
# the connections ourselves.
pool.add_connection()
pool.add_connection()
pool.add_connection()

conn = pool.get_connection()

• Connections must be added to the pool manually using the add_connection() method.

• The Connection attributes (user, password, host, etc.) associated with the connection pool can be reconfigured.

• The Connection Pool attributes (pool_name, pool_size, etc.) cannot be reconfigured.