postgresql - Flask-sqlalchemy losing connection after restarting of DB server -

- January 15, 2011

i use flask-sqlalchemy in application. db postgresql 9.3. have simple init of db, model , view:

from config import * flask import flask, request, render_template 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 works fine. when happens db server restarting (sudo service postgresql restart), on first request /users/ obtain sqlalchemy.exc.operationalerror:

operationalerror: (psycopg2.operationalerror) terminating connection due administrator command ssl connection has been closed unexpectedly  [sql: ....

is there way renew connection inside view, or setup flask-sqlalchemy in way renew connection automatically?

update.

i ended using clear sqlalchemy, declaring engine, metadata , db_session every view, critically need it.

it not solution of question, 'hack'.

so question open. sure, nice find solution :)

the sqlalchemy documentation explains default behaviour handle disconnects optimistically. did try request - connection should have re-established ? i've tested flask/postgres/windows project , works.

in typical web application using orm session, above condition correspond single request failing 500 error, web application continuing beyond that. hence approach “optimistic” in frequent database restarts not anticipated.

if want connection state checked prior connection attempt need write code handles disconnects pessimistically. following example code provided @ documentation:

from sqlalchemy import exc sqlalchemy import event 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 whole pool         # instead of invalidating 1 @ time         # connection_proxy._pool.dispose()          # raise disconnectionerror - pool try         # connecting again 3 times before raising.         raise exc.disconnectionerror()     cursor.close()

here's screenshots of event being caught in pycharm's debugger:

windows 7 (postgres 9.4, flask 0.10.1, sqlalchemy 1.0.11, flask-sqlalchemy 2.1 , psycopg 2.6.1)

on first db request after db restart