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)

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.