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_requestdef 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 Flaskfrom flask_sqlalchemy import SQLAlchemydef 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 applicationif __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.