connection pool, why use connection pool, how to control db connection per sql

Python Database Connection Pooling with MySQL

Updated on: March 9, 2021

In this lesson, you will learn how toimplement a MySQL connection poolin Python. Also, learn the benefits of using connection pooling.
　
This article provides information intended to help developers to implement database connection pooling for Python database-centric applications.
　
First, this article provides an overview of the MySQL Database connection pooling. Next, it gives examples of using themysql.connector.poolingclass to implements connection pooling in your Python applications.
　
Finally, we will see an example showing the performance benefits you can achieve by using MySQL connection pooling in Python.

Note: We are using theMySQL connector Pythonmodule to implement connection pooling.

Further Reading:

• Solve Python MySQL Exercise
• Read Python MySQL Tutorial (Complete Guide)

Table of contents

• What is Connection Pooling in Python
• Configuring the connection pool in Python with MySQL
  • How to use the connection pool in Python
• Create a Connection pool in Python using MySQL Connector Python
  • Arguments required to create a connection pool
  • Adds a new or existing MySQL Connection to the pool
  • Get Connection object from a connection pool
  • Get Connection pool name
  • Change Configuration parameters for connections in the pool
• Manage pooled connection instance
  • Get a pooled connection
  • Close the pooled MySQL connection instance
• Python Example to Create, manage and use a Connection pool with MySQL
• Next Steps:

What is Connection Pooling in Python

Connection pooling means connections are reused rather than creating each time when requested.

Establishing MySQL connection through python is resource-expensive and time-consuming, primarily when the MySQL connector Python API is used in a middle-tier server environment. i.e., Middleware that maintains multiple connections to multiple MySQL servers and requires connections to be readily available.
　
For Example, Your application uses a database very frequently may be for data retrieving or data updations. To handle every new request application is creating a new MySQL database connection, and after completion of a request, the connection gets closed. Again for a new request, the application establishes a new connection, and this process repeats for every new request.
　
If you analyze this situation, you can easily find that the application spends most of the time creating and closing the database connection object. In this type of environment, we can improve application performance using connection pooling.

python database connection pooling working

　
A memory cache of database connections, called a connection pool, is maintained by a connection pooling module as a layer on top of any Database driver product to facilitate connection reuse. Connection pooling is performed in the background and does not affect how an application is coded.

Configuring the connection pool in Python with MySQL

To configure a connection pool, you need to consider the followingfactors: –

• The maximum connections a Database module can support. For example, the MySQL Connector Python supports a maximum of 32.
• The size and nature of your application also how database-intensive your application is.
• The size of the connection pool is configurable. You need to provide a connection pool size at the time of its creation. You cannot change the size once created.

The number of connection objects depends on the above factors.
For many cases, one connection per thread handling a single HTTP request is sufficient. Alternatively, you may need fewer if not every HTTP request needs to access the database. You can check the previous request history, analyze your application nature and then decide how to configure the connection pool.

How to use the connection pool in Python

Let’s see how to use a connection pool in Python in four simple steps: –

• Create a Connection Pool.
• Get Connection from a connection pool
• Perform some database operations on it
• Close the connection instance (return it to the connection pool)

Create a Connection pool in Python using MySQL Connector Python

Let see how to create a connection pool using the MySQL Connector Python module to handle the MySQL database from the Python application.
　
Use MySQL Connector Python’spooling.MySQLConnectionPoolandpooling.PooledMySQLConnectionclass to create and manage connection pools.

pooling.MySQLConnectionPool class constructor instantiates an object that manages a connection pool.

The syntax of creating a connection pool

from mysql.connector import pooling

try:
    connection_pool = pooling.MySQLConnectionPool(pool_name="pynative_pool",
                                                  pool_size=1,
                                                  pool_reset_session=True,
                                                  host='localhost',
                                                  database='python_db',
                                                  user='pynative',
                                                  password='pynative@#29')

　
As you can see using a MySQLConnectionPool class, we can create a connection pool. mysql.connector.pooling.MySQLConnectionPool return a connection pool object.
　
This class constructor takes pool_name, pool_size, database, username,and passwordfor creating a connection pool.
　
So let see how to configure a connection pool using these arguments.

Arguments required to create a connection pool

• pool_name: The pool name. As you can see, we have given a pynative_pool as a connection pool name. If this argument is not given, MySQL connector Python automatically sets the name using host, user, and database name. The application must create each pool with a different name.
• pool_size: a pool size is a number of the connection objects that the pool can support. If this argument is not given, the default is 5. The pool size cannot be 0 or less than 0.
• pool_reset_session: Reset session variables when the connection is returned to the pool.
• Auser,password, anddatabaseare additional connection arguments to connect MySQL.

