1. Home
2. MySQL Connector/Python Tutorial
3. Connection Pooling using Connector/Python

Connection Pooling using Connector/Python

Last updated on July 27, 2020

What is Connection Pool? #

So far every time we want to talk to the database we have created a new connection. This is fine if your application's user base is small. However, if you already know that your application is going to be used by millions of people around the globe then this approach is not very feasible.

The problem is that opening and maintaining a connection is an expensive operation and wastes a lot of resources. Issues like this can make your application slow and unresponsive. Eventually, driving the users away from the application.

Enter the Connection pooling to the rescue.

The connection pool a standard way of caching connections in the memory so that they can be reused when needed instead of creating a connection from scratch. The connection pool if implemented correctly offers a significant performance boost.

In connection pooling whenever an application needs to talk to the database it fetches the connection from the pool. After performing the desired queries application releases the connection and pushes it back to the pool. In case all the connections are being used, a new connection is created and added to the pool.

Creating Connection Pool in MySQL Connector/Python #

There are two ways of creating a connection pool.

1. mysql.connector.connect()
2. mysql.connector.pooling.MySQLConnectionPool

Creating Connection Pool via connect() function #

To create a connection pool with the connect() function, we pass any one of the following pool related argument to it.

Here is an example:

1
2
3

db = mysql.connector.connect(option_files='my.conf',
                             pool_name='my_connection_pool',
                             pool_size=10)

The above call to connect() function does two things:

1. Creates a connection pool named my_connection_pool of size 10.
2. Returns the first connection from the pool and assign it to the db variable.

From now on, subsequent calls to connect() with the same pool name, will return the connection from an existing pool. If the connect() function is called and there is no available connection in the pool, a PoolError exception is raised.

Furthermore, the connection object returned by connect() is of type PooledMySQLConnection instead of MySQLConnection or CMySQLConnection.

The pooled connection object (i.e PooledMySQLConnection) object behaves exactly like MySQLConnection or CMySQLConnection, the only differences are:

1. The close() returns the connection to the connection pool instead of actually closing the connection.

2. The pool connection object has a property named pool_name which returns the name of the connection pool to which the connection belongs.

Note that connection are created lazily by the pool. This means if you create a connection pool of size 10, but only 5 users are accessing the application simultaneously then only 5 connections will be made. The 6th connection or so on will be created on demand.

Let's now take some examples:

Example 1: Fetching connections from the connection pool

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

import mysql.connector
from mysql.connector import errors

try:

    # create connection pool and fetch the first connection
    db1 = mysql.connector.connect(option_files='my.conf',
                                 pool_name='my_connection_pool',
                                 pool_size=3)
    print("Connection db1:", db1.connection_id)

    # fetch the second connection from the pool
    db2 = mysql.connector.connect(pool_name='my_connection_pool')
    print("Connection db2:", db2.connection_id)

    # fetch the third connection from the pool
    db3 = mysql.connector.connect(pool_name='my_connection_pool')
    print("Connection db3:", db3.connection_id)    

    try:
        # fetch the fourth connection from the pool
        db4 = mysql.connector.connect(pool_name='my_connection_pool')

    except errors.PoolError as e:
        # connection pool exhausted, so we can't fetch 4th connection
        print(e)
        print('Closing db3 connection ')
        db3.close()

        # lets try fetching db4 again
        db4 = mysql.connector.connect(pool_name='my_connection_pool')


except errors.Error as e:
    print(e)

else:
    print("Connection db1:", db1.connection_id)
    print("Connection db2:", db2.connection_id)
    print("Connection db4:", db4.connection_id)
    db1.close()
    db2.close()
    db4.close()

Expected Output:

1
2
3
4
5
6
7
8

Connection db1: 163
Connection db2: 164
Connection db3: 165
Failed getting connection; pool exhausted
Closing db3 connection 
Connection db1: 163
Connection db2: 164
Connection db4: 165

Note: Connection ids may differ every time you run the program.

Here is how it works:

1. In line 78, we create a connection of size 3 and fetch the first connection from it.

2. In line 13, we fetch the second connection from the pool.

3. In line 17, we fetch the third connection from the pool.

4. The connection pool is now exhausted, subsequent calls to connect() with the same pool name will raise PoolError exception. This is exactly what's happening in line 25.

5. In line 31, we close db3 connection. So, now there is one available connection in the pool. The subsequent call to connect() assigns this connection to the db4 variable (in line 34).

6. Finally, we print the connection ids and then close the connections. Note that the connection id of db3 and db4 is same this is because db4 is reusing the same connection which was used by db3.

Example 2: Executing queries on a connection fetched from the connection pool

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import mysql.connector
from mysql.connector import errors

try:

    # create connection pool and fetch the first connection
    db = mysql.connector.connect(option_files='my.conf',
                                 pool_name='my_connection_pool',
                                 pool_size=3)

    cursor = db.cursor(dictionary=True)

    print("Pool name:", db.pool_name)
    print("Connection ID:", db.connection_id)

    cursor.execute('select * from city ORDER BY id desc limit 10')

    print("\nResult: ")

    for row in cursor:
        print(row)

except errors.Error as e:
    print(e)

finally:
    db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

Pool name: my_connection_pool
Connection ID: 208

