Essential SQLAlchemy, 2nd Edition

Chapter 7. Working with Data via SQLAlchemy ORM

Now that we have defined classes that represent the tables in our database and persisted them, let’s start working with data via those classes. In this chapter, we’ll look at how to insert, retrieve, update, and delete data. Then we’ll learn how to sort and group that data and take a look at how relationships work. We’ll begin by learning about the SQLAlchemy session, one of the most important parts of the SQLAlchemy ORM.

The Session

The session is the way SQLAlchemy ORM interacts with the database. It wraps a database connection via an engine, and provides an identity map for objects that you load via the session or associate with the session. The identity map is a cache-like data structure that contains a unique list of objects determined by the object’s table and primary key. A session also wraps a transaction, and that transaction will be open until the session is committed or rolled back, very similar to the process described in “Transactions”.

To create a new session, SQLAlchemy provides the sessionmaker class to ensure that sessions can be created with the same parameters throughout an application. It does this by creating a Session class that has been configured according to the arguments passed to the sessionmaker factory. The sessionmaker factory should be used just once in your application global scope, and treated like a configuration setting. Let’s create a new session associated with an in-memory SQLite database: