Skip to content

Instantly share code, notes, and snippets.

@vulcan25
Last active January 11, 2023 13:52
Show Gist options
  • Save vulcan25/55ce270d76bf78044d067c51e23ae5ad to your computer and use it in GitHub Desktop.
Save vulcan25/55ce270d76bf78044d067c51e23ae5ad to your computer and use it in GitHub Desktop.
psycopg2 flask implementation with connection pooling support
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)
@vulcan25
Copy link
Author

vulcan25 commented Feb 12, 2019

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 -

@vulcan25
Copy link
Author

vulcan25 commented Feb 12, 2019

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?

@Directory
Copy link

Directory commented Dec 15, 2020

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?

@vcoopman
Copy link

Thanks for this! It did help me :)

@2uger
Copy link

2uger commented Oct 10, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment