SQLAlchemy 2.0 Documentation
SQLAlchemy ORM
- ORM Quick Start
- Mapper Configuration
- Relationship Configuration
- Querying Data, Loading Objects
- ORM Querying Guide¶
- SELECT statements
- ORM Update / Delete with Arbitrary WHERE clause
- Inspecting entities and columns from ORM-enabled SELECT and DML statements
- Additional ORM API Constructs
- Loading Columns
- Relationship Loading Techniques
- Loading Inheritance Hierarchies
- Constructors and Object Initialization
- Legacy Query API
- ORM Querying Guide¶
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Querying Data, Loading Objects
- Next: Loading Columns
- Up: Home
- On this page:
- ORM Querying Guide
- SELECT statements
- ORM Update / Delete with Arbitrary WHERE clause
- Inspecting entities and columns from ORM-enabled SELECT and DML statements
- Additional ORM API Constructs
ORM Querying Guide¶
This section provides an overview of emitting queries with the SQLAlchemy ORM using 2.0 style usage.
Readers of this section should be familiar with the SQLAlchemy overview at SQLAlchemy 2.0 Tutorial, and in particular most of the content here expands upon the content at Selecting Rows with Core or ORM.
Attention legacy users
In the SQLAlchemy 2.x series, SQL SELECT statements for the ORM are
constructed using the same select()
construct as is used in
Core, which is then invoked in terms of a Session
using the
Session.execute()
method (as are the update()
and
delete()
constructs now used for the
UPDATE and DELETE with arbitrary WHERE clause feature). However, the legacy
Query
object, which performs these same steps as more of an
“all-in-one” object, continues to remain available as a thin facade over
this new system, to support applications that were built on the 1.x series
without the need for wholesale replacement of all queries. For reference on
this object, see the section Legacy Query API.
SELECT statements¶
SELECT statements are produced by the select()
function which
returns a Select
object:
>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == 'spongebob')
To invoke a Select
with the ORM, it is passed to
Session.execute()
:
sql>>> result = session.execute(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
>>> for user_obj in result.scalars():
... print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants
Selecting ORM Entities and Attributes¶
The select()
construct accepts ORM entities, including mapped
classes as well as class-level attributes representing mapped columns, which
are converted into ORM-annotated FromClause
and
ColumnElement
elements at construction time.
A Select
object that contains ORM-annotated entities is normally
executed using a Session
object, and not a Connection
object, so that ORM-related features may take effect, including that
instances of ORM-mapped objects may be returned. When using the
Connection
directly, result rows will only contain
column-level data.
Below we select from the User
entity, producing a Select
that selects from the mapped Table
to which User
is mapped:
sql>>> result = session.execute(select(User).order_by(User.id))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
When selecting from ORM entities, the entity itself is returned in the result
as a row with a single element, as opposed to a series of individual columns;
for example above, the Result
returns Row
objects that have just a single element per row, that element holding onto a
User
object:
>>> result.fetchone()
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
When selecting a list of single-element rows containing ORM entities, it is
typical to skip the generation of Row
objects and instead
receive ORM entities directly, which is achieved using the
Result.scalars()
method:
>>> result.scalars().all()
[User(id=2, name='sandy', fullname='Sandy Cheeks'),
User(id=3, name='patrick', fullname='Patrick Star'),
User(id=4, name='squidward', fullname='Squidward Tentacles'),
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]
ORM Entities are named in the result row based on their class name,
such as below where we SELECT from both User
and Address
at the
same time:
>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
sql>>> for row in session.execute(stmt):
... print(f"{row.User.name} {row.Address.email_address}")
SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
Selecting Individual Attributes¶
The attributes on a mapped class, such as User.name
and Address.email_address
,
have a similar behavior as that of the entity class itself such as User
in that they are automatically converted into ORM-annotated Core objects
when passed to select()
. They may be used in the same way
as table columns are used:
sql>>> result = session.execute(
... select(User.name, Address.email_address).
... join(User.addresses).
... order_by(User.id, Address.id)
... )
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()
ORM attributes, themselves known as
InstrumentedAttribute
objects, can be used in the same way as any ColumnElement
,
and are delivered in result rows just the same way, such as below
where we refer to their values by column name within each row:
>>> for row in result:
... print(f"{row.name} {row.email_address}")
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
Grouping Selected Attributes with Bundles¶
The Bundle
construct is an extensible ORM-only construct that
allows sets of column expressions to be grouped in result rows:
>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
... Bundle("user", User.name, User.fullname),
... Bundle("email", Address.email_address)
... ).join_from(User, Address)
sql>>> for row in session.execute(stmt):
... print(f"{row.user.name} {row.email.email_address}")
SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org
The Bundle
is potentially useful for creating lightweight
views as well as custom column groupings such as mappings.
See also
Column Bundles - in the ORM loading documentation.
Selecting ORM Aliases¶
As discussed in the tutorial at Using Aliases, to create a
SQL alias of an ORM entity is achieved using the aliased()
construct against a mapped class:
>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id
As is the case when using Table.alias()
, the SQL alias
is anonymously named. For the case of selecting the entity from a row
with an explicit name, the aliased.name
parameter may be
passed as well:
>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
sql>>> row = session.execute(stmt).first()
SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] ()
>>> print(f"{row.u1.name}")
spongebob
The aliased
construct is also central to making use of subqueries
with the ORM; the sections Selecting Entities from Subqueries and
Joining to Subqueries discusses this further.
Getting ORM Results from Textual and Core Statements¶
The ORM supports loading of entities from SELECT statements that come from other
sources. The typical use case is that of a textual SELECT statement, which
in SQLAlchemy is represented using the text()
construct. The
text()
construct, once constructed, can be augmented with
information
about the ORM-mapped columns that the statement would load; this can then be
associated with the ORM entity itself so that ORM objects can be loaded based
on this statement.
Given a textual SQL statement we’d like to load from:
>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")
We can add column information to the statement by using the
TextClause.columns()
method; when this method is invoked, the
TextClause
object is converted into a TextualSelect
object, which takes on a role that is comparable to the Select
construct. The TextClause.columns()
method
is typically passed Column
objects or equivalent, and in this
case we can make use of the ORM-mapped attributes on the User
class
directly:
>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)
We now have an ORM-configured SQL construct that as given, can load the “id”,
“name” and “fullname” columns separately. To use this SELECT statement as a
source of complete User
entities instead, we can link these columns to a
regular ORM-enabled
Select
construct using the Select.from_statement()
method:
>>> # using from_statement()
>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
... print(user_obj)
SELECT id, name, fullname FROM user_account ORDER BY id
[...] ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
The same TextualSelect
object can also be converted into
a subquery using the TextualSelect.subquery()
method,
and linked to the User
entity to it using the aliased()
construct, in a similar manner as discussed below in Selecting Entities from Subqueries:
>>> # using aliased() to select from a subquery
>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
The difference between using the TextualSelect
directly with
Select.from_statement()
versus making use of aliased()
is that in the former case, no subquery is produced in the resulting SQL.
This can in some scenarios be advantageous from a performance or complexity
perspective.
See also
Using INSERT, UPDATE and ON CONFLICT (i.e. upsert) to return ORM Objects - The Select.from_statement()
method also works with DML statements that support RETURNING.
Selecting Entities from Subqueries¶
The aliased()
construct discussed in the previous section
can be used with any Subuqery
construct that comes from a
method such as Select.subquery()
to link ORM entities to the
columns returned by that subquery; there must be a column correspondence
relationship between the columns delivered by the subquery and the columns
to which the entity is mapped, meaning, the subquery needs to be ultimately
derived from those entities, such as in the example below:
>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')
Selecting Entities from UNIONs and other set operations¶
The union()
and union_all()
functions are the most
common set operations, which along with other set operations such as
except_()
, intersect()
and others deliver an object known as
a CompoundSelect
, which is composed of multiple
Select
constructs joined by a set-operation keyword. ORM entities may
be selected from simple compound selects using the Select.from_statement()
method illustrated previously at Getting ORM Results from Textual and Core Statements. In
this method, the UNION statement is the complete statement that will be
rendered, no additional criteria can be added after Select.from_statement()
is used:
>>> from sqlalchemy import union_all
>>> u = union_all(
... select(User).where(User.id < 2),
... select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')
A CompoundSelect
construct can be more flexibly used within
a query that can be further modified by organizing it into a subquery
and linking it to an ORM entity using aliased()
,
as illustrated previously at Selecting Entities from Subqueries. In the
example below, we first use CompoundSelect.subquery()
to create
a subquery of the UNION ALL statement, we then package that into the
aliased()
construct where it can be used like any other mapped
entity in a select()
construct, including that we can add filtering
and order by criteria based on its exported columns:
>>> subq = union_all(
... select(User).where(User.id < 2),
... select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
... print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')
See also
Selecting ORM Entities from Unions - in the SQLAlchemy 2.0 Tutorial
Joins¶
The Select.join()
and Select.join_from()
methods
are used to construct SQL JOINs against a SELECT statement.
This section will detail ORM use cases for these methods. For a general overview of their use from a Core perspective, see Explicit FROM clauses and JOINs in the SQLAlchemy 2.0 Tutorial.
The usage of Select.join()
in an ORM context for 2.0 style
queries is mostly equivalent, minus legacy use cases, to the usage of the
Query.join()
method in 1.x style queries.
Simple Relationship Joins¶
Consider a mapping between two classes User
and Address
,
with a relationship User.addresses
representing a collection
of Address
objects associated with each User
. The most
common usage of Select.join()
is to create a JOIN along this
relationship, using the User.addresses
attribute as an indicator
for how this should occur:
>>> stmt = select(User).join(User.addresses)
Where above, the call to Select.join()
along
User.addresses
will result in SQL approximately equivalent to:
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
In the above example we refer to User.addresses
as passed to
Select.join()
as the “on clause”, that is, it indicates
how the “ON” portion of the JOIN should be constructed.
Chaining Multiple Joins¶
To construct a chain of joins, multiple Select.join()
calls may be
used. The relationship-bound attribute implies both the left and right side of
the join at once. Consider additional entities Order
and Item
, where
the User.orders
relationship refers to the Order
entity, and the
Order.items
relationship refers to the Item
entity, via an association
table order_items
. Two Select.join()
calls will result in
a JOIN first from User
to Order
, and a second from Order
to
Item
. However, since Order.items
is a many to many
relationship, it results in two separate JOIN elements, for a total of three
JOIN elements in the resulting SQL:
>>> stmt = (
... select(User).
... join(User.orders).
... join(Order.items)
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
The order in which each call to the Select.join()
method
is significant only to the degree that the “left” side of what we would like
to join from needs to be present in the list of FROMs before we indicate a
new target. Select.join()
would not, for example, know how to
join correctly if we were to specify
select(User).join(Order.items).join(User.orders)
, and would raise an
error. In correct practice, the Select.join()
method is invoked
in such a way that lines up with how we would want the JOIN clauses in SQL
to be rendered, and each call should represent a clear link from what
precedes it.
All of the elements that we target in the FROM clause remain available
as potential points to continue joining FROM. We can continue to add
other elements to join FROM the User
entity above, for example adding
on the User.addresses
relationship to our chain of joins:
>>> stmt = (
... select(User).
... join(User.orders).
... join(Order.items).
... join(User.addresses)
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id
Joins to a Target Entity or Selectable¶
A second form of Select.join()
allows any mapped entity or core
selectable construct as a target. In this usage, Select.join()
will attempt to infer the ON clause for the JOIN, using the natural foreign
key relationship between two entities:
>>> stmt = select(User).join(Address)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
In the above calling form, Select.join()
is called upon to infer
the “on clause” automatically. This calling form will ultimately raise
an error if either there are no ForeignKeyConstraint
setup
between the two mapped Table
constructs, or if there are multiple
ForeignKeyConstraint
linakges between them such that the
appropriate constraint to use is ambiguous.
Note
When making use of Select.join()
or Select.join_from()
without indicating an ON clause, ORM
configured relationship()
constructs are not taken into account.
Only the configured ForeignKeyConstraint
relationships between
the entities at the level of the mapped Table
objects are consulted
when an attempt is made to infer an ON clause for the JOIN.
Joins to a Target with an ON Clause¶
The third calling form allows both the target entity as well as the ON clause to be passed explicitly. A example that includes a SQL expression as the ON clause is as follows:
>>> stmt = select(User).join(Address, User.id==Address.user_id)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
The expression-based ON clause may also be the relationship-bound
attribute; this form in fact states the target of Address
twice, however
this is accepted:
>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id
The above syntax has more functionality if we use it in terms of aliased
entities. The default target for User.addresses
is the Address
class, however if we pass aliased forms using aliased()
, the
aliased()
form will be used as the target, as in the example
below:
>>> a1 = aliased(Address)
>>> a2 = aliased(Address)
>>> stmt = (
... select(User).
... join(a1, User.addresses).
... join(a2, User.addresses).
... where(a1.email_address == 'ed@foo.com').
... where(a2.email_address == 'ed@bar.com')
... )
>>> print(stmt)
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
When using relationship-bound attributes, the target entity can also be
substituted with an aliased entity by using the
PropComparator.of_type()
method. The same example using
this method would be:
>>> stmt = (
... select(User).
... join(User.addresses.of_type(a1)).
... join(User.addresses.of_type(a2)).
... where(a1.email_address == 'ed@foo.com').
... where(a2.email_address == 'ed@bar.com')
... )
>>> print(stmt)
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
Augmenting Built-in ON Clauses¶
As a substitute for providing a full custom ON condition for an
existing relationship, the PropComparator.and_()
function
may be applied to a relationship attribute to augment additional
criteria into the ON clause; the additional criteria will be combined
with the default criteria using AND. Below, the ON criteria between
user_account
and address
contains two separate elements joined
by AND
, the first one being the natural join along the foreign key,
and the second being a custom limiting criteria:
>>> stmt = (
... select(User).
... join(User.addresses.and_(Address.email_address != 'foo@bar.com'))
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id
AND address.email_address != :email_address_1
See also
The PropComparator.and_()
method also works with loader
strategies. See the section Adding Criteria to loader options for an example.
Joining to Subqueries¶
The target of a join may be any “selectable” entity which usefully includes
subuqeries. When using the ORM, it is typical
that these targets are stated in terms of an
aliased()
construct, but this is not strictly required particularly
if the joined entity is not being returned in the results. For example, to join from the
User
entity to the Address
entity, where the Address
entity
is represented as a row limited subquery, we first construct a Subquery
object using Select.subquery()
, which may then be used as the
target of the Select.join()
method:
>>> subq = (
... select(Address).
... where(Address.email_address == 'pat999@aol.com').
... subquery()
... )
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1) AS anon_1
ON user_account.id = anon_1.user_id
The above SELECT statement when invoked via Session.execute()
will return rows that contain User
entities, but not Address
entities.
In order to add Address
entities to the set of entities that would be
returned in result sets, we construct an aliased()
object against
the Address
entity and the custom subquery. Note we also apply a name
"address"
to the aliased()
construct so that we may
refer to it by name in the result row:
>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
... print(f"{row.User} {row.address}")
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')
The same subquery may be referred towards by multiple entities as well,
for a subquery that represents more than one entity. The subquery itself
will remain unique within the statement, while the entities that are linked
to it using aliased
refer to distinct sets of columns:
>>> user_address_subq = (
... select(User.id, User.name, Address.id, Address.email_address).
... join_from(User, Address).
... where(Address.email_address.in_(['pat999@aol.com', 'squirrel@squirrelpower.org'])).
... subquery()
... )
>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")
>>> stmt = select(user_alias, address_alias).where(user_alias.name == 'sandy')
>>> for row in session.execute(stmt):
... print(f"{row.user} {row.address}")
SELECT anon_1.id, anon_1.name, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name, address.id AS id_1, address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')
Controlling what to Join From¶
In cases where the left side of the current state of
Select
is not in line with what we want to join from,
the Select.join_from()
method may be used:
>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == 'sandy')
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
The Select.join_from()
method accepts two or three arguments, either
in the form <join from>, <onclause>
, or <join from>, <join to>,
[<onclause>]
:
>>> stmt = select(Address).join_from(User, Address).where(User.name == 'sandy')
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
To set up the initial FROM clause for a SELECT such that Select.join()
can be used subsequent, the Select.select_from()
method may also
be used:
>>> stmt = select(Address).select_from(User).join(Address).where(User.name == 'sandy')
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Tip
The Select.select_from()
method does not actually have the
final say on the order of tables in the FROM clause. If the statement
also refers to a Join
construct that refers to existing
tables in a different order, the Join
construct takes
precedence. When we use methods like Select.join()
and Select.join_from()
, these methods are ultimately creating
such a Join
object. Therefore we can see the contents
of Select.select_from()
being overridden in a case like this:
>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == 'sandy')
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1
Where above, we see that the FROM clause is address JOIN user_account
,
even though we stated select_from(User)
first. Because of the
.join(Address.user)
method call, the statement is ultimately equivalent
to the following:
>>> user_table = User.__table__
>>> address_table = Address.__table__
>>> from sqlalchemy.sql import join
>>>
>>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
>>> stmt = (
... select(address_table).select_from(user_table).select_from(j).
... where(user_table.c.name == 'sandy')
... )
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1
The Join
construct above is added as another entry in the
Select.select_from()
list which supersedes the previous entry.
Special Relationship Operators¶
As detailed in the SQLAlchemy 2.0 Tutorial at
Using Relationships in Queries, ORM attributes mapped by
relationship()
may be used in a variety of ways as SQL construction
helpers. In addition to the above documentation on
Joins, relationships may produce criteria to be used in
the WHERE clause as well. See the linked sections below.
See also
Sections in the Working with Related Objects section of the SQLAlchemy 2.0 Tutorial:
EXISTS forms: has() / any() - helpers to generate EXISTS clauses using
relationship()
Common Relationship Operators - helpers to create comparisons in terms of a
relationship()
in reference to a specific object instance
ORM Loader Options¶
Loader options are objects that are passed to the Select.options()
method which affect the loading of both column and relationship-oriented
attributes. The majority of loader options descend from the Load
hierarchy. For a complete overview of using loader options, see the linked
sections below.
See also
Loading Columns - details mapper and loading options that affect how column and SQL-expression mapped attributes are loaded
Relationship Loading Techniques - details relationship and loading options that affect how
relationship()
mapped attributes are loaded
ORM Execution Options¶
Execution options are keyword arguments that are passed to an
“execution_options” method, which take place at the level of statement
execution. The primary “execution option” method is in Core at
Connection.execution_options()
. In the ORM, execution options may
also be passed to Session.execute()
using the
Session.execute.execution_options
parameter. Perhaps more
succinctly, most execution options, including those specific to the ORM, can be
assigned to a statement directly, using the
Executable.execution_options()
method, so that the options may be
associated directly with the statement instead of being configured separately.
The examples below will use this form.
Populate Existing¶
The populate_existing
execution option ensures that for all rows
loaded, the corresponding instances in the Session
will
be fully refreshed, erasing any existing data within the objects
(including pending changes) and replacing with the data loaded from the
result.
Example use looks like:
>>> stmt = select(User).execution_options(populate_existing=True)
sql>>> result = session.execute(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
...
Normally, ORM objects are only loaded once, and if they are matched up
to the primary key in a subsequent result row, the row is not applied to the
object. This is both to preserve pending, unflushed changes on the object
as well as to avoid the overhead and complexity of refreshing data which
is already there. The Session
assumes a default working
model of a highly isolated transaction, and to the degree that data is
expected to change within the transaction outside of the local changes being
made, those use cases would be handled using explicit steps such as this method.
Using populate_existing
, any set of objects that matches a query
can be refreshed, and it also allows control over relationship loader options.
E.g. to refresh an instance while also refreshing a related set of objects:
stmt = (
select(User).
where(User.name.in_(names)).
execution_options(populate_existing=True).
options(selectinload(User.addresses)
)
# will refresh all matching User objects as well as the related
# Address objects
users = session.execute(stmt).scalars().all()
Another use case for populate_existing
is in support of various
attribute loading features that can change how an attribute is loaded on
a per-query basis. Options for which this apply include:
The
with_expression()
optionThe
PropComparator.and_()
method that can modify what a loader strategy loadsThe
contains_eager()
optionThe
with_loader_criteria()
option
The populate_existing
execution option is equvialent to the
Query.populate_existing()
method in 1.x style ORM queries.
See also
I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere - in Frequently Asked Questions
Refreshing / Expiring - in the ORM Session
documentation
Autoflush¶
This option when passed as False
will cause the Session
to not invoke the “autoflush” step. It’s equivalent to using the
Session.no_autoflush
context manager to disable autoflush:
>>> stmt = select(User).execution_options(autoflush=False)
sql>>> session.execute(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
...
This option will also work on ORM-enabled Update
and
Delete
queries.
The autoflush
execution option is equvialent to the
Query.autoflush()
method in 1.x style ORM queries.
See also
Yield Per¶
The yield_per
execution option is an integer value which will cause the
Result
to yield only a fixed count of rows at a time. It is
often useful to use with a result partitioning method such as
Result.partitions()
, e.g.:
>>> stmt = select(User).execution_options(yield_per=10)
sql>>> for partition in session.execute(stmt).partitions(10):
... for row in partition:
... print(row)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
...
The purpose of this method is when fetching very large result sets (> 10K rows), to batch results in sub-collections and yield them out partially, so that the Python interpreter doesn’t need to declare very large areas of memory which is both time consuming and leads to excessive memory use. The performance from fetching hundreds of thousands of rows can often double when a suitable yield-per setting (e.g. approximately 1000) is used, even with DBAPIs that buffer rows (which are most).
When yield_per
is used, the
Connection.execution_options.stream_results
option is also
set for the Core execution, so that a streaming / server side cursor will be
used if the backend supports it 1
The yield_per
execution option is not compatible with subqueryload eager
loading or joinedload eager loading when using collections. It is
potentially compatible with selectinload eager loading, provided the database
driver supports multiple, independent cursors 2 .
Additionally, the yield_per
execution option is not compatible
with the Result.unique()
method; as this method relies upon
storing a complete set of identities for all rows, it would necessarily
defeat the purpose of using yield_per
which is to handle an arbitrarily
large number of rows.
Changed in version 1.4.6: An exception is raised when ORM rows are fetched
from a Result
object that makes use of the
Result.unique()
filter, at the same time as the yield_per
execution option is used.
The yield_per
execution option is equvialent to the
Query.yield_per()
method in 1.x style ORM queries.
- 1
currently known are
psycopg2
,mysqldb
andpymysql
. Other backends will pre buffer all rows. The memory use of raw database rows is much less than that of an ORM-mapped object, but should still be taken into consideration when benchmarking.- 2
the
psycopg2
andpysqlite
drivers are known to work, drivers for MySQL and SQL Server ODBC drivers do not.
ORM Update / Delete with Arbitrary WHERE clause¶
The Session.execute()
method, in addition to handling ORM-enabled
Select
objects, can also accommodate ORM-enabled
Update
and Delete
objects, which UPDATE or DELETE
any number of database rows while also being able to synchronize the state of
matching objects locally present in the Session
. See the section
UPDATE and DELETE with arbitrary WHERE clause for background on this feature.
Inspecting entities and columns from ORM-enabled SELECT and DML statements¶
The select()
construct, as well as the insert()
, update()
and delete()
constructs (for the latter DML constructs, as of SQLAlchemy
1.4.33), all support the ability to inspect the entities in which these
statements are created against, as well as the columns and datatypes that would
be returned in a result set.
For a Select
object, this information is available from the
Select.column_descriptions
attribute. This attribute operates in the
same way as the legacy Query.column_descriptions
attribute. The format
returned is a list of dictionaries:
>>> from pprint import pprint
>>> user_alias = aliased(User, name='user2')
>>> stmt = select(User, User.id, user_alias)
>>> pprint(stmt.column_descriptions)
[{'aliased': False,
'entity': <class 'User'>,
'expr': <class 'User'>,
'name': 'User',
'type': <class 'User'>},
{'aliased': False,
'entity': <class 'User'>,
'expr': <....InstrumentedAttribute object at ...>,
'name': 'id',
'type': Integer()},
{'aliased': True,
'entity': <AliasedClass ...; User>,
'expr': <AliasedClass ...; User>,
'name': 'user2',
'type': <class 'User'>}]
When Select.column_descriptions
is used with non-ORM objects
such as plain Table
or Column
objects, the entries
will contain basic information about individual columns returned in all
cases:
>>> stmt = select(user_table, address_table.c.id)
>>> pprint(stmt.column_descriptions)
[{'expr': Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False),
'name': 'id',
'type': Integer()},
{'expr': Column('name', String(length=30), table=<user_account>),
'name': 'name',
'type': String(length=30)},
{'expr': Column('fullname', String(), table=<user_account>),
'name': 'fullname',
'type': String()},
{'expr': Column('id', Integer(), table=<address>, primary_key=True, nullable=False),
'name': 'id_1',
'type': Integer()}]
Changed in version 1.4.33: The Select.column_descriptions
attribute now returns
a value when used against a Select
that is not ORM-enabled. Previously,
this would raise NotImplementedError
.
For insert()
, update()
and delete()
constructs, there are
two separate attributes. One is UpdateBase.entity_description
which
returns information about the primary ORM entity and database table which the
DML construct would be affecting:
>>> from sqlalchemy import update
>>> stmt = update(User).values(name="somename").returning(User.id)
>>> pprint(stmt.entity_description)
{'entity': <class 'User'>,
'expr': <class 'User'>,
'name': 'User',
'table': Table('user_account', ...),
'type': <class 'User'>}
Tip
The UpdateBase.entity_description
includes an entry
"table"
which is actually the table to be inserted, updated or
deleted by the statement, which is not always the same as the SQL
“selectable” to which the class may be mapped. For example, in a
joined-table inheritance scenario, "table"
will refer to the local table
for the given entity.
The other is UpdateBase.returning_column_descriptions
which
delivers information about the columns present in the RETURNING collection
in a manner roughly similar to that of Select.column_descriptions
:
>>> pprint(stmt.returning_column_descriptions)
[{'aliased': False,
'entity': <class 'User'>,
'expr': <sqlalchemy.orm.attributes.InstrumentedAttribute ...>,
'name': 'id',
'type': Integer()}]
New in version 1.4.33: Added the UpdateBase.entity_description
and UpdateBase.returning_column_descriptions
attributes.
Additional ORM API Constructs¶
Object Name | Description |
---|---|
aliased(element[, alias, name, flat, ...]) |
Produce an alias of the given element, usually an |
Represents an “aliased” form of a mapped class for usage with Query. |
|
Provide an inspection interface for an
|
|
A grouping of SQL expressions that are returned by a |
|
join(left, right[, onclause, isouter, ...]) |
Produce an inner join between left and right clauses. |
outerjoin(left, right[, onclause, full]) |
Produce a left outer join between left and right clauses. |
with_loader_criteria(entity_or_base, where_criteria[, loader_only, include_aliases, ...]) |
Add additional WHERE criteria to the load for all occurrences of a particular entity. |
with_parent(instance, prop[, from_entity]) |
Create filtering criterion that relates this query’s primary entity
to the given related instance, using established
|
- function sqlalchemy.orm.aliased(element: Union[_EntityType[_O], FromClause], alias: Optional[Union[Alias, Subquery]] = None, name: Optional[str] = None, flat: bool = False, adapt_on_names: bool = False) → Union[AliasedClass[_O], FromClause, AliasedType[_O]]¶
Produce an alias of the given element, usually an
AliasedClass
instance.E.g.:
my_alias = aliased(MyClass) stmt = select(MyClass, my_alias).filter(MyClass.id > my_alias.id) result = session.execute(stmt)
The
aliased()
function is used to create an ad-hoc mapping of a mapped class to a new selectable. By default, a selectable is generated from the normally mapped selectable (typically aTable
) using theFromClause.alias()
method. However,aliased()
can also be used to link the class to a newselect()
statement. Also, thewith_polymorphic()
function is a variant ofaliased()
that is intended to specify a so-called “polymorphic selectable”, that corresponds to the union of several joined-inheritance subclasses at once.For convenience, the
aliased()
function also accepts plainFromClause
constructs, such as aTable
orselect()
construct. In those cases, theFromClause.alias()
method is called on the object and the newAlias
object returned. The returnedAlias
is not ORM-mapped in this case.See also
ORM Entity Aliases - in the SQLAlchemy 2.0 Tutorial
Selecting ORM Aliases - in the ORM Querying Guide
ormtutorial_aliases - in the legacy Object Relational Tutorial
- Parameters
element¶ – element to be aliased. Is normally a mapped class, but for convenience can also be a
FromClause
element.alias¶ – Optional selectable unit to map the element to. This is usually used to link the object to a subquery, and should be an aliased select construct as one would produce from the
Query.subquery()
method or theSelect.subquery()
orSelect.alias()
methods of theselect()
construct.name¶ – optional string name to use for the alias, if not specified by the
alias
parameter. The name, among other things, forms the attribute name that will be accessible via tuples returned by aQuery
object. Not supported when creating aliases ofJoin
objects.flat¶ – Boolean, will be passed through to the
FromClause.alias()
call so that aliases ofJoin
objects will alias the individual tables inside the join, rather than creating a subquery. This is generally supported by all modern databases with regards to right-nested joins and generally produces more efficient queries.adapt_on_names¶ –
if True, more liberal “matching” will be used when mapping the mapped columns of the ORM entity to those of the given selectable - a name-based match will be performed if the given selectable doesn’t otherwise have a column that corresponds to one on the entity. The use case for this is when associating an entity with some derived selectable such as one that uses aggregate functions:
class UnitPrice(Base): __tablename__ = 'unit_price' ... unit_id = Column(Integer) price = Column(Numeric) aggregated_unit_price = Session.query( func.sum(UnitPrice.price).label('price') ).group_by(UnitPrice.unit_id).subquery() aggregated_unit_price = aliased(UnitPrice, alias=aggregated_unit_price, adapt_on_names=True)
Above, functions on
aggregated_unit_price
which refer to.price
will return thefunc.sum(UnitPrice.price).label('price')
column, as it is matched on the name “price”. Ordinarily, the “price” function wouldn’t have any “column correspondence” to the actualUnitPrice.price
column as it is not a proxy of the original.
- class sqlalchemy.orm.util.AliasedClass¶
Represents an “aliased” form of a mapped class for usage with Query.
The ORM equivalent of a
alias()
construct, this object mimics the mapped class using a__getattr__
scheme and maintains a reference to a realAlias
object.A primary purpose of
AliasedClass
is to serve as an alternate within a SQL statement generated by the ORM, such that an existing mapped entity can be used in multiple contexts. A simple example:# find all pairs of users with the same name user_alias = aliased(User) session.query(User, user_alias).\ join((user_alias, User.id > user_alias.id)).\ filter(User.name == user_alias.name)
AliasedClass
is also capable of mapping an existing mapped class to an entirely new selectable, provided this selectable is column- compatible with the existing mapped selectable, and it can also be configured in a mapping as the target of arelationship()
. See the links below for examples.The
AliasedClass
object is constructed typically using thealiased()
function. It also is produced with additional configuration when using thewith_polymorphic()
function.The resulting object is an instance of
AliasedClass
. This object implements an attribute scheme which produces the same attribute and method interface as the original mapped class, allowingAliasedClass
to be compatible with any attribute technique which works on the original class, including hybrid attributes (see Hybrid Attributes).The
AliasedClass
can be inspected for its underlyingMapper
, aliased selectable, and other information usinginspect()
:from sqlalchemy import inspect my_alias = aliased(MyClass) insp = inspect(my_alias)
The resulting inspection object is an instance of
AliasedInsp
.See also
Class signature
class
sqlalchemy.orm.AliasedClass
(sqlalchemy.inspection.Inspectable
,sqlalchemy.orm.ORMColumnsClauseRole
)-
method
sqlalchemy.orm.util.AliasedClass.
__init__(mapped_class_or_ac: _EntityType[_O], alias: Optional[FromClause] = None, name: Optional[str] = None, flat: bool = False, adapt_on_names: bool = False, with_polymorphic_mappers: Optional[Sequence[Mapper[Any]]] = None, with_polymorphic_discriminator: Optional[ColumnElement[Any]] = None, base_alias: Optional[AliasedInsp[Any]] = None, use_mapper_path: bool = False, represents_outer_join: bool = False)¶
-
method
sqlalchemy.orm.util.AliasedClass.
static __new__(cls, *args, **kwds)¶ inherited from the
typing.Generic.__new__
method ofGeneric
-
method
- class sqlalchemy.orm.util.AliasedInsp¶
Provide an inspection interface for an
AliasedClass
object.The
AliasedInsp
object is returned given anAliasedClass
using theinspect()
function:from sqlalchemy import inspect from sqlalchemy.orm import aliased my_alias = aliased(MyMappedClass) insp = inspect(my_alias)
Attributes on
AliasedInsp
include:entity
- theAliasedClass
represented.mapper
- theMapper
mapping the underlying class.selectable
- theAlias
construct which ultimately represents an aliasedTable
orSelect
construct.name
- the name of the alias. Also is used as the attribute name when returned in a result tuple fromQuery
.with_polymorphic_mappers
- collection ofMapper
objects indicating all those mappers expressed in the select construct for theAliasedClass
.polymorphic_on
- an alternate column or SQL expression which will be used as the “discriminator” for a polymorphic load.
See also
Class signature
class
sqlalchemy.orm.AliasedInsp
(sqlalchemy.orm.ORMEntityColumnsClauseRole
,sqlalchemy.orm.ORMFromClauseRole
,sqlalchemy.sql.cache_key.HasCacheKey
,sqlalchemy.orm.base.InspectionAttr
,sqlalchemy.util.langhelpers.MemoizedSlots
,sqlalchemy.inspection.Inspectable
,typing.Generic
)-
method
sqlalchemy.orm.util.AliasedInsp.
__init__(entity: AliasedClass[_O], inspected: _InternalEntityType[_O], selectable: FromClause, name: Optional[str], with_polymorphic_mappers: Optional[Sequence[Mapper[Any]]], polymorphic_on: Optional[ColumnElement[Any]], _base_alias: Optional[AliasedInsp[Any]], _use_mapper_path: bool, adapt_on_names: bool, represents_outer_join: bool, nest_adapters: bool)¶
-
method
sqlalchemy.orm.util.AliasedInsp.
static __new__(cls, *args, **kwds)¶ inherited from the
typing.Generic.__new__
method ofGeneric
- class sqlalchemy.orm.Bundle¶
A grouping of SQL expressions that are returned by a
Query
under one namespace.The
Bundle
essentially allows nesting of the tuple-based results returned by a column-orientedQuery
object. It also is extensible via simple subclassing, where the primary capability to override is that of how the set of expressions should be returned, allowing post-processing as well as custom return types, without involving ORM identity-mapped classes.New in version 0.9.0.
See also
Class signature
class
sqlalchemy.orm.Bundle
(sqlalchemy.orm.ORMColumnsClauseRole
,sqlalchemy.sql.annotation.SupportsCloneAnnotations
,sqlalchemy.sql.cache_key.MemoizedHasCacheKey
,sqlalchemy.inspection.Inspectable
,sqlalchemy.orm.base.InspectionAttr
)-
method
sqlalchemy.orm.Bundle.
__init__(name: str, *exprs: _ColumnExpressionArgument[Any], **kw: Any)¶ Construct a new
Bundle
.e.g.:
bn = Bundle("mybundle", MyClass.x, MyClass.y) for row in session.query(bn).filter( bn.c.x == 5).filter(bn.c.y == 4): print(row.mybundle.x, row.mybundle.y)
-
attribute
sqlalchemy.orm.Bundle.
c: ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]¶ An alias for
Bundle.columns
.
-
attribute
sqlalchemy.orm.Bundle.
columns: ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]¶ A namespace of SQL expressions referred to by this
Bundle
.e.g.:
bn = Bundle("mybundle", MyClass.x, MyClass.y) q = sess.query(bn).filter(bn.c.x == 5)
Nesting of bundles is also supported:
b1 = Bundle("b1", Bundle('b2', MyClass.a, MyClass.b), Bundle('b3', MyClass.x, MyClass.y) ) q = sess.query(b1).filter( b1.c.b2.c.a == 5).filter(b1.c.b3.c.y == 9)
See also
-
method
sqlalchemy.orm.Bundle.
create_row_processor(query: Select[Any], procs: Sequence[Callable[[Row[Any]], Any]], labels: Sequence[str]) → Callable[[Row[Any]], Any]¶ Produce the “row processing” function for this
Bundle
.May be overridden by subclasses.
See also
Column Bundles - includes an example of subclassing.
-
method
sqlalchemy.orm.Bundle.
label(name)¶ Provide a copy of this
Bundle
passing a new label.
-
attribute
sqlalchemy.orm.Bundle.
single_entity = False¶ If True, queries for a single Bundle will be returned as a single entity, rather than an element within a keyed tuple.
-
method
- function sqlalchemy.orm.with_loader_criteria(entity_or_base: _EntityType[Any], where_criteria: _ColumnExpressionArgument[bool], loader_only: bool = False, include_aliases: bool = False, propagate_to_loaders: bool = True, track_closure_variables: bool = True) → LoaderCriteriaOption¶
Add additional WHERE criteria to the load for all occurrences of a particular entity.
New in version 1.4.
The
with_loader_criteria()
option is intended to add limiting criteria to a particular kind of entity in a query, globally, meaning it will apply to the entity as it appears in the SELECT query as well as within any subqueries, join conditions, and relationship loads, including both eager and lazy loaders, without the need for it to be specified in any particular part of the query. The rendering logic uses the same system used by single table inheritance to ensure a certain discriminator is applied to a table.E.g., using 2.0-style queries, we can limit the way the
User.addresses
collection is loaded, regardless of the kind of loading used:from sqlalchemy.orm import with_loader_criteria stmt = select(User).options( selectinload(User.addresses), with_loader_criteria(Address, Address.email_address != 'foo')) )
Above, the “selectinload” for
User.addresses
will apply the given filtering criteria to the WHERE clause.Another example, where the filtering will be applied to the ON clause of the join, in this example using 1.x style queries:
q = session.query(User).outerjoin(User.addresses).options( with_loader_criteria(Address, Address.email_address != 'foo')) )
The primary purpose of
with_loader_criteria()
is to use it in theSessionEvents.do_orm_execute()
event handler to ensure that all occurrences of a particular entity are filtered in a certain way, such as filtering for access control roles. It also can be used to apply criteria to relationship loads. In the example below, we can apply a certain set of rules to all queries emitted by a particularSession
:session = Session(bind=engine) @event.listens_for("do_orm_execute", session) def _add_filtering_criteria(execute_state): if ( execute_state.is_select and not execute_state.is_column_load and not execute_state.is_relationship_load ): execute_state.statement = execute_state.statement.options( with_loader_criteria( SecurityRole, lambda cls: cls.role.in_(['some_role']), include_aliases=True ) )
In the above example, the
SessionEvents.do_orm_execute()
event will intercept all queries emitted using theSession
. For those queries which are SELECT statements and are not attribute or relationship loads a customwith_loader_criteria()
option is added to the query. Thewith_loader_criteria()
option will be used in the given statement and will also be automatically propagated to all relationship loads that descend from this query.The criteria argument given is a
lambda
that accepts acls
argument. The given class will expand to include all mapped subclass and need not itself be a mapped class.Tip
When using
with_loader_criteria()
option in conjunction with thecontains_eager()
loader option, it’s important to note thatwith_loader_criteria()
only affects the part of the query that determines what SQL is rendered in terms of the WHERE and FROM clauses. Thecontains_eager()
option does not affect the rendering of the SELECT statement outside of the columns clause, so does not have any interaction with thewith_loader_criteria()
option. However, the way things “work” is thatcontains_eager()
is meant to be used with a query that is already selecting from the additional entities in some way, wherewith_loader_criteria()
can apply it’s additional criteria.In the example below, assuming a mapping relationship as
A -> A.bs -> B
, the givenwith_loader_criteria()
option will affect the way in which the JOIN is rendered:stmt = select(A).join(A.bs).options( contains_eager(A.bs), with_loader_criteria(B, B.flag == 1) )
Above, the given
with_loader_criteria()
option will affect the ON clause of the JOIN that is specified by.join(A.bs)
, so is applied as expected. Thecontains_eager()
option has the effect that columns fromB
are added to the columns clause:SELECT b.id, b.a_id, b.data, b.flag, a.id AS id_1, a.data AS data_1 FROM a JOIN b ON a.id = b.a_id AND b.flag = :flag_1
The use of the
contains_eager()
option within the above statement has no effect on the behavior of thewith_loader_criteria()
option. If thecontains_eager()
option were omitted, the SQL would be the same as regards the FROM and WHERE clauses, wherewith_loader_criteria()
continues to add its criteria to the ON clause of the JOIN. The addition ofcontains_eager()
only affects the columns clause, in that additional columns againstb
are added which are then consumed by the ORM to produceB
instances.Warning
The use of a lambda inside of the call to
with_loader_criteria()
is only invoked once per unique class. Custom functions should not be invoked within this lambda. See Using Lambdas to add significant speed gains to statement production for an overview of the “lambda SQL” feature, which is for advanced use only.- Parameters
entity_or_base¶ – a mapped class, or a class that is a super class of a particular set of mapped classes, to which the rule will apply.
where_criteria¶ – a Core SQL expression that applies limiting criteria. This may also be a “lambda:” or Python function that accepts a target class as an argument, when the given class is a base with many different mapped subclasses.
include_aliases¶ – if True, apply the rule to
aliased()
constructs as well.propagate_to_loaders¶ –
defaults to True, apply to relationship loaders such as lazy loaders.
See also
ORM Query Events - includes examples of using
with_loader_criteria()
.Adding global WHERE / ON criteria - basic example on how to combine
with_loader_criteria()
with theSessionEvents.do_orm_execute()
event.track_closure_variables¶ –
when False, closure variables inside of a lambda expression will not be used as part of any cache key. This allows more complex expressions to be used inside of a lambda expression but requires that the lambda ensures it returns the identical SQL every time given a particular class.
New in version 1.4.0b2.
- function sqlalchemy.orm.join(left: _FromClauseArgument, right: _FromClauseArgument, onclause: Optional[_OnClauseArgument] = None, isouter: bool = False, full: bool = False) → _ORMJoin¶
Produce an inner join between left and right clauses.
join()
is an extension to the core join interface provided byjoin()
, where the left and right selectables may be not only core selectable objects such asTable
, but also mapped classes orAliasedClass
instances. The “on” clause can be a SQL expression or an ORM mapped attribute referencing a configuredrelationship()
.join()
is not commonly needed in modern usage, as its functionality is encapsulated within that of theSelect.join()
andQuery.join()
methods. which feature a significant amount of automation beyondjoin()
by itself. Explicit use ofjoin()
with ORM-enabled SELECT statements involves use of theSelect.select_from()
method, as in:from sqlalchemy.orm import join stmt = select(User).\ select_from(join(User, Address, User.addresses)).\ filter(Address.email_address=='foo@bar.com')
In modern SQLAlchemy the above join can be written more succinctly as:
stmt = select(User).\ join(User.addresses).\ filter(Address.email_address=='foo@bar.com')
See Joins for information on modern usage of ORM level joins.
- function sqlalchemy.orm.outerjoin(left: _FromClauseArgument, right: _FromClauseArgument, onclause: Optional[_OnClauseArgument] = None, full: bool = False) → _ORMJoin¶
Produce a left outer join between left and right clauses.
This is the “outer join” version of the
join()
function, featuring the same behavior except that an OUTER JOIN is generated. See that function’s documentation for other usage details.
- function sqlalchemy.orm.with_parent(instance: object, prop: attributes.QueryableAttribute[Any], from_entity: Optional[_EntityType[Any]] = None) → ColumnElement[bool]¶
Create filtering criterion that relates this query’s primary entity to the given related instance, using established
relationship()
configuration.E.g.:
stmt = select(Address).where(with_parent(some_user, User.addresses))
The SQL rendered is the same as that rendered when a lazy loader would fire off from the given parent on that attribute, meaning that the appropriate state is taken from the parent object in Python without the need to render joins to the parent table in the rendered statement.
The given property may also make use of
PropComparator.of_type()
to indicate the left side of the criteria:a1 = aliased(Address) a2 = aliased(Address) stmt = select(a1, a2).where( with_parent(u1, User.addresses.of_type(a2)) )
The above use is equivalent to using the
from_entity()
argument:a1 = aliased(Address) a2 = aliased(Address) stmt = select(a1, a2).where( with_parent(u1, User.addresses, from_entity=a2) )
- Parameters
instance¶ – An instance which has some
relationship()
.property¶ – Class-bound attribute, which indicates what relationship from the instance should be used to reconcile the parent/child relationship.
from_entity¶ –
Entity in which to consider as the left side. This defaults to the “zero” entity of the
Query
itself.New in version 1.2.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 4.5.0.