Python postgresql connection pool

Python PostgreSQL Connection Pooling Using Psycopg2

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)

• minConnection : Minimum connection objects required.
• *args, **kwargs are the necessary arguments for a connect () 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.

psycopg2.pool.SimpleConnectionPool(minConnection, maxConnection, *args, **kwargs)

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)

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)

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. The key parameter is optional, and if used, the connection associated with the key will be returned. The Key parameter used in PersistentConnectionPool class.
• putconn(connection, key=None, close=False) : To Put away a connection. i.e., return a connection to the connection pool. If the close parameter is to True , discard the connection from the pool. If a key 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 operations
• minConnection : 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 a connect() 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.

import psycopg2 from psycopg2 import pool try: postgreSQL_pool = psycopg2.pool.SimpleConnectionPool(1, 20, user="postgres", password="pass@#29", host="127.0.0.1", port="5432", database="postgres_db") if (postgreSQL_pool): print("Connection pool created successfully") # Use getconn() to Get Connection from connection pool ps_connection = postgreSQL_pool.getconn() if (ps_connection): print("successfully recived connection from connection pool ") ps_cursor = ps_connection.cursor() ps_cursor.execute("select * from mobile") mobile_records = ps_cursor.fetchall() print("Displaying rows from mobile table") for row in mobile_records: print(row) ps_cursor.close() # Use this method to release the connection object and send back to connection pool postgreSQL_pool.putconn(ps_connection) print("Put away a PostgreSQL connection") except (Exception, psycopg2.DatabaseError) as error: print("Error while connecting to PostgreSQL", error) finally: # closing database connection. # use closeall() method to close all the active connection if you want to turn of the application if postgreSQL_pool: postgreSQL_pool.closeall print("PostgreSQL connection pool is closed") 

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()

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()

• Using a getconn () method we requested a new connection from a connection pool.
• After that, we executed database operations.

postgreSQL_pool.putconn(ps_connection)

The putconn() method to release the connection object back to the connection pool.

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.

import psycopg2 from psycopg2 import pool try: threaded_postgreSQL_pool = psycopg2.pool.ThreadedConnectionPool(5, 20, user="postgres", password="pass@#29", host="127.0.0.1", port="5432", database="postgres_db") if (threaded_postgreSQL_pool): print("Connection pool created successfully using ThreadedConnectionPool") # Use getconn() method to Get Connection from connection pool ps_connection = threaded_postgreSQL_pool.getconn() if (ps_connection): print("successfully recived connection from connection pool ") ps_cursor = ps_connection.cursor() ps_cursor.execute("select * from mobile") mobile_records = ps_cursor.fetchmany(2) print("Displaying rows from mobile table") for row in mobile_records: print(row) ps_cursor.close() # Use this method to release the connection object and send back ti connection pool threaded_postgreSQL_pool.putconn(ps_connection) print("Put away a PostgreSQL connection") except (Exception, psycopg2.DatabaseError) as error: print("Error while connecting to PostgreSQL", error) finally: # closing database connection. # use closeall() method to close all the active connection if you want to turn of the application if threaded_postgreSQL_pool: threaded_postgreSQL_pool.closeall print("Threaded PostgreSQL connection pool is closed") 

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.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

psycopg2.pool – Connections pooling¶

Creating new PostgreSQL connections can be an expensive operation. This module offers a few pure Python classes implementing simple connection pooling directly in the client application.

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

Base class implementing generic key-based pooling code.

New minconn connections are created automatically. The pool will support a maximum of about maxconn connections. *args and **kwargs are passed to the connect() function.

The following methods are expected to be implemented by subclasses:

Get a free connection from the pool.

The key parameter is optional: if used, the connection will be associated to the key and calling getconn() with the same key again will return the same connection.

putconn ( conn , key = None , close = False ) ¶

If close is True , discard the connection from the pool. key should be used consistently with getconn() .

Close all the connections handled by the pool.

Note that all the connections are closed, including ones eventually in use by the application.

The following classes are AbstractConnectionPool subclasses ready to be used.

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

A connection pool that can’t be shared across different threads.

This pool class is useful only for single-threaded applications.

A connection pool that works with the threading module.

This pool class can be safely used in multi-threaded applications.

Источник