Adds a new or existing MySQL Connection to the pool

Now, Let’s see how to get a connection object from the connection pool and add new connection objects to the connection pool.
　
Using aMySQLConnectionPool.add_connection()method we can add a new or existing MySQL Connection to the pool. If the pool is full OR exceeding its limit, it raises a PoolError.
　
Example of adding the MySQL Connection object to the pool.

connection_pool.add_connection(connection_obj= None)

Theconnection_objis the MySQL Connection object we want to add to the pool. If this argument is missing and the pool is not full, the pool creates a new connection and adds it.

Get Connection object from a connection pool

To request a connection from the pool, use MySQLConnectionPool.get_connection() method. Using this method application can get the connection object from a connection pool.

Example to get get connection object from a connection pool.

connection_objt = connection_pool.get_connection()

This method returns a connection from the pool. If all connections are in use or pool is empty it raises aPoolError.

Get Connection pool name

Use MySQLConnectionPool.pool_name property to get the pool name. Example to get the connection pool name:

pool_name  = connection_pool.pool_name

Change Configuration parameters for connections in the pool

Using MySQLConnectionPool.set_config()method we can set the configuration parameters for connections in the pool. i.e., we can reconfigure the connection object.
　
When this method is called pool configuration is overridden, any new connection requested from a connection pool starts using the new parameters mentioned in the MySQLConnectionPool.set_config() method.
　
Already running connections have no impact. Now, Let see how to use the pooled connection instance.

Manage pooled connection instance

Using the pooling.PooledMySQLConnection class, you can manage pooled connection instances. MySQLConnectionPool uses this class to return a pooled connection instance.

Get a pooled connection

Using the pooling.PooledMySQLConnection class constructor, we can get a pooled connection. This constructor takes connection pool and connection arguments and returns a pooled connection instance.
　
Example to get the pooled connection instance

pooled_connection = mysql.connector.pooling.PooledMySQLConnection(connection_pool, connection_object)

As you can see we have passed two arguments to get the pooled connection instance 　

• connection_pool: A MySQL Connection Pool instance. here in our case connection pool name ispynative_pool
• connection_object: A MySQL Connection instance.

Close the pooled MySQL connection instance

we can use a PooledMySQLConnection.close() method to close the pooled connection instance.
　
This method doesn’t close the connection but returns the connection to the connection pool to serve the next request. i.e., it returns a pooled connection to its connection pool.
　
Example to close the pooled connection instance.

pooled_connection = mysql.connector.pooling.PooledMySQLConnection(connection_pool, connection_object)

pooled_connection.close()

Python Example to Create, manage and use a Connection pool with MySQL

Let see how to use all the methods that I mentioned in this article.

from mysql.connector import Error
from mysql.connector import pooling

try:
    connection_pool = pooling.MySQLConnectionPool(pool_name="pynative_pool",
                                                  pool_size=5,
                                                  pool_reset_session=True,
                                                  host='localhost',
                                                  database='python_db',
                                                  user='pynative',
                                                  password='pynative@#29')

    print("Printing connection pool properties ")
    print("Connection Pool Name - ", connection_pool.pool_name)
    print("Connection Pool Size - ", connection_pool.pool_size)

    # Get connection object from a pool
    connection_object = connection_pool.get_connection()

    if connection_object.is_connected():
        db_Info = connection_object.get_server_info()
        print("Connected to MySQL database using connection pool ... MySQL Server version on ", db_Info)

        cursor = connection_object.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("Your connected to - ", record)

except Error as e:
    print("Error while connecting to MySQL using Connection pool ", e)
finally:
    # closing database connection.
    if connection_object.is_connected():
        cursor.close()
        connection_object.close()
        print("MySQL connection is closed")

Output:

Printing connection pool properties 
Connection Pool Name -  pynative_pool
Connection Pool Size -  5
Connected to MySQL database using connection pool ... MySQL Server version on  5.7.19
Your connected to -  ('python_db',)
MySQL connection is closed

Understand the connection pool example

As you can see in the first statement, we have imported two classes from MySQL Connector Python to create and manage the connection pool.

• Error: This class helps us to debug any database exception that may occur during this process.
• pooling: Using this class, we can create, manage and use the connection pool
• Also we set connection pool name to “pynative_pool” andpool size=5,pool_reset_session=True. Next, we Printed connection pool properties.
• After this, we got a connection object from a pool and executed a MySQL query to fetch the MySQL database name using a cursor object.
• In the end, we closed the connection instance.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.