How to use connection pooling with psycopg2 (postgresql) with Flask

Using connection Pooling is needed with Flask or any web server, as you rightfully mentioned, it is not wise to open and close connections for every request.

psycopg2 offers connection pooling out of the box. The AbstractConnectionPool class which you can extend and implement or a SimpleConnectionPool class that can be used out of the box. Depending on how you run the Flask App, you may what to use ThreadedConnectionPool which is described in docs as

A connection pool that works with the threading module.

Creating a simple Flask app and adding a ConnectionPool to it

import psycopg2from psycopg2 import poolfrom flask import Flaskapp = Flask(__name__)postgreSQL_pool = psycopg2.pool.SimpleConnectionPool(1, 20, user="postgres",                                                         password="pass@#29",                                                         host="127.0.0.1",                                                         port="5432",                                                         database="postgres_db")@app.route('/')def hello_world():    # Use getconn() to Get Connection from connection pool    ps_connection = postgreSQL_pool.getconn()    # use cursor() to get a cursor as normal    ps_cursor = ps_connection.cursor()    #     # use ps_cursor to interact with DB    #    # close cursor    ps_cursor.close()    # release the connection back to connection pool    postgreSQL_pool.putconn(ps_connection)    return 'Hello, World!'

The Flask App itself is not complete or production-ready, please follow the instructions on Flask Docs to manage DB credentials and use the Pool object across the Flask App within the Flask context

I would strongly recommend using Libraries such as SQLAlchemy along with Flask (available as a wrapper) which will maintain connections and manage the pooling for you. Allowing you to focus on your logic