On Fri, Oct 4, 2013 at 3:47 PM, Russell Holloway <[email protected]> wrote: > Hello all, > > I am trying to migrate some custom ORM code to use SQLAlchemy instead for > database interactions. I'm having some issues with proper session > management. The main issue that seems to occur is the operationalerror > 'mysql has gone away' with every now and then one comes up StatementError > "can't reconnect until invalid transaction is rolled back (original cause: > InvalidRequestError)". > > I am aware that others have had issues with the first error on long standing > connections. For example, if someone creates a session when python script > initially loaded and tries to reuse much later, the error occurs. There is > scoped_session which should be tied to the specific request (Flask) and > solve the issue if a session is created and closed within the route. > > The problem comes with trying to wrap the code in such a way that it aligns > with previous code. With SQLAlchemy, you call > > session.query(ClassName).get(some_stuff) > > I would like to use the structure below instead: > > ClassName.get(some_stuff) > > To do so, ClassName inherits from a base class that creates the get() > method, which in turn converts to the SQLAlchemy format (and similar for > filter, update, and similar functions). A simple example would be > > @classmethod > def get(cls, arg): > return session.query(cls).get(arg) > > Does anyone have any recommendations on how to best set that session > variable though to use within the base SQLAlchemy class, so that it is > scoped to a request? Is it possible? I suppose I could modify all the > functions and pass in a session variable as well, so from within Flask route > the user would create a (scoped) session and pass it in to all the class > methods (ClassName.get(session, arg)), but that changes syntax and isn't > quite as clear. I thought I would create a custom Session class with a > global session var that a user could set during route function (and then use > that static variable within get() function), but then as multiple Flask > users interact, the static variable would be overwritten by others > prematurely. > > I know there is Flask-SQLAlchemy, and it provides a *close* syntax such as > User.query.get() through the query attribute. Does anyone know how it does > the session management behind the scenes to keep them sane? I am looking for > pure SQLAlchemy solutions, since the API is used both through Flask (web) > and standard python. > >
As long as Flask is using a separate thread for each request, then scoped_session basically does all the hard work for you. You want something like this at module-level: session = scoped_session(sessionmaker()) and just use that global session everywhere. scoped_session will make sure that separate threads will have separate sessions. However, you do need to make sure that the session is cleaned up properly at the end of each request. I don't know Flask so I don't know where you would put this, but you basically need to make sure that "session.close()" is called at the end of every request. Typically you would have something that runs at the end of the request, calls session.commit() if there were no errors, or session.rollback() if there were, followed by session.close(). This removes the real session from the scoped_session's registry, so that when this thread services another request, it gets a fresh session. As for your other problem (mysql has gone away), that is typically solved by passing the pool_recycle=n parameter to create_engine. This tells SQLAlchemy not to use a database connection that is older than n seconds. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
