In this lesson, you will learn a connection pool and how to implement a PostgreSQL database connection pool using Psycopg2 in Python.
Further Reading:
Table of contents
What is Connection Pool
PostgreSQL connection Pool is nothing but cached database connections created and maintained to get reused for coming requests instead of making the new connection every time.
There are various advantages of implementing and using a connection pool for your Python application while working with PostgreSQL. The primary benefit is time and performance improvements.
As you know, creating a PostgreSQL database connection is expensive, i.e., it is a resource-heavy and time-consuming process.
Using Connection Pooling, we can reduce the request and response time of database-centric applications in Python. Let see how to implement the connection pool in Python to work with a PostgreSQL database.
The Psycopg2 module provides four classes to manage a connection pool. i.e., It has ready-to-use classes to create and manage the connection pool directly. Alternatively, we can implement your connection pool implementation using its abstract class.
Psycopg2’s Connection pooling classes
The Psycopg2 module has the following four classes to manage the PostgreSQL connection pool.
AbstractConnectionPool
SimpleConnectionPool
ThreadedConnectionPool
PersistentConnectionPool
Note: The SimpleConnectionPool, ThreadedConnectionPool, PersistentConnectionPool are all subclass of AbstractConnectionPool class. and implement all methods defined in it.
Let see the use of each class separately.
AbstractConnectionPool
It is a base class implementing generic key-based pooling code.
A AbstractConnectionPool
is an abstract class. Subclass need to implements methods defined in it. If you want to create your custom implementation for the connection pool, you can extend this class and implement its methods.
psycopg2.pool.AbstractConnectionPool(minConnection, maxConnection, *args, **kwargs)
Code language: Python (python)
minConnection
: Minimum connection objects required.*args, **kwargs
are the necessary arguments for aconnect ()
method to establish a connection to the PostgreSQL database.
SimpleConnectionPool
It is a subclass of the AbstractConnectionPool class and implements methods defined in it. It is ready to use class for the connection pool.
This class is suitable only for single-threaded applications. i.e., If we create a connection pool using this class, then we can’t share this across different threads. So use this class to manage the connection pool only when you have a single-threaded application.
Syntax:
psycopg2.pool.SimpleConnectionPool(minConnection, maxConnection, *args, **kwargs)
Code language: Python (python)
ThreadedConnectionPool
It is also a subclass of the AbstractConnectionPool class and implements methods defined in it. Ready to use for the connection pool.
As the name suggests, this class used in a multithreaded environment. i.e., the connection pool created using this class can be shared between multiple threads.
Syntax and example:
psycopg2.pool.ThreadedConnectionPool(minConnection, maxConnection, *args, **kwargs)
Code language: Python (python)
PersistentConnectionPool
It is also a subclass of the AbstractConnectionPool class and implements methods defined in it.
This class is used in the multithread application, where a pool assigns persistent connections to different threads.
As the name suggests, each thread gets a single connection from the pool, i.e., the thread can’t use more than one connection from the pool.
This connection pool generates a key using thread ID.
Note: This pool class is intended to interact with Zope and probably not useful in typical applications.
Syntax and Example
psycopg2.pool.PersistentConnectionPool(minConnection, maxConnection, *args, **kwargs)
Code language: Python (python)
Now, Let see how to create a connection pool.
Methods to manage PostgreSQL connection Pool
The Psycopg2 module provides the following methods to manage the Connection pool.
getconn(key=None)
: To Get an available connection from the pool. Thekey
parameter is optional, and if used, the connection associated with the key will be returned. TheKey
parameter used inPersistentConnectionPool
class.putconn(connection, key=None, close=False)
: To Put away a connection. i.e., return a connection to the connection pool. If theclose
parameter is toTrue
, discard the connection from the pool. If akey
is used while creating the connection, then specify it while putting a connection.closeall()
: Close all the connections handled by the pool. This method closes all connections, including connections that are in use.
Create a PostgreSQL Connection Pool in Python
In this example, we are using a SimpleConnectionPool class to create a connection pool. Before creating a connection pool, let’s see the necessary arguments required to create a connection pool.
username
: the username that you use to work with PostgreSQL.password
:hostname
: Server name or Ip address on which PostgreSQL is running.database
: The name of the database to which you want to connect and perform the operationsminConnection
: The minimum PostgreSQL database connection object to create. i.e., the lower limit.maxConnection
: The maximum PostgreSQL database connection object to create and support. i.e., the upper limit.*args
,**kwargs
: are the arguments required to pass to aconnect()
method to create a connection object such as Host Name, User, Password, Database, Port.
Python Example to create and manage PostgreSQL Connection Pool
Let see how to use the SimpleConnectionPool
class to create and manage a PostgreSQL connection pool in Python.
Output:
Connection pool created successfully successfully recived connection from connection pool Displaying rows from mobile table (4, 'LG V30', 800.0) (5, 'iPhone 8 Plus', 750.0) (3, 'Samsung Galaxy S9', 850.0) (1, 'IPhone X', 1000.0) Put away a PostgreSQL connection PostgreSQL connection pool is closed
Let’s Understand connection pool example
postgreSQL_pool = psycopg2.pool.SimpleConnectionPool()
Code language: Python (python)
We passed the following values while creating a connection pool.
- Minimum connection = 1, i.e., create a minimum one connection at the time of the creation of a connection pool.
- Maximum Connection = 20, i.e., you can use a maximum 20 PostgreSQL connections.
- The
SimpleConnectionPool
class constructor returns us the connection pool instance.
ps_connection = postgreSQL_pool.getconn()
Code language: Python (python)
- Using a
getconn ()
method we requested a new connection from a connection pool. - After that, we executed database operations.
postgreSQL_pool.putconn(ps_connection)
Code language: Python (python)
The putconn()
method to release the connection object back to the connection pool.
postgreSQL_pool.closeall ()
Code language: Python (python)
In the end, we are closing all the active and passive connection objects.
Create a Threaded PostgreSQL Connection Pool in Python
Use the ThreadedConnectionPool
class To develop a connection pool that will work in a multithreading environment.
Output.
Connection pool created successfully using ThreadedConnectionPool successfully recived connection from connection pool Displaying rows from mobile table (4, 'LG V30', 800.0) (5, 'iPhone 8 Plus', 750.0) Put away a PostgreSQL connection Threaded PostgreSQL connection pool is closed
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.