psycopg2 flask implementation with connection pooling support

run.py

from flask import Flask, g, jsonify
import werkzeug, os
from werkzeug.utils import secure_filename

import psycopg2
from psycopg2 import pool

def get_db():
    print ('GETTING CONN')
    if 'db' not in g:
        g.db = app.config['postgreSQL_pool'].getconn()
    return g.db


def create_app():
    app = Flask(__name__)

    app.config['postgreSQL_pool'] = psycopg2.pool.SimpleConnectionPool(1, 20,user = "postgres",
                                                  password = "top_secret",
                                                  host = "127.0.0.1",
                                                  port = "9502",
                                                  database = "postgres")

    @app.teardown_appcontext
    def close_conn(e):
        print('CLOSING CONN')
        db = g.pop('db', None)
        if db is not None:
            app.config['postgreSQL_pool'].putconn(db)


    @app.route('/')
    def index():
        print ('ROUTE')
        db = get_db()
        cursor = db.cursor()

        cursor.execute("select 1;")
        result = cursor.fetchall()
        print (result)

        cursor.close()
        return jsonify(result)

    return app

if __name__ == '__main__':
    app = create_app()
    app.run(debug=True)

psycopg2 setup without SQL Alchemy.

Really basic. Needs work. Just a concept for: https://stackoverflow.com/a/54642162/2052575

Usage

Run the flask app with python run.py or gunicorn.
Hit it with: curl http://localhost:5000/

Console output:

 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
ROUTE
GETTING CONN
[(1,)]
CLOSING CONN
127.0.0.1 - - [12/Feb/2019 03:04:31] "GET / HTTP/1.1" 200 -

It seems the database connection does get closed when gunicorn or the python interpreter stop.

If you go by the pg_stat_activity table in postgres, you can test in various configurtions:

postgres=# SELECT count(*) FROM pg_stat_activity;
 count 
-------
     6
(1 row)

This is before the application is run with python at which point we see:

count: 7

It returns to 6 when you kill the process.

If you launch 2 application instances in one process with:

from werkzeug.serving import run_simple
from werkzeug.wsgi import DispatcherMiddleware

app = DispatcherMiddleware(create_app(),
    {'/asd':create_app()})
if __name__ == '__main__':
    # app=create_app()
    # app.run(port=5000)
    run_simple('localhost',5000, app,use_debugger=True,use_reloader=True ,use_evalex=True)

Then each python interpreter would have 2 database connections, then go ahead and launch with 16 gunicorn workers:

gunicorn --bind 0.0.0.0:5000 --workers 16 run:app --log-level=debug

That should make 6 + 2 * 16 Indeed:

count: 38

Which again returns to 6 when I kill the gunicorn process.

It would be interesting to find a similar method to monitor the pooler's health.

Does psycopg2.pool.SimpleConnectionPool implement this behaviour when the parent process is killed?

is there any specific reason SimpleConnectionPool is used and not its parent class AbstractConnectionPool? Also why is the cursor being manually closed, is that necessary? wouldn't putting away the connection in close_conn eliminate that?

Thanks for this! It did help me :)

Thanks for simple, but useful example. Just want to emphasize
Note: This pool class is useful only for single-threaded applications.
From psycopg2 documentation. So, it better to use ThreadedConnectionPool, i think.