Flask-SQLAlchemy: Can't reconnect until invalid transaction is rolled back

I think what did it was adding

db.init_app(application)

in application.py, haven't had the error since.

Everytime checking rollback or not is troublesome..

I made insert, update functions which need commit.

@app.teardown_request
def session_clear(exception=None):
    Session.remove()
    if exception and Session.is_active:
        Session.rollback()

It seems not to be a problem with the transactions at the first place, but this is probably caused by an MySQL Error like Connection reset by peer beforehand. That means your connection is lost, probably because your application context was not setup correctly.

In general it is preferrable to use the factory pattern to create your app. This has a lot of advantages, your code is

• easier to read and setup
• easier to test
• avoid circular imports

To prevent the invalid transaction error (that is probably caused by an OperationalError: Connection reset by peer) you should ensure that you are handling the database connection right.

The following example is based on this article which gives a nice explanation of the flask application context and how to use it with database connections or any other extensions.

application.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

def create_app():
    """Construct the core application."""
    application = Flask(__name__)
    application.config.from_object('config')    # Set globals
    db = SQLAlchemy()

    with application.app_context():
        # Initialize globals/extensions in app context
        db.init_app(app)        

        # import routes here
        from . import routes

    return application


if __name__ == "__main__":
   app = create_app()
   app.run(host="0.0.0.0")

routes.py

from flask import current_app as application

@application.route('/', methods=['GET'])
def index():
   return "Hello, World!"

If you still run into disconnect-problems you should also check the SQLAlchemy documentation on dealing with disconnects and have a look at this question.