SQLAlchemy 1.4 / 2.0 Tutorial

This page is part of the SQLAlchemy 2.0 Tutorial.

Previous: Data Manipulation with the ORM | Next: Further Reading

Working with Related Objects

In this section, we will cover one more essential ORM concept, which is how the ORM interacts with mapped classes that refer to other objects. In the section Declaring Mapped Classes, the mapped class examples made use of a construct called _orm.relationship(). This construct defines a linkage between two different mapped classes, or from a mapped class to itself, the latter of which is called a self-referential relationship.

To describe the basic idea of _orm.relationship(), first we’ll review the mapping in short form, omitting the _schema.Column mappings and other directives:

from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = 'user_account'

    # ... Column mappings

    addresses = relationship("Address", back_populates="user")


class Address(Base):
    __tablename__ = 'address'

    # ... Column mappings

    user = relationship("User", back_populates="addresses")

Above, the User class now has an attribute User.addresses and the Address class has an attribute Address.user. The _orm.relationship() construct will be used to inspect the table relationships between the _schema.Table objects that are mapped to the User and Address classes. As the _schema.Table object representing the address table has a _schema.ForeignKeyConstraint which refers to the user_account table, the _orm.relationship() can determine unambiguously that there is a one to many relationship from User.addresses to User; one particular row in the user_account table may be referred towards by many rows in the address table.

All one-to-many relationships naturally correspond to a many to one relationship in the other direction, in this case the one noted by Address.user. The :paramref:`_orm.relationship.back_populates` parameter, seen above configured on both _orm.relationship() objects referring to the other name, establishes that each of these two _orm.relationship() constructs should be considered to be complimentary to each other; we will see how this plays out in the next section.

Persisting and Loading Relationships

We can start by illustrating what _orm.relationship() does to instances of objects. If we make a new User object, we can note that there is a Python list when we access the .addresses element:

>>> u1 = User(name='pkrabs', fullname='Pearl Krabs')
>>> u1.addresses
[]

This object is a SQLAlchemy-specific version of Python list which has the ability to track and respond to changes made to it. The collection also appeared automatically when we accessed the attribute, even though we never assigned it to the object. This is similar to the behavior noted at Inserting Rows with the ORM where it was observed that column-based attributes to which we don’t explicitly assign a value also display as None automatically, rather than raising an AttributeError as would be Python’s usual behavior.

As the u1 object is still transient and the list that we got from u1.addresses has not been mutated (i.e. appended or extended), it’s not actually associated with the object yet, but as we make changes to it, it will become part of the state of the User object.

The collection is specific to the Address class which is the only type of Python object that may be persisted within it. Using the list.append() method we may add an Address object:

>>> a1 = Address(email_address="[email protected]")
>>> u1.addresses.append(a1)

At this point, the u1.addresses collection as expected contains the new Address object:

>>> u1.addresses
[Address(id=None, email_address='[email protected]')]

As we associated the Address object with the User.addresses collection of the u1 instance, another behavior also occurred, which is that the User.addresses relationship synchronized itself with the Address.user relationship, such that we can navigate not only from the User object to the Address object, we can also navigate from the Address object back to the “parent” User object:

>>> a1.user
User(id=None, name='pkrabs', fullname='Pearl Krabs')

This synchronization occurred as a result of our use of the :paramref:`_orm.relationship.back_populates` parameter between the two _orm.relationship() objects. This parameter names another _orm.relationship() for which complementary attribute assignment / list mutation should occur. It will work equally well in the other direction, which is that if we create another Address object and assign to its Address.user attribute, that Address becomes part of the User.addresses collection on that User object:

>>> a2 = Address(email_address="[email protected]", user=u1)
>>> u1.addresses
[Address(id=None, email_address='[email protected]'), Address(id=None, email_address='[email protected]')]

We actually made use of the user parameter as a keyword argument in the Address constructor, which is accepted just like any other mapped attribute that was declared on the Address class. It is equivalent to assignment of the Address.user attribute after the fact:

# equivalent effect as a2 = Address(user=u1)
>>> a2.user = u1

Cascading Objects into the Session

We now have a User and two Address objects that are associated in a bidirectional structure in memory, but as noted previously in Inserting Rows with the ORM , these objects are said to be in the transient state until they are associated with a _orm.Session object.

