An example of connection pooling in PostgreSQL using Psycopg2

In this article we will explore the concept of connection pool and how to implement it for a PostgreSQL database in Python with Psycopg2. A connection pool is a cached database connection that is created and maintained so that you do not need to recreate it for new queries. Implementing and using connection pooling in a Python application that works with a PostgreSQL database provides several benefits. The main one is improved time and performance. As you know, creating a connection to a PostgreSQL database is a resource- and time-consuming operation. And with pooling, you can reduce the query and response time for database applications in Python. Let’s look at how to implement it. The psycopg2 module has 4 classes to manage connection pooling. You can use them to easily create and manage a pool. Alternatively, the same result can be achieved by implementing an abstract class.

The psycopg2 connection pool management classes

The psycopg2 module has four classes for connection pool management:

• AbstractConnectionPool.
• SimpleConnectionPool.
• ThreadedConnectionPool.
• PersistentConnectionPool.

Note: SimpleConnectionPool, ThreadedConnectionPool, and PersistentConnectionPool are subclasses of AbstractConnectionPool and implement methods from it. Let’s look at each of them individually.

AbstractConnectionPool

This is a base class that implements a generalized key-based pool code.

psycopg2.pool.AbstractConnectionPool(minconn, maxconn, *args, **kwargs)

AbstractConnectionPool is an abstract class. The inherited ones must implement the methods declared in it. If you want to create your own implementation of the connection pool, you need to inherit from it and implement those methods. minconn is the minimum required number of connection objects. * args, *kwargs – arguments you need for the connect() method which is responsible for connecting to a PostgreSQL database.

SimpleConnectionPool

This is a subclass of AbstractConnectionPool that implements its methods. It can already be used for a connection pool. This class is only suitable for single-threaded applications. This means that if you want to create a connection pool using this class, it cannot be passed between threads. Syntax:

psycopg2.pool.SimpleConnectionPool(minconn, maxconn, *args, **kwargs)

ThreadedConnectionPool

It is also a subclass of the AbstractConnectionPool class and implements its methods. This class is used in a multithreaded environment, i.e. a pool created with this class can be shared between multiple threads.

psycopg2.pool.ThreadedConnectionPool(minconn, maxconn, *args, **kwargs)

PersistentConnectionPool

Another subclass of AbstractConnectionPool that implements its methods. This class is used in multithreaded applications with a pool that distributes persistent connections to different threads. As the name suggests, each thread receives one connection from the pool. Thus, one thread can have no more than one connection from the pool. The connection pool generates a key using the thread ID. This means that for each thread, the connection does not change when called. Note: this class is mainly designed to interact with Zope and is probably not suitable for normal applications. Syntax:

psycopg2.pool.PersistentConnectionPool(minconn, maxconn, *args, **kwargs)

Let’s see how to create a connection pool.

Psycopg2 methods for connection pool management

The following methods are presented in the Psycopg2 module and are used to manage it.

• getconn(key=None) – to get an available connection from the pool. The key parameter is optional. When this parameter is used, getconn() returns the connection associated with this key. Key is used in the PersistentConnectionPool class.
• putconn(connection, key=None, close=False) – to return the connection back to the pool. If the close parameter is True, the connection is also removed from the pool. If a key was used to receive the connection, the same key must be passed when returning the connection.
• closeall() – closes all used pool connections.

Creating a connection pool using psycopg2

This example uses the SimpleConnectionPool to create a connection pool. Before we do this, it is worth looking at the arguments that are required to make it work. We need to specify the minimum and maximum number of connections, username, password, host, and database.

• minconn is the lower limit of the number of connections.
• maxconn is the maximum number of possible connections.
• *args, **kwargs – arguments for the connect() method, required to create a connection object. Here you need to specify hostname, user, password, database and port.

Example of creating and managing a PostgreSQL connection pool

Let’s see how to use the SimpleConnectionPool class to create and manage a connection pool from Python.

import psycopg2
from psycopg2 import pool
try:
# Connect to an existing database
postgresql_pool = psycopg2.pool.SimpleConnectionPool(1, 20,
user="postgres",
# password you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
if postgresql_pool:
print("The connection pool was created successfully.")
# Use getconn() to get a connection from the connection pool.
connection = postgresql_pool.getconn()
if connection:
print("Connection established")
cursor = connection.cursor()
cursor.execute("select * from mobile")
mobile_records = cursor.fetchall()
print ("Display rows from the mobile table")
for row in mobile_records:
print(row)
cursor.close()
# use this method to let go of the connection object
# and send it back to the connection pool
postgresql_pool.putconn(connection)
print("PostgreSQL connection returned to the pool")
except (Exception, psycopg2.DatabaseError) as error :
print ("Error connecting to PostgreSQL", error)
finally:
if postgresql_pool:
postgresql_pool.closeall
print("PostgreSQL connection pool closed")

Conclusion:

Connection pool created successfully
Connection established
Display rows from the mobile table
(1, 'IPhone 12', 800.0)
(2, 'Google Pixel 2', 900.0)
PostgreSQL connection returned to the connection pool
PostgreSQL connection pool is closed
Let's break down an example. The following values were passed to the method:


 	
Minimum connection = 1. This means that at least one connection is created when the pool is created.
 	
Maximum connection = 20. You can have a total of 20 connections to PostgreSQL.
 	
Other connection parameters.
 	
The constructor of the SimpleConnectionPool class returns an instance of the pool.
 	
Using getconn() a connection request is made from the pool
 	
After that, the database operations are performed.
 	
And at the end, all active and passive connection objects are closed to close the application.

Creating a multithreaded connection pool
Let's create a connection pool that will work in a multithreaded environment. This can be done with the ThreadedConnectionPool class.
import psycopg2
from psycopg2 import pool
try:
# Connect to an existing database
postgresql_pool = psycopg2.pool.ThreadedConnectionPool(5, 20,
user="postgres",
# password you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
if postgresql_pool:
print("The connection pool was created successfully.")
# Use getconn() to get a connection from the connection pool.
connection = postgresql_pool.getconn()
if connection:
print("Connection established")
cursor = connection.cursor()
cursor.execute("select * from mobile")
mobile_records = cursor.fetchall()
print ("Display rows from the mobile table")
for row in mobile_records:
print(row)
cursor.close()
# use this method to let go of the connection object
# and send it back to the connection pool
postgresql_pool.putconn(connection)
print("PostgreSQL connection returned to the pool")
except (Exception, psycopg2.DatabaseError) as error :
print ("Error connecting to PostgreSQL", error)
finally:
if postgresql_pool:
postgresql_pool.closeall
print("PostgreSQL connection pool closed")
 
The output will be the same: 
 
 
 
 You Might Be Interested In
 
 
 Seaborn for data visualization in Python 
 
 Django Blog #25: Adding Comments to a Post Template 
 
 Examples of working with Python lists 
 
 The Complete SQLAlchemy Manual 
 
 Creating a schema in SQLAlchemy ORM 
 
 Examples of working with classes in Python