CODEWITHSUNDEEP
pythonpython-2.7sqlalchemyflask-sqlalchemy
KBN
KBN

Reputation: 2974

How to handle multiple requests without SQLAlchemy crashing/raising exceptions?

Context: I'm working on a Flask app, running on CherryPy, DB handled using SQLAlchemy ORM.

Problem:

The app runs fine and does everything I want, however, if I have a page which fetches some data from DB and displays, and I press and hold "Ctrl + R" or "F5". That is, continuously refresh a page, making that many DB requests. First few goes fine, and then it breaks. The following errors are logged:

(OperationalError) (2013, 'Lost connection to MySQL server during query')

Can't reconnect until invalid transaction is rolled back (original cause: 
InvalidRequestError: Can't reconnect until invalid transaction is rolled back)

This result object does not return rows. It has been closed automatically.

(ProgrammingError) (2014, "Commands out of sync; you can't run this command now")

There's also another error which bothers me (but not logged this time), it's

dictionary changed size during iteration

This happens when I'm iterating through a query, using values obtained to populate a dictionary. The dictionary is local (scope of the dict) to the function.

More info:

How I am handling sessions:

A new session is created when you enter any page, use that session to perform all the DB transactions, and the session is closed right before rendering the HTML. Technically, that means, the scope of session is the same as the HTTP request.

I do a session.rollback() only when there's an exception raised during updating table or inserting into a table. No rollback() during any query() operations. I'm pretty sure I've made some silly mistakes or am not doing things the right way.

Unlimited refreshes like that is not really a probably scenario, but can't be overlooked. Also, I think the behavior would be similar when there a lot of users using it at the same time.

How the SQLAlchemy engine, sessionmaker was handled:

sql_alchemy_engine = create_engine(self.db_string, echo=False, encoding="utf8", convert_unicode=True, pool_recycle=9)
sqla_session = sessionmaker(bind=sql_alchemy_engine)

It's done only ONCE like it's recommended in the SQLA documentation, and a new session is created and returned sqla_session() whenever required.

Upvotes: 3

Views: 3056

Answers (1)

Pedro Werneck
Pedro Werneck

Reputation: 41898

If you're using Flask, you should be using flask-sqlalchemy, and let the Flask request context manage your session, and not handling your engine and sessions by hand. This is how SQLAlchemy recommends it:

Most web frameworks include infrastructure to establish a single Session, associated with the request, which is correctly constructed and torn down corresponding torn down at the end of a request. Such infrastructure pieces include products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, for usage in conjunction with the Pyramid and Zope frameworks. SQLAlchemy strongly recommends that these products be used as available.

http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html?highlight=flask

Then you create your engine simply by:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'your db uri'

db = SQLAlchemy(app)

Or, if you're using app factory:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'your db uri'

    db.init_app(app)

With that, the base declarative model you should be using will be at db.Model and the session you should be using will be at db.session.

Upvotes: 3

Related Questions