In this lesson, you will learn how to implement a MySQL connection pool in 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 the
mysql.connector.pooling class 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 the MySQL connector Python module to implement connection pooling.
Table of contents
- What is Connection Pooling in Python
- Configuring the connection pool in Python with MySQL
- Create a Connection pool in Python using MySQL Connector Python
- Manage pooled 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.
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 following factors: –
- 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’s
pooling.PooledMySQLConnection class 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 password for 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.
databaseare 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.
MySQLConnectionPool.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_obj is 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 a
Get Connection pool name
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
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
pooling.PooledMySQLConnection class, you can manage pooled connection instances. MySQLConnectionPool uses this class to return a pooled connection instance.
Get a pooled connection
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 is pynative_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")
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” and
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.
To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.