Using Connection Pools with MariaDB Connector/Python

Overview

Python developers can connect to MariaDB Enterprise using MariaDB Connector/Python to use connection pools.

Connection pools enable reuse of database connections to minimize the performance overhead of connecting to the database and the churn of opening and closing connections.

Connection pools hold connections open in a pool. When a process is done with the connection, it is returned to the pool rather than closed, allowing MariaDB Connector/Python to reacquire a connection as needed.

A connection obtained from a connection pool can be used in the same way as a connection instantiated using the Connection class.

Connection Pools

Connection pools are created, used, and managed using the following class:

Class

Description

ConnectionPool

Create a connection pool and returns a connection pool object.

The ConnectionPool class provides several methods to manage connections:

Class

Description

get_connection()

Returns a connection from the connection pool or raises a PoolError if a connection is not available.

add_connection()

Adds a connection to the connection pool. If the pool doesn't have a free slot or is not configured, a PoolError exception will be raised.

close()

Closes connection pool and all connections.

Code Examples

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

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(
       user="connpy_test",
       password="passwd",
       host="192.0.2.1",
       port=3306,
       pool_name="web-app",
       pool_size=20
    )

    # Return Connection Pool
    return pool

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

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(
       user="connpy_test",
       password="passwd",
       host="192.0.2.1",
       port=3306,
       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(
      user="connpy_test",
      password="passwd",
      host="192.0.2.1",
      port=3306)
  • 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:

Method

Description

execute()

Executes a single SQL statement.

executemany()

Executes the given SQL statement for each tuple in a list.

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",
    user = "connpy_test",
    password = "secret")

# 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.