This article is part 8 of 11 in the series Python SQLAlchemy Tutorial

What are SQLAlchemy Sessions? What does the Session do?

One of the core concepts in SQLAlchemy is the Session. A Session establishes and maintains all conversations between your program and the databases. It represents an intermediary zone for all the Python model objects you have loaded in it. It is one of the entry points to initiate a query against the database, whose results are populated and mapped into unique objects within the Session. A unique object is the only object in the Session with a particular primary key.

A typical lifespan of a Session looks like this:

  • A Session is constructed, at which point it is not associated with any model objects.
  • The Session receives query requests, whose results are persisted / associated with the Session.
  • Arbitrary number of model objects are constructed and then added to the Session, after which point the Session starts to maintain and manage those objects.
  • Once all the changes are made against the objects in the Session, we may decide to commit the changes from the Session to the database or rollback those changes in the Session. Session.commit() means that the changes made to the objects in the Session so far will be persisted into the database while Session.rollback() means those changes will be discarded.
  • Session.close() will close the Session and its corresponding connections, which means we are done with the Session and want to release the connection object associated with it.

Understanding SQLAlchemy Sessions by Examples

Let's use a simple example to illustrate how to use Session to insert objects into the databases.

Create and Persist Session Objects

Once we have a session, we can create objects and add them to the session.

Let's insert another user Mary and inspect the new object's id at each step during the insertion process.

Notice that is None before s.commit() is called. Why? Because the object mary has not been committed to the database when it's constructed and added to s, it does not have a primary key assigned by the underlying SQLite database. Once the new object mary is committed by s, then it is assigned a id value by the underlying SQLite database.

Query Objects

Once we have both John and Mary in the database, we can query them using a Session.

As you can see, the queried objects have valid id values from the database.

Update Objects

We can change the name of Mary just like changing the attribute of a normal Python object, as long as we remember to call session.commit() at the end.

Delete Objects

Now we have two User objects persisted in the database, Mary and John. We are going to delete them by calling delete() of the session object.

Since Mary has been marked for deletion by the session and the deletion has been committed by the session into the database, we won't be able to find Mary in the database anymore.

Session Object States

Since we have already seen an Session object in action, it's important to also know the four different states of session objects:

  • Transient: an instance that's not included in a session and has not been persisted to the database.
  • Pending: an instance that has been added to a session but not persisted to a database yet. It will be persisted to the database in the next session.commit().
  • Persistent: an instance that has been persisted to the database and also included in a session. You can make a model object persistent by committing it to the database or query it from the database.
  • Detached: an instance that has been persisted to the database but not included in any sessions.

Let's use sqlalchemy.inspect to take a look at the states of a new User object david.

Notice the change of david's state progressing from Transient to Detached at each step of the insertion process. It's important to become familiar with these states of the objects because a slight misunderstanding may lead to hard-to-find bugs in a program.

Scoped Session vs. Normal Session

So far, the session object we constructed from the sessionmaker() call and used to communicate with our database is a normal session. If you call sessionmaker() a second time, you will get a new session object whose states are independent of the previous session. For example, suppose we have two session objects constructed in the following way:

Then, we won't be able to add the same User object to both s1 and s2 at the same time. In other words, an object can only be attached at most one unique session object.

If the session objects are retrieved from a scoped_session object, however, then we don't have such an issue since the scoped_session object maintains a registry for the same session object.

Notice that s1 and s2 are the same session object since they are both retrieved from a scoped_session object who maintains a reference to the same session object.

Summary and Tips

In this article, we reviewed how to use SQLAlchemy's Session and the four different states of a model object. Since unit-of-work is a core concept in SQLAlchemy, it's crucial to fully understand and be familiar with how to use Session and the four different states of a model object. In the next article, we will show you how to utilize the Session to manage complex model objects and avoid common bugs.

About The Author

  • Tumen

    Thanks very much for your job!

  • Seth Williams

    Hey Xiaonou, Excellent work. This is a thorough tutorial for all python users.If you want to explore more, you can visit our python course curriculum here

  • DE. G

    Very helpful. Thanks!