Do I need to use SQLAlchemy sessions?

Question

The official tutorial for SQLAlchemy provides examples that make use of the session system, such as the following:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

Many unofficial tutorials also make use of sessions, however some don't make use of them at all, instead opting for whatever one would call this approach:

e = create_engine('sqlite:///company.db')
conn = e.connect()
query = conn.execute("SELECT first_name FROM employee")

Why are sessions needed at all when this much simpler system seems to do the same thing? The official documentation doesn't make it clear why this would be necessary, as far as I've seen.

There is one particularily relevant section in the official SQLAlchemy documentation:

A web application is the easiest case because such an application is already constructed around a single, consistent scope - this is the request, which represents an incoming request from a browser, the processing of that request to formulate a response, and finally the delivery of that response back to the client. Integrating web applications with the Session is then the straightforward task of linking the scope of the Session to that of the request. The Session can be established as the request begins, or using a lazy initialization pattern which establishes one as soon as it is needed. The request then proceeds, with some system in place where application logic can access the current Session in a manner associated with how the actual request object is accessed. As the request ends, the Session is torn down as well, usually through the usage of event hooks provided by the web framework. The transaction used by the Session may also be committed at this point, or alternatively the application may opt for an explicit commit pattern, only committing for those requests where one is warranted, but still always tearing down the Session unconditionally at the end.

...and...

Some web frameworks include infrastructure to assist in the task of aligning the lifespan of a Session with that of a web request. This includes products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, typically used with the Pyramid framework. SQLAlchemy recommends that these products be used as available.

Unfortunately I still can't tell whether I need to use sessions, or if the final paragraph is implying that certain implementation such as Flask-SQLAlchemy are already managing sessions automatically.

Do I need to use sessions? Is there a significant risk to not using sessions? Am I already using sessions because I'm using Flask-SQLAlchemy?

Answer 1

Like you pointed out, sessions are not strictly necessary if you only construct and execute queries using plain SQLAlchemy Core. However, they provide higher layer of abstraction required to take advantage of SQLAlchemy ORM. Session maintains a graph of modified models and makes sure the changes are efficiently and consistently flushed to the database when necessary.

Since you already use Flask-SQLAlchemy, I don't see a reason to avoid sessions even if you don't need the ORM features. The extension handles all the plumbing necessary for isolating requests, so that you don't have to reinvent the wheel and can focus on your application code.

Answer 2

You need sessions if you want to use ORM features including:

• Changing some attribute on an object returned by a query and having it easily written back to the database

• create an object using normal python constructors and have it easily sent back to the database

• Have relationships on objects. So, for example if you had a blog, and you had a post object, be able to write post.author to access the User object responsible for that post.

I'll note also that even if you use Session, you don't actually need sessionmaker. In a web application, you probably want it, but you can use sessions like this if you're looking for simplicity:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine(...)
session = Session(bind = engine)

Again, I think you probably want sessionmaker in your app, but you don't need it for sessions to work