Result: 
{'Population': 1780000, 'Name': 'Kabul', 'District': 'Kabol', 'CountryCode': 'AFG', 'ID': 4081}
{'Population': 92020, 'Name': 'Rafah', 'District': 'Rafah', 'CountryCode': 'PSE', 'ID': 4079}
{'Population': 100231, 'Name': 'Nablus', 'District': 'Nablus', 'CountryCode': 'PSE', 'ID': 4078}
{'Population': 113901, 'Name': 'Jabaliya', 'District': 'North Gaza', 'CountryCode': 'PSE', 'ID': 4077}
{'Population': 119401, 'Name': 'Hebron', 'District': 'Hebron', 'CountryCode': 'PSE', 'ID': 4076}
{'Population': 123175, 'Name': 'Khan Yunis', 'District': 'Khan Yunis', 'CountryCode': 'PSE', 'ID': 4075}
{'Population': 353632, 'Name': 'Gaza', 'District': 'Gaza', 'CountryCode': 'PSE', 'ID': 4074}
{'Population': 128037, 'Name': 'Gweru', 'District': 'Midlands', 'CountryCode': 'ZWE', 'ID': 4073}
{'Population': 131367, 'Name': 'Mutare', 'District': 'Manicaland', 'CountryCode': 'ZWE', 'ID': 4072}
{'Population': 164362, 'Name': 'Mount Darwin', 'District': 'Harare', 'CountryCode': 'ZWE', 'ID': 4071}

Creating Connection Pool via MySQLConnectionPool class #

Another way to create connection pool is to instantiate it directly using the MySQLConnectionPool class. The MySQLConnectionPool is the main class which creates and manages the connection pool. The syntax of MySQLConnectionPool constructor is as follows:

1
2
3

MySQLConnectionPool(pool_name=None,
                    pool_size=5,                
                    **kwargs)

Here is an example:

1
2
3

con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
                               pool_size=5,
                               option_files='my.conf',)

Instantiating MySQLConnectionPool only creates the connection pool it doesn't automatically fetch the first connection from the pool.

To obtain a connection, we use the get_connection() method of the MySQLConnectionPool instance.

1
2
3
4
5

# fetch the first connection
db1 = con_pool.get_connection()

# fetch the second connection
db2 = con_pool.get_connection()

As usual, when the connection pool is exhausted, a PoolError exception will be raised.

Let's take some examples now:

Example 1: Fetching connections from the connection pool

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector import errors

try:

    # create the connection pool
    con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
                                   pool_size=3,
                                   option_files='my.conf',)

    db1 = con_pool.get_connection()
    print("Connection db1:", db1.connection_id)

    # fetch the second connection from the pool
    db2 = con_pool.get_connection()
    print("Connection db2:", db2.connection_id)

    # fetch the third connection from the pool
    db3 = con_pool.get_connection()
    print("Connection db3:", db3.connection_id)

    try:
        # fetch the fourth connection from the pool
        db4 = con_pool.get_connection()

    except errors.PoolError as e:
        # connection pool exhausted, so we can't fetch 4th connection
        print(e)
        print('Closing db3 connection ')
        db3.close()

        # lets try fetching db4 again
        db4 = con_pool.get_connection()


except errors.Error as e:
    print(e)

finally:
    print("Connection db1:", db1.connection_id)
    print("Connection db2:", db2.connection_id)
    print("Connection db4:", db4.connection_id)
    db1.close()
    db2.close()
    db4.close()

Expected Output:

1
2
3
4
5
6
7
8

Connection db1: 229
Connection db2: 230
Connection db3: 231
Failed getting connection; pool exhausted
Closing db3 connection 
Connection db1: 229
Connection db2: 230
Connection db4: 231

This example works exactly like the example we went through in the preceding section. The only difference is that here we are creating a connection pool explicitly via MySQLConnectionPool and then obtaining connections via the get_connection() method.

Example 2: Executing queries on a connection fetched from the connection pool

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector import errors

try:

    # create the connection pool
    con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
                                   pool_size=3,
                                   option_files='my.conf', )

    # fetch the first connection
    db = con_pool.get_connection()

    cursor = db.cursor(dictionary=True)

    print("Pool name:", db.pool_name)
    print("Connection ID:", db.connection_id)

    cursor.execute('select * from city ORDER BY id desc limit 10')

    print("\nResult: ")

    for row in cursor:
        print(row)

except errors.Error as e:
    print(e)

finally:
    db.close()

Expected Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

Pool name: my_connection_pool
Connection ID: 241

Result: 
{'Population': 1780000, 'District': 'Kabol', 'Name': 'Kabul', 'CountryCode': 'AFG', 'ID': 4081}
{'Population': 92020, 'District': 'Rafah', 'Name': 'Rafah', 'CountryCode': 'PSE', 'ID': 4079}
{'Population': 100231, 'District': 'Nablus', 'Name': 'Nablus', 'CountryCode': 'PSE', 'ID': 4078}
{'Population': 113901, 'District': 'North Gaza', 'Name': 'Jabaliya', 'CountryCode': 'PSE', 'ID': 4077}
{'Population': 119401, 'District': 'Hebron', 'Name': 'Hebron', 'CountryCode': 'PSE', 'ID': 4076}
{'Population': 123175, 'District': 'Khan Yunis', 'Name': 'Khan Yunis', 'CountryCode': 'PSE', 'ID': 4075}
{'Population': 353632, 'District': 'Gaza', 'Name': 'Gaza', 'CountryCode': 'PSE', 'ID': 4074}
{'Population': 128037, 'District': 'Midlands', 'Name': 'Gweru', 'CountryCode': 'ZWE', 'ID': 4073}
{'Population': 131367, 'District': 'Manicaland', 'Name': 'Mutare', 'CountryCode': 'ZWE', 'ID': 4072}
{'Population': 164362, 'District': 'Harare', 'Name': 'Mount Darwin', 'CountryCode': 'ZWE', 'ID': 4071}