Flask-sqlalchemy losing connection after restarting of DB server

Question

I use flask-sqlalchemy in my application. DB is postgresql 9.3. I have simple init of db, model and view:

from config import *
from flask import Flask, request, render_template
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://%s:%s@%s/%s' % (DB_USER, DB_PASSWORD, HOST, DB_NAME)
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    login = db.Column(db.String(255), unique=True, index=True, nullable=False)

db.create_all()
db.session.commit()

@app.route('/users/')
def users():
    users = User.query.all()
    return '1'

And all works fine. But when happens DB server restarting (sudo service postgresql restart), on first request to the /users/ I obtain sqlalchemy.exc.OperationalError:

OperationalError: (psycopg2.OperationalError) terminating connection due to administrator command
SSL connection has been closed unexpectedly
 [SQL: ....

Is there any way to renew connection inside view, or setup flask-sqlalchemy in another way for renew connection automatically?

UPDATE.

I ended up with using clear SQLAlchemy, declaring engine, metadata and db_session for every view, where I critically need it.

It is not solution of question, just a 'hack'.

So question is open. I am sure, It will be nice to find solution for this :)

Answer 1

The SQLAlchemy documentation explains that the default behaviour is to handle disconnects optimistically. Did you try another request - the connection should have re-established itself ? I've just tested this with a Flask/Postgres/Windows project and it works.

In a typical web application using an ORM Session, the above condition would correspond to a single request failing with a 500 error, then the web application continuing normally beyond that. Hence the approach is “optimistic” in that frequent database restarts are not anticipated.

If you want the connection state to be checked prior to a connection attempt you need to write code that handles disconnects pessimistically. The following example code is provided at the documentation:

from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        # optional - dispose the whole pool
        # instead of invalidating one at a time
        # connection_proxy._pool.dispose()

        # raise DisconnectionError - pool will try
        # connecting again up to three times before raising.
        raise exc.DisconnectionError()
    cursor.close()

Here's some screenshots of the event being caught in PyCharm's debugger:

Windows 7 (Postgres 9.4, Flask 0.10.1, SQLAlchemy 1.0.11, Flask-SQLAlchemy 2.1 and psycopg 2.6.1)

On first db request After db restart

Ubuntu 14.04 (Postgres 9.4, Flask 0.10.1, SQLAlchemy 1.0.8, Flask-SQLAlchemy 2.0 and psycopg 2.5.5)

On first db request After db restart

Answer 2

In plain SQLAlchemy you can add the pool_pre_ping=True kwarg when calling the create_engine function to fix this issue.

When using Flask-SQLAlchemy you can use the same argument, but you need to pass it as a dict in the engine_options kwarg:

app.db = SQLAlchemy(app, engine_options={"pool_pre_ping": True})