8.1 Connector/Python Connection Pooling

MySQL Connector/Python 1.1.1 and up supports simple connection pooling that has these characteristics:

Applications that can benefit from connection-pooling capability include:

A connection pool can be created implicitly or explicitly.

To create a connection pool implicitly: Open a connection and specify one or more pool-related arguments (pool_name, pool_size). For example:

dbconfig = {
  "database": "test",
  "user":     "joe"
}

cnx = mysql.connector.connect(pool_name = "mypool",
                              pool_size = 3,
                              **dbconfig)

The pool name is restricted to alphanumeric characters and the special characters ., _, *, $, and #. The pool name must be no more than pooling.CNX_POOL_MAXNAMESIZE characters long (default 64).

The pool size must be greater than 0 and less than pooling.CNX_POOL_MAXSIZE (default 32).

With either the pool_name or pool_size argument present, Connector/Python creates the new pool. If the pool_name argument is not given, the connect() call automatically generates the name, composed from whichever of the host, port, user, and database connection arguments are given, in that order. If the pool_size argument is not given, the default size is 5 connections.

Subsequent calls to connect() that name the same connection pool return connections from the existing pool. Any pool_size or connection parameter arguments are ignored, so the following connect() calls are equivalent to the original connect() call shown earlier:

cnx = mysql.connector.connect(pool_name = "mypool", pool_size = 3)
cnx = mysql.connector.connect(pool_name = "mypool", **dbconfig)
cnx = mysql.connector.connect(pool_name = "mypool")

Pooled connections obtained by calling connect() with a pool-related argument have a class of PooledMySQLConnection (see Section 9.4, “Class pooling.PooledMySQLConnection). PooledMySQLConnection pooled connection objects are similar to MySQLConnection unpooled connection objects, with these differences:

To create a connection pool explicitly: Create a MySQLConnectionPool object (see Section 9.3, “Class pooling.MySQLConnectionPool):

dbconfig = {
  "database": "test",
  "user":     "joe"
}

cnxpool = mysql.connector.pooling.MySQLConnectionPool(pool_name = "mypool",
                                                      pool_size = 3,
                                                      **dbconfig)

To request a connection from the pool, use its get_connection() method:

cnx1 = cnxpool.get_connection()
cnx2 = cnxpool.get_connection()

When you create a connection pool explicitly, it is possible to use the pool object's set_config() method to reconfigure the pool connection parameters:

dbconfig = {
  "database": "performance_schema",
  "user":     "admin",
  "password": "secret"
}

cnxpool.set_config(**dbconfig)

Connections requested from the pool after the configuration change use the new parameters. Connections obtained before the change remain unaffected, but when they are closed (returned to the pool) are reopened with the new parameters before being returned by the pool for subsequent connection requests.