We make use of the _orm.Session that’s still ongoing, and note that when we apply the _orm.Session.add() method to the lead User object, the related Address object also gets added to that same _orm.Session:

>>> session.add(u1)
>>> u1 in session
True
>>> a1 in session
True
>>> a2 in session
True

The above behavior, where the _orm.Session received a User object, and followed along the User.addresses relationship to locate a related Address object, is known as the save-update cascade and is discussed in detail in the ORM reference documentation at Cascades.

The three objects are now in the pending state; this means they are ready to be the subject of an INSERT operation but this has not yet proceeded; all three objects have no primary key assigned yet, and in addition, the a1 and a2 objects have an attribute called user_id which refers to the _schema.Column that has a _schema.ForeignKeyConstraint referring to the user_account.id column; these are also None as the objects are not yet associated with a real database row:

>>> print(u1.id)
None
>>> print(a1.user_id)
None

It’s at this stage that we can see the very great utility that the unit of work process provides; recall in the section INSERT usually generates the “values” clause automatically, rows were inserted into the user_account and address tables using some elaborate syntaxes in order to automatically associate the address.user_id columns with those of the user_account rows. Additionally, it was necessary that we emit INSERT for user_account rows first, before those of address, since rows in address are dependent on their parent row in user_account for a value in their user_id column.

When using the _orm.Session, all this tedium is handled for us and even the most die-hard SQL purist can benefit from automation of INSERT, UPDATE and DELETE statements. When we _orm.Session.commit() the transaction all steps invoke in the correct order, and furthermore the newly generated primary key of the user_account row is applied to the address.user_id column appropriately:

>>> session.commit()
{opensql}INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('pkrabs', 'Pearl Krabs')
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('[email protected]', 6)
INSERT INTO address (email_address, user_id) VALUES (?, ?)
[...] ('[email protected]', 6)
COMMIT

Loading Relationships

In the last step, we called _orm.Session.commit() which emitted a COMMIT for the transaction, and then per :paramref:`_orm.Session.commit.expire_on_commit` expired all objects so that they refresh for the next transaction.

When we next access an attribute on these objects, we’ll see the SELECT emitted for the primary attributes of the row, such as when we view the newly generated primary key for the u1 object:

>>> u1.id
{opensql}BEGIN (implicit)
SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
user_account.fullname AS user_account_fullname
FROM user_account
WHERE user_account.id = ?
[...] (6,){stop}
6

The u1 User object now has a persistent collection User.addresses that we may also access. As this collection consists of an additional set of rows from the address table, when we access this collection as well we again see a lazy load emitted in order to retrieve the objects:

>>> u1.addresses
{opensql}SELECT address.id AS address_id, address.email_address AS address_email_address,
address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
[...] (6,){stop}
[Address(id=4, email_address='[email protected]'), Address(id=5, email_address='[email protected]')]

Collections and related attributes in the SQLAlchemy ORM are persistent in memory; once the collection or attribute is populated, SQL is no longer emitted until that collection or attribute is expired. We may access u1.addresses again as well as add or remove items and this will not incur any new SQL calls:

>>> u1.addresses
[Address(id=4, email_address='[email protected]'), Address(id=5, email_address='[email protected]')]

While the loading emitted by lazy loading can quickly become expensive if we don’t take explicit steps to optimize it, the network of lazy loading at least is fairly well optimized to not perform redundant work; as the u1.addresses collection was refreshed, per the identity map these are in fact the same Address instances as the a1 and a2 objects we’ve been dealing with already, so we’re done loading all attributes in this particular object graph:

>>> a1
Address(id=4, email_address='[email protected]')
>>> a2
Address(id=5, email_address='[email protected]')

The issue of how relationships load, or not, is an entire subject onto itself. Some additional introduction to these concepts is later in this section at Loader Strategies.

Using Relationships in Queries

The previous section introduced the behavior of the _orm.relationship() construct when working with instances of a mapped class, above, the u1, a1 and a2 instances of the User and Address classes. In this section, we introduce the behavior of _orm.relationship() as it applies to class level behavior of a mapped class, where it serves in several ways to help automate the construction of SQL queries.

Using Relationships to Join

The sections Explicit FROM clauses and JOINs and Setting the ON Clause introduced the usage of the _sql.Select.join() and _sql.Select.join_from() methods to compose SQL JOIN clauses. In order to describe how to join between tables, these methods either infer the ON clause based on the presence of a single unambiguous _schema.ForeignKeyConstraint object within the table metadata structure that links the two tables, or otherwise we may provide an explicit SQL Expression construct that indicates a specific ON clause.

When using ORM entities, an additional mechanism is available to help us set up the ON clause of a join, which is to make use of the _orm.relationship() objects that we set up in our user mapping, as was demonstrated at Declaring Mapped Classes. The class-bound attribute corresponding to the _orm.relationship() may be passed as the single argument to _sql.Select.join(), where it serves to indicate both the right side of the join as well as the ON clause at once:

>>> print(
...     select(Address.email_address).
...     select_from(User).
...     join(User.addresses)
... )
{opensql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

The presence of an ORM _orm.relationship() on a mapping is not used by _sql.Select.join() or _sql.Select.join_from() if we don’t specify it; it is not used for ON clause inference. This means, if we join from User to Address without an ON clause, it works because of the _schema.ForeignKeyConstraint between the two mapped _schema.Table objects, not because of the _orm.relationship() objects on the User and Address classes:

>>> print(
...    select(Address.email_address).
...    join_from(User, Address)
... )
{opensql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Joining between Aliased targets

In the section ORM Entity Aliases we introduced the _orm.aliased() construct, which is used to apply a SQL alias to an ORM entity. When using a _orm.relationship() to help construct SQL JOIN, the use case where the target of the join is to be an _orm.aliased() is suited by making use of the _orm.PropComparator.of_type() modifier. To demonstrate we will construct the same join illustrated at ORM Entity Aliases using the _orm.relationship() attributes to join instead:

>>> print(
...        select(User).
...        join(User.addresses.of_type(address_alias_1)).
...        where(address_alias_1.email_address == '[email protected]').
...        join(User.addresses.of_type(address_alias_2)).
...        where(address_alias_2.email_address == '[email protected]')
...    )
{opensql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

To make use of a _orm.relationship() to construct a join from an aliased entity, the attribute is available from the _orm.aliased() construct directly:

>>> user_alias_1 = aliased(User)
>>> print(
...     select(user_alias_1.name).
...     join(user_alias_1.addresses)
... )
{opensql}SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

Augmenting the ON Criteria

The ON clause generated by the _orm.relationship() construct may also be augmented with additional criteria. This is useful both for quick ways to limit the scope of a particular join over a relationship path, and also for use cases like configuring loader strategies, introduced below at Loader Strategies. The _orm.PropComparator.and_() method accepts a series of SQL expressions positionally that will be joined to the ON clause of the JOIN via AND. For example if we wanted to JOIN from User to Address but also limit the ON criteria to only certain email addresses:

>>> stmt = (
...   select(User.fullname).
...   join(User.addresses.and_(Address.email_address == '[email protected]'))
... )
>>> session.execute(stmt).all()
{opensql}SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('[email protected]',){stop}
[('Pearl Krabs',)]

EXISTS forms: has() / any()

In the section EXISTS subqueries, we introduced the _sql.Exists object that provides for the SQL EXISTS keyword in conjunction with a scalar subquery. The _orm.relationship() construct provides for some helper methods that may be used to generate some common EXISTS styles of queries in terms of the relationship.

For a one-to-many relationship such as User.addresses, an EXISTS against the address table that correlates back to the user_account table can be produced using _orm.PropComparator.any(). This method accepts an optional WHERE criteria to limit the rows matched by the subquery:

>>> stmt = (
...   select(User.fullname).
...   where(User.addresses.any(Address.email_address == '[email protected]'))
... )
>>> session.execute(stmt).all()
{opensql}SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('[email protected]',){stop}
[('Pearl Krabs',)]

As EXISTS tends to be more efficient for negative lookups, a common query is to locate entities where there are no related entities present. This is succinct using a phrase such as ~User.addresses.any(), to select for User entities that have no related Address rows:

>>> stmt = (
...   select(User.fullname).
...   where(~User.addresses.any())
... )
>>> session.execute(stmt).all()
{opensql}SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] (){stop}
[('Patrick McStar',), ('Squidward Tentacles',), ('Eugene H. Krabs',)]

The _orm.PropComparator.has() method works in mostly the same way as _orm.PropComparator.any(), except that it’s used for many-to-one relationships, such as if we wanted to locate all Address objects which belonged to “pearl”:

>>> stmt = (
...   select(Address.email_address).
...   where(Address.user.has(User.name=="pkrabs"))
... )
>>> session.execute(stmt).all()
{opensql}SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('pkrabs',){stop}
[('[email protected]',), ('[email protected]',)]

Common Relationship Operators

There are some additional varieties of SQL generation helpers that come with _orm.relationship(), including:

many to one equals comparison - a specific object instance can be compared to many-to-one relationship, to select rows where the foreign key of the target entity matches the primary key value of the object given:
```
>>> print(select(Address).where(Address.user == u1))
{opensql}SELECT address.id, address.email_address, address.user_id
FROM address
WHERE :param_1 = address.user_id
```

many to one not equals comparison - the not equals operator may also be used:

>>> print(select(Address).where(Address.user != u1))
{opensql}SELECT address.id, address.email_address, address.user_id
FROM address
WHERE address.user_id != :user_id_1 OR address.user_id IS NULL

object is contained in a one-to-many collection - this is essentially the one-to-many version of the “equals” comparison, select rows where the primary key equals the value of the foreign key in a related object:
```
>>> print(select(User).where(User.addresses.contains(a1)))
{opensql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = :param_1
```
An object has a particular parent from a one-to-many perspective - the _orm.with_parent() function produces a comparison that returns rows which are referred towards by a given parent, this is essentially the same as using the == operator with the many-to-one side:
```
>>> from sqlalchemy.orm import with_parent
>>> print(select(Address).where(with_parent(u1, User.addresses)))
{opensql}SELECT address.id, address.email_address, address.user_id
FROM address
WHERE :param_1 = address.user_id
```

Loader Strategies

In the section Loading Relationships we introduced the concept that when we work with instances of mapped objects, accessing the attributes that are mapped using _orm.relationship() in the default case will emit a lazy load when the collection is not populated in order to load the objects that should be present in this collection.

Lazy loading is one of the most famous ORM patterns, and is also the one that is most controversial. When several dozen ORM objects in memory each refer to a handful of unloaded attributes, routine manipulation of these objects can spin off many additional queries that can add up (otherwise known as the N plus one problem), and to make matters worse they are emitted implicitly. These implicit queries may not be noticed, may cause errors when they are attempted after there’s no longer a database transaction available, or when using alternative concurrency patterns such as asyncio, they actually won’t work at all.

At the same time, lazy loading is a vastly popular and useful pattern when it is compatible with the concurrency approach in use and isn’t otherwise causing problems. For these reasons, SQLAlchemy’s ORM places a lot of emphasis on being able to control and optimize this loading behavior.

Above all, the first step in using ORM lazy loading effectively is to test the application, turn on SQL echoing, and watch the SQL that is emitted. If there seem to be lots of redundant SELECT statements that look very much like they could be rolled into one much more efficiently, if there are loads occurring inappropriately for objects that have been detached from their _orm.Session, that’s when to look into using loader strategies.

Loader strategies are represented as objects that may be associated with a SELECT statement using the _sql.Select.options() method, e.g.:

for user_obj in session.execute(
    select(User).options(selectinload(User.addresses))
).scalars():
    user_obj.addresses  # access addresses collection already loaded

They may be also configured as defaults for a _orm.relationship() using the :paramref:`_orm.relationship.lazy` option, e.g.:

from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = 'user_account'

    addresses = relationship("Address", back_populates="user", lazy="selectin")

Each loader strategy object adds some kind of information to the statement that will be used later by the _orm.Session when it is deciding how various attributes should be loaded and/or behave when they are accessed.

The sections below will introduce a few of the most prominently used loader strategies.

See also

Two sections in Relationship Loading Techniques:

Configuring Loader Strategies at Mapping Time - details on configuring the strategy on _orm.relationship()
Relationship Loading with Loader Options - details on using query-time loader strategies

Selectin Load

The most useful loader in modern SQLAlchemy is the _orm.selectinload() loader option. This option solves the most common form of the “N plus one” problem which is that of a set of objects that refer to related collections. _orm.selectinload() will ensure that a particular collection for a full series of objects are loaded up front using a single query. It does this using a SELECT form that in most cases can be emitted against the related table alone, without the introduction of JOINs or subqueries, and only queries for those parent objects for which the collection isn’t already loaded. Below we illustrate _orm.selectinload() by loading all of the User objects and all of their related Address objects; while we invoke _orm.Session.execute() only once, given a _sql.select() construct, when the database is accessed, there are in fact two SELECT statements emitted, the second one being to fetch the related Address objects:

>>> from sqlalchemy.orm import selectinload
>>> stmt = (
...   select(User).options(selectinload(User.addresses)).order_by(User.id)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})")
{opensql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6){stop}
spongebob  ([email protected])
sandy  ([email protected], [email protected])
patrick  ()
squidward  ()
ehkrabs  ()
pkrabs  ([email protected], [email protected])

Joined Load

The _orm.joinedload() eager load strategy is the oldest eager loader in SQLAlchemy, which augments the SELECT statement that’s being passed to the database with a JOIN (which may be an outer or an inner join depending on options), which can then load in related objects.

The _orm.joinedload() strategy is best suited towards loading related many-to-one objects, as this only requires that additional columns are added to a primary entity row that would be fetched in any case. For greater efficiency, it also accepts an option :paramref:`_orm.joinedload.innerjoin` so that an inner join instead of an outer join may be used for a case such as below where we know that all Address objects have an associated User:

>>> from sqlalchemy.orm import joinedload
>>> stmt = (
...   select(Address).options(joinedload(Address.user, innerjoin=True)).order_by(Address.id)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.Address.email_address} {row.Address.user.name}")
{opensql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
user_account_1.name, user_account_1.fullname
FROM address
JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
ORDER BY address.id
[...] (){stop}
[email protected] spongebob
[email protected] sandy
[email protected] sandy
[email protected] pkrabs
[email protected] pkrabs

_orm.joinedload() also works for collections, meaning one-to-many relationships, however it has the effect of multiplying out primary rows per related item in a recursive way that grows the amount of data sent for a result set by orders of magnitude for nested collections and/or larger collections, so its use vs. another option such as _orm.selectinload() should be evaluated on a per-case basis.

It’s important to note that the WHERE and ORDER BY criteria of the enclosing _sql.Select statement do not target the table rendered by joinedload(). Above, it can be seen in the SQL that an anonymous alias is applied to the user_account table such that is not directly addressable in the query. This concept is discussed in more detail in the section The Zen of Joined Eager Loading.

The ON clause rendered by _orm.joinedload() may be affected directly by using the _orm.PropComparator.and_() method described previously at Augmenting the ON Criteria; examples of this technique with loader strategies are further below at Augmenting Loader Strategy Paths. However, more generally, “joined eager loading” may be applied to a _sql.Select that uses _sql.Select.join() using the approach described in the next section, Explicit Join + Eager load.

Tip

It’s important to note that many-to-one eager loads are often not necessary, as the “N plus one” problem is much less prevalent in the common case. When many objects all refer to the same related object, such as many Address objects that each refer to the same User, SQL will be emitted only once for that User object using normal lazy loading. The lazy load routine will look up the related object by primary key in the current _orm.Session without emitting any SQL when possible.

Explicit Join + Eager load

If we were to load Address rows while joining to the user_account table using a method such as _sql.Select.join() to render the JOIN, we could also leverage that JOIN in order to eagerly load the contents of the Address.user attribute on each Address object returned. This is essentially that we are using “joined eager loading” but rendering the JOIN ourselves. This common use case is achieved by using the _orm.contains_eager() option. This option is very similar to _orm.joinedload(), except that it assumes we have set up the JOIN ourselves, and it instead only indicates that additional columns in the COLUMNS clause should be loaded into related attributes on each returned object, for example:

>>> from sqlalchemy.orm import contains_eager
>>> stmt = (
...   select(Address).
...   join(Address.user).
...   where(User.name == 'pkrabs').
...   options(contains_eager(Address.user)).order_by(Address.id)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.Address.email_address} {row.Address.user.name}")
{opensql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? ORDER BY address.id
[...] ('pkrabs',){stop}
[email protected] pkrabs
[email protected] pkrabs

Above, we both filtered the rows on user_account.name and also loaded rows from user_account into the Address.user attribute of the returned rows. If we had applied _orm.joinedload() separately, we would get a SQL query that unnecessarily joins twice:

>>> stmt = (
...   select(Address).
...   join(Address.user).
...   where(User.name == 'pkrabs').
...   options(joinedload(Address.user)).order_by(Address.id)
... )
>>> print(stmt)  # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily
{opensql}SELECT address.id, address.email_address, address.user_id,
user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname
FROM address JOIN user_account ON user_account.id = address.user_id
LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
WHERE user_account.name = :name_1 ORDER BY address.id

See also

Two sections in Relationship Loading Techniques:

The Zen of Joined Eager Loading - describes the above problem in detail
Routing Explicit Joins/Statements into Eagerly Loaded Collections - using contains_eager()

Augmenting Loader Strategy Paths

In Augmenting the ON Criteria we illustrated how to add arbitrary criteria to a JOIN rendered with _orm.relationship() to also include additional criteria in the ON clause. The _orm.PropComparator.and_() method is in fact generally available for most loader options. For example, if we wanted to re-load the names of users and their email addresses, but omitting the email addresses with the sqlalchemy.org domain, we can apply _orm.PropComparator.and_() to the argument passed to _orm.selectinload() to limit this criteria:

>>> from sqlalchemy.orm import selectinload
>>> stmt = (
...   select(User).
...   options(
...       selectinload(
...           User.addresses.and_(
...             ~Address.email_address.endswith("sqlalchemy.org")
...           )
...       )
...   ).
...   order_by(User.id).
...   execution_options(populate_existing=True)
... )
>>> for row in session.execute(stmt):
...     print(f"{row.User.name}  ({', '.join(a.email_address for a in row.User.addresses)})")
{opensql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
AND (address.email_address NOT LIKE '%' || ?)
[...] (1, 2, 3, 4, 5, 6, 'sqlalchemy.org'){stop}
spongebob  ()
sandy  ([email protected])
patrick  ()
squidward  ()
ehkrabs  ()
pkrabs  ([email protected], [email protected])

A very important thing to note above is that a special option is added with .execution_options(populate_existing=True). This option which takes effect when rows are being fetched indicates that the loader option we are using should replace the existing contents of collections on the objects, if they are already loaded. As we are working with a single _orm.Session repeatedly, the objects we see being loaded above are the same Python instances as those that were first persisted at the start of the ORM section of this tutorial.

Populate Existing - in ORM Querying Guide

Raiseload

One additional loader strategy worth mentioning is _orm.raiseload(). This option is used to completely block an application from having the N plus one problem at all by causing what would normally be a lazy load to raise an error instead. It has two variants that are controlled via the :paramref:`_orm.raiseload.sql_only` option to block either lazy loads that require SQL, versus all “load” operations including those which only need to consult the current _orm.Session.

One way to use _orm.raiseload() is to configure it on _orm.relationship() itself, by setting :paramref:`_orm.relationship.lazy` to the value "raise_on_sql", so that for a particular mapping, a certain relationship will never try to emit SQL:

class User(Base):
    __tablename__ = 'user_account'

    # ... Column mappings

    addresses = relationship("Address", back_populates="user", lazy="raise_on_sql")

class Address(Base):
    __tablename__ = 'address'

    # ... Column mappings

    user = relationship("User", back_populates="addresses", lazy="raise_on_sql")

Using such a mapping, the application is blocked from lazy loading, indicating that a particular query would need to specify a loader strategy:

u1 = s.execute(select(User)).scalars().first()
u1.addresses
sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise_on_sql'

The exception would indicate that this collection should be loaded up front instead:

u1 = s.execute(select(User).options(selectinload(User.addresses))).scalars().first()

The lazy="raise_on_sql" option tries to be smart about many-to-one relationships as well; above, if the Address.user attribute of an Address object were not loaded, but that User object were locally present in the same _orm.Session, the “raiseload” strategy would not raise an error.

Working with Related Objects — SQLAlchemy 2.0.0b1 documentation