CODEWITHSUNDEEP
pythonflaskpsycopg2
coffee.isGood
coffee.isGood

Reputation: 151

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

How should I use psycopg2 with Flask? I suspect it wouldn't be good to open a new connection every request so how can I open just one and make it globally available to the application?

from flask import Flask
app = Flask(__name__)
app.config.from_object('config') # Now we can access the configuration variables via app.config["VAR_NAME"].

import psycopg2


import myapp.views

Upvotes: 6

Views: 4487

Answers (1)

shoaib30
shoaib30

Reputation: 975

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 psycopg2
from psycopg2 import pool
from flask import Flask

app = 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

Upvotes: 2

Related Questions