Sunday, October 23, 2016

Is it necessary to run SQLAlchemy's session.remove()?

If you're using flask-sqlalchemy, then the answer is no - it's already doing it for you when the request finishes: https://github.com/mitsuhiko/flask-sqlalchemy/blob/2.1/flask_sqlalchemy/__init__.py#L823

If you're using scoped_session and not explicitly running session.remove(), then your connections will only be returned to the pool after your thread finishes and garbage collection occurs. It's not a good idea to leave this to garbage collection, because you can't guarantee your connections will be returned to the pool when your application is busy. This will often lead to errors like this: "TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30"

The solution is to make sure session.remove() runs when the work finishes (like flask-sqlalchemy does), as described here: http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-thread-local-scope-with-web-applications

Another alternative is not making the session global and using a context manager, as described at the bottom of this section: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it However, this can make things difficult when you need to access the query results outside of the context manager. If you access the query results outside of the context manager without running session.expunge_all(), you'll see all kinds of errors like this: "DetachedInstanceError: Instance <ReportingJob at 0xa41cd8c> is not bound to a Session; attribute refresh operation cannot proceed"

Another option is setting autocommit to True, but that has quite a few gotchas. Turning on autocommit will acquire connections from the engine on an as-needed basis and return them immediately after their use. You will have to explicitly start transactions with session.begin() if you enable it. It seems like this setting should be called "autotransaction" instead. It's also not very efficient, because it needs to get and return a connection each time you run a query.

No comments:

Post a Comment