.. highlight:: pycon+sql

.. _queryguide_toplevel:

==================
ORM Querying Guide
==================

This section provides an overview of emitting queries with the
SQLAlchemy ORM using :term:`2.0 style` usage.

Readers of this section should be familiar with the SQLAlchemy overview
at :ref:`unified_tutorial`, and in particular most of the content here expands
upon the content at :ref:`tutorial_selecting_data`.


..  Setup code, not for display

    >>> from sqlalchemy import create_engine
    >>> engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
    >>> from sqlalchemy import MetaData, Table, Column, Integer, String
    >>> metadata_obj = MetaData()
    >>> user_table = Table(
    ...     "user_account",
    ...     metadata_obj,
    ...     Column("id", Integer, primary_key=True),
    ...     Column("name", String(30)),
    ...     Column("fullname", String),
    ... )
    >>> from sqlalchemy import ForeignKey
    >>> address_table = Table(
    ...     "address",
    ...     metadata_obj,
    ...     Column("id", Integer, primary_key=True),
    ...     Column("user_id", None, ForeignKey("user_account.id")),
    ...     Column("email_address", String, nullable=False),
    ... )
    >>> orders_table = Table(
    ...     "user_order",
    ...     metadata_obj,
    ...     Column("id", Integer, primary_key=True),
    ...     Column("user_id", None, ForeignKey("user_account.id")),
    ...     Column("email_address", String, nullable=False),
    ... )
    >>> order_items_table = Table(
    ...     "order_items",
    ...     metadata_obj,
    ...     Column("order_id", ForeignKey("user_order.id"), primary_key=True),
    ...     Column("item_id", ForeignKey("item.id"), primary_key=True),
    ... )
    >>> items_table = Table(
    ...     "item",
    ...     metadata_obj,
    ...     Column("id", Integer, primary_key=True),
    ...     Column("name", String),
    ...     Column("description", String),
    ... )
    >>> metadata_obj.create_all(engine)
    BEGIN (implicit)
    ...
    >>> from sqlalchemy.orm import declarative_base
    >>> Base = declarative_base()
    >>> from sqlalchemy.orm import relationship
    >>> class User(Base):
    ...     __table__ = user_table
    ...
    ...     addresses = relationship("Address", back_populates="user")
    ...     orders = relationship("Order")
    ...
    ...     def __repr__(self):
    ...         return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

    >>> class Address(Base):
    ...     __table__ = address_table
    ...
    ...     user = relationship("User", back_populates="addresses")
    ...
    ...     def __repr__(self):
    ...         return f"Address(id={self.id!r}, email_address={self.email_address!r})"

    >>> class Order(Base):
    ...     __table__ = orders_table
    ...     items = relationship("Item", secondary=order_items_table)

    >>> class Item(Base):
    ...     __table__ = items_table

    >>> conn = engine.connect()
    >>> from sqlalchemy.orm import Session
    >>> session = Session(conn)
    >>> session.add_all(
    ...     [
    ...         User(
    ...             name="spongebob",
    ...             fullname="Spongebob Squarepants",
    ...             addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    ...         ),
    ...         User(
    ...             name="sandy",
    ...             fullname="Sandy Cheeks",
    ...             addresses=[
    ...                 Address(email_address="sandy@sqlalchemy.org"),
    ...                 Address(email_address="squirrel@squirrelpower.org"),
    ...             ],
    ...         ),
    ...         User(
    ...             name="patrick",
    ...             fullname="Patrick Star",
    ...             addresses=[Address(email_address="pat999@aol.com")],
    ...         ),
    ...         User(
    ...             name="squidward",
    ...             fullname="Squidward Tentacles",
    ...             addresses=[Address(email_address="stentcl@sqlalchemy.org")],
    ...         ),
    ...         User(name="ehkrabs", fullname="Eugene H. Krabs"),
    ...     ]
    ... )
    >>> session.commit()
    BEGIN ...
    >>> conn.begin()
    BEGIN ...


SELECT statements
=================

SELECT statements are produced by the :func:`_sql.select` function which
returns a :class:`_sql.Select` object::

    >>> from sqlalchemy import select
    >>> stmt = select(User).where(User.name == "spongebob")

To invoke a :class:`_sql.Select` with the ORM, it is passed to
:meth:`_orm.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',){stop}
    >>> for user_obj in result.scalars():
    ...     print(f"{user_obj.name} {user_obj.fullname}")
    spongebob Spongebob Squarepants


.. _orm_queryguide_select_columns:

Selecting ORM Entities and Attributes
--------------------------------------

The :func:`_sql.select` construct accepts ORM entities, including mapped
classes as well as class-level attributes representing mapped columns, which
are converted into ORM-annotated :class:`_sql.FromClause` and
:class:`_sql.ColumnElement` elements at construction time.

A :class:`_sql.Select` object that contains ORM-annotated entities is normally
executed using a :class:`_orm.Session` object, and not a :class:`_future.Connection`
object, so that ORM-related features may take effect, including that
instances of ORM-mapped objects may be returned.  When using the
:class:`_future.Connection` directly, result rows will only contain
column-level data.

Below we select from the ``User`` entity, producing a :class:`_sql.Select`
that selects from the mapped :class:`_schema.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
    [...] (){stop}

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 :class:`_engine.Result` returns :class:`_engine.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 :class:`_engine.Row` objects and instead
receive ORM entities directly, which is achieved using the
:meth:`_engine.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
    [...] (){stop}
    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 :func:`_sql.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
    [...] (){stop}

ORM attributes, themselves known as
:class:`_orm.InstrumentedAttribute`
objects, can be used in the same way as any :class:`_sql.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 :class:`_orm.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.user.fullname} {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
    [...] (){stop}
    spongebob Spongebob Squarepants spongebob@sqlalchemy.org
    sandy Sandy Cheeks sandy@sqlalchemy.org
    sandy Sandy Cheeks squirrel@squirrelpower.org
    patrick Patrick Star pat999@aol.com
    squidward Squidward Tentacles stentcl@sqlalchemy.org

The :class:`_orm.Bundle` is potentially useful for creating lightweight
views as well as custom column groupings such as mappings.

.. seealso::

    :ref:`bundles` - in the ORM loading documentation.


.. _orm_queryguide_orm_aliases:

Selecting ORM Aliases
^^^^^^^^^^^^^^^^^^^^^

As discussed in the tutorial at :ref:`tutorial_using_aliases`, to create a
SQL alias of an ORM entity is achieved using the :func:`_orm.aliased`
construct against a mapped class::

    >>> from sqlalchemy.orm import aliased
    >>> u1 = aliased(User)
    >>> print(select(u1).order_by(u1.id))
    {opensql}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 :meth:`_schema.Table.alias`, the SQL alias
is anonymously named.   For the case of selecting the entity from a row
with an explicit name, the :paramref:`_orm.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
    [...] (){stop}
    >>> print(f"{row.u1.name}")
    spongebob

The :class:`_orm.aliased` construct is also central to making use of subqueries
with the ORM; the sections :ref:`orm_queryguide_subqueries` and
:ref:`orm_queryguide_join_subqueries` discusses this further.


.. _orm_queryguide_selecting_text:

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 :func:`_sql.text` construct.   The
:func:`_sql.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
:meth:`_sql.TextClause.columns` method; when this method is invoked, the
:class:`_sql.TextClause` object is converted into a :class:`_sql.TextualSelect`
object, which takes on a role that is comparable to the :class:`_sql.Select`
construct.  The :meth:`_sql.TextClause.columns` method
is typically passed :class:`_schema.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
:class:`_sql.Select` construct using the :meth:`_sql.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)
    {opensql}SELECT id, name, fullname FROM user_account ORDER BY id
    [...] (){stop}
    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 :class:`_sql.TextualSelect` object can also be converted into
a subquery using the :meth:`_sql.TextualSelect.subquery` method,
and linked to the ``User`` entity to it using the :func:`_orm.aliased`
construct, in a similar manner as discussed below in :ref:`orm_queryguide_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)
    {opensql}SELECT anon_1.id, anon_1.name, anon_1.fullname
    FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
    [...] (){stop}
    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 :class:`_sql.TextualSelect` directly with
:meth:`_sql.Select.from_statement` versus making use of :func:`_sql.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.

.. seealso::

  :ref:`orm_dml_returning_objects` - The :meth:`_sql.Select.from_statement`
  method also works with :term:`DML` statements that support RETURNING.


.. _orm_queryguide_subqueries:

Selecting Entities from Subqueries
-----------------------------------

The :func:`_orm.aliased` construct discussed in the previous section
can be used with any :class:`_sql.Subquery` construct that comes from a
method such as :meth:`_sql.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)
    {opensql} 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,)
    {stop}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')

.. seealso::

    :ref:`tutorial_subqueries_orm_aliased` - in the :ref:`unified_tutorial`

    :ref:`orm_queryguide_join_subqueries`

.. _orm_queryguide_unions:

Selecting Entities from UNIONs and other set operations
--------------------------------------------------------

The :func:`_sql.union` and :func:`_sql.union_all` functions are the most
common set operations, which along with other set operations such as
:func:`_sql.except_`, :func:`_sql.intersect` and others deliver an object known as
a :class:`_sql.CompoundSelect`, which is composed of multiple
:class:`_sql.Select` constructs joined by a set-operation keyword.   ORM entities may
be selected from simple compound selects using the :meth:`_sql.Select.from_statement`
method illustrated previously at :ref:`orm_queryguide_selecting_text`.  In
this method, the UNION statement is the complete statement that will be
rendered, no additional criteria can be added after :meth:`_sql.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)
    {opensql}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)
    {stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
    User(id=3, name='patrick', fullname='Patrick Star')

A :class:`_sql.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 :func:`_orm.aliased`,
as illustrated previously at :ref:`orm_queryguide_subqueries`.  In the
example below, we first use :meth:`_sql.CompoundSelect.subquery` to create
a subquery of the UNION ALL statement, we then package that into the
:func:`_orm.aliased` construct where it can be used like any other mapped
entity in a :func:`_sql.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)
    {opensql}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)
    {stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
    User(id=3, name='patrick', fullname='Patrick Star')


.. seealso::

    :ref:`tutorial_orm_union` - in the :ref:`unified_tutorial`

.. _orm_queryguide_joins:

Joins
-----

The :meth:`_sql.Select.join` and :meth:`_sql.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 :ref:`tutorial_select_join`
in the :ref:`unified_tutorial`.

The usage of :meth:`_sql.Select.join` in an ORM context for :term:`2.0 style`
queries is mostly equivalent, minus legacy use cases, to the usage of the
:meth:`_orm.Query.join` method in :term:`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 :meth:`_sql.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 :meth:`_sql.Select.join` along
``User.addresses`` will result in SQL approximately equivalent to::

    >>> print(stmt)
    {opensql}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
:meth:`_sql.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 :meth:`_sql.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 :meth:`_sql.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 :ref:`many to many <relationships_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)
    {opensql}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 :meth:`_sql.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.   :meth:`_sql.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 :meth:`_sql.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)
    {opensql}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 :meth:`_sql.Select.join` allows any mapped entity or core
selectable construct as a target.   In this usage, :meth:`_sql.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)
    {opensql}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, :meth:`_sql.Select.join` is called upon to infer
the "on clause" automatically.  This calling form will ultimately raise
an error if either there are no :class:`_schema.ForeignKeyConstraint` setup
between the two mapped :class:`_schema.Table` constructs, or if there are multiple
:class:`_schema.ForeignKeyConstraint` linakges between them such that the
appropriate constraint to use is ambiguous.

.. note:: When making use of :meth:`_sql.Select.join` or :meth:`_sql.Select.join_from`
    without indicating an ON clause, ORM
    configured :func:`_orm.relationship` constructs are **not taken into account**.
    Only the configured :class:`_schema.ForeignKeyConstraint` relationships between
    the entities at the level of the mapped :class:`_schema.Table` objects are consulted
    when an attempt is made to infer an ON clause for the JOIN.

.. _queryguide_join_onclause:

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)
    {opensql}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)
    {opensql}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 :func:`_orm.aliased`, the
:func:`_orm.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)
    {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

When using relationship-bound attributes, the target entity can also be
substituted with an aliased entity by using the
:meth:`_orm.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)
    {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

.. _orm_queryguide_join_on_augmented:

Augmenting Built-in ON Clauses
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

As a substitute for providing a full custom ON condition for an
existing relationship, the :meth:`_orm.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)
    {opensql}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

.. seealso::

    The :meth:`_orm.PropComparator.and_` method also works with loader
    strategies. See the section :ref:`loader_option_criteria` for an example.

.. _orm_queryguide_join_subqueries:

Joining to Subqueries
^^^^^^^^^^^^^^^^^^^^^^^

The target of a join may be any "selectable" entity which usefully includes
subqueries.   When using the ORM, it is typical
that these targets are stated in terms of an
:func:`_orm.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 :class:`_sql.Subquery`
object using :meth:`_sql.Select.subquery`, which may then be used as the
target of the :meth:`_sql.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)
    {opensql}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{stop}

The above SELECT statement when invoked via :meth:`_orm.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 :func:`_orm.aliased` object against
the ``Address`` entity and the custom subquery.  Note we also apply a name
``"address"`` to the :func:`_orm.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}")
    {opensql}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',){stop}
    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 :class:`_orm.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}")
    {opensql}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'){stop}
    User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')


.. _orm_queryguide_select_from:

Controlling what to Join From
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In cases where the left side of the current state of
:class:`_sql.Select` is not in line with what we want to join from,
the :meth:`_sql.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 :meth:`_sql.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 :meth:`_sql.Select.join`
can be used subsequent, the :meth:`_sql.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 :meth:`_sql.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 :class:`_sql.Join` construct that refers to existing
    tables in a different order, the :class:`_sql.Join` construct takes
    precedence.    When we use methods like :meth:`_sql.Select.join`
    and :meth:`_sql.Select.join_from`, these methods are ultimately creating
    such a :class:`_sql.Join` object.   Therefore we can see the contents
    of :meth:`_sql.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 :class:`_sql.Join` construct above is added as another entry in the
    :meth:`_sql.Select.select_from` list which supersedes the previous entry.

Special Relationship Operators
------------------------------

As detailed in the :ref:`unified_tutorial` at
:ref:`tutorial_select_relationships`, ORM attributes mapped by
:func:`_orm.relationship` may be used in a variety of ways as SQL construction
helpers.  In addition to the above documentation on
:ref:`orm_queryguide_joins`, relationships may produce criteria to be used in
the WHERE clause as well.  See the linked sections below.

.. seealso::

    Sections in the :ref:`tutorial_orm_related_objects` section of the
    :ref:`unified_tutorial`:

    * :ref:`tutorial_relationship_exists` - helpers to generate EXISTS clauses
      using :func:`_orm.relationship`


    * :ref:`tutorial_relationship_operators` - helpers to create comparisons in
      terms of a :func:`_orm.relationship` in reference to a specific object
      instance


ORM Loader Options
-------------------

Loader options are objects that are passed to the :meth:`_sql.Select.options`
method which affect the loading of both column and relationship-oriented
attributes.  The majority of loader options descend from the :class:`_orm.Load`
hierarchy.  For a complete overview of using loader options, see the linked
sections below.

.. seealso::

    * :ref:`loading_columns` - details mapper and loading options that affect
      how column and SQL-expression mapped attributes are loaded

    * :ref:`loading_toplevel` - details relationship and loading options that
      affect how :func:`_orm.relationship` mapped attributes are loaded

.. _orm_queryguide_execution_options:

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
:meth:`_engine.Connection.execution_options`. In the ORM, execution options may
also be passed to :meth:`_orm.Session.execute` using the
:paramref:`_orm.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
:meth:`_sql.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.

.. _orm_queryguide_populate_existing:

Populate Existing
^^^^^^^^^^^^^^^^^^

The ``populate_existing`` execution option ensures that for all rows
loaded, the corresponding instances in the :class:`_orm.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 :class:`_orm.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 :func:`_orm.with_expression` option

* The :meth:`_orm.PropComparator.and_` method that can modify what a loader
  strategy loads

* The :func:`_orm.contains_eager` option

* The :func:`_orm.with_loader_criteria` option

The ``populate_existing`` execution option is equvialent to the
:meth:`_orm.Query.populate_existing` method in :term:`1.x style` ORM queries.

.. seealso::

    :ref:`faq_session_identity` - in :doc:`/faq/index`

    :ref:`session_expire` - in the ORM :class:`_orm.Session`
    documentation

.. _orm_queryguide_autoflush:

Autoflush
^^^^^^^^^

This option when passed as ``False`` will cause the :class:`_orm.Session`
to not invoke the "autoflush" step.  It's equivalent to using the
:attr:`_orm.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 :class:`_sql.Update` and
:class:`_sql.Delete` queries.

The ``autoflush`` execution option is equvialent to the
:meth:`_orm.Query.autoflush` method in :term:`1.x style` ORM queries.

.. seealso::

    :ref:`session_flushing`

.. _orm_queryguide_yield_per:

Fetching Large Result Sets with Yield Per
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The ``yield_per`` execution option is an integer value which will cause the
:class:`_engine.Result` to buffer only limited number of rows and/or ORM
objects at a time, before making data available to the client.

Normally, the ORM will construct ORM objects for **all** rows up front,
assembling them into a single buffer, before passing this buffer to
the :class:`_engine.Result` object as a source of rows to be returned.
The rationale for this behavior is to allow correct behavior
for features such as joined eager loading, uniquifying of results, and the
general case of result handling logic that relies upon the identity map
maintaining a consistent state for every object in a result set as it is
fetched.

The purpose of the ``yield_per`` option is to change this behavior so that the
ORM result set is optimized for iteration through very large result sets (> 10K
rows), where the user has determined that the above patterns don't apply. When
``yield_per`` is used, the ORM will instead batch ORM results into
sub-collections and yield rows from each sub-collection individually as the
:class:`_engine.Result` object is iterated, 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 option affects both the way the database
cursor is used as well as how the ORM constructs rows and objects to be
passed to the :class:`_engine.Result`.

.. tip::

    From the above, it follows that the :class:`_engine.Result` must be
    consumed in an iterable fashion, that is, using iteration such as
    ``for row in result`` or using partial row methods such as
    :meth:`_engine.Result.fetchmany` or :meth:`_engine.Result.partitions`.
    Calling :meth:`_engine.Result.all` will defeat the purpose of using
    ``yield_per``.

Using ``yield_per`` is equivalent to making use
of both the :paramref:`_engine.Connection.execution_options.stream_results`
execution option, which selects for server side cursors to be used
by the backend if supported, and the :meth:`_engine.Result.yield_per` method
on the returned :class:`_engine.Result` object,
which establishes a fixed size of rows to be fetched as well as a
corresponding limit to how many ORM objects will be constructed at once.

.. tip::

    ``yield_per`` is now available as a Core execution option as well,
    described in detail at :ref:`engine_stream_results`.  This section details
    the use of ``yield_per`` as an execution option with an ORM
    :class:`_orm.Session`.  The option behaves as similarly as possible
    in both contexts.

``yield_per`` when used with the ORM is typically established either
via the :meth:`.Executable.execution_options` method on the given statement
or by passing it to the :paramref:`_orm.Session.execute.execution_options`
parameter of :meth:`_orm.Session.execute` or other similar :class:`_orm.Session`
method.  In the example below its invoked upon a statement::

    >>> stmt = select(User).execution_options(yield_per=10)
    {sql}>>> for row in session.execute(stmt):
    ...     print(row)
    SELECT user_account.id, user_account.name, user_account.fullname
    FROM user_account
    [...] (){stop}
    (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
    ...

The above code is mostly equivalent as making use of the
:paramref:`_engine.Connection.execution_options.stream_results` execution
option, setting the :paramref:`_engine.Connection.execution_options.max_row_buffer`
to the given integer size, and then using the :meth:`_engine.Result.yield_per`
method on the :class:`_engine.Result` returned by the
:class:`_orm.Session`, as in the following example::

    # equivalent code
    >>> stmt = select(User).execution_options(stream_results=True, max_row_buffer=10)
    {sql}>>> for row in session.execute(stmt).yield_per(10):
    ...     print(row)
    SELECT user_account.id, user_account.name, user_account.fullname
    FROM user_account
    [...] (){stop}
    (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
    ...

``yield_per`` is also commonly used in combination with the
:meth:`_engine.Result.partitions` method, that will iterate rows in grouped
partitions. The size of each partition defaults to the integer value passed to
``yield_per``, as in the below example::

    >>> stmt = select(User).execution_options(yield_per=10)
    {sql}>>> for partition in session.execute(stmt).partitions():
    ...     for row in partition:
    ...         print(row)
    SELECT user_account.id, user_account.name, user_account.fullname
    FROM user_account
    [...] (){stop}
    (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
    ...

The ``yield_per`` execution option **is not compatible** with
:ref:`"subquery" eager loading <subquery_eager_loading>` loading or
:ref:`"joined" eager loading <joined_eager_loading>` when using collections. It
is potentially compatible with :ref:`"select in" eager loading
<selectin_eager_loading>` , provided the database driver supports multiple,
independent cursors.

Additionally, the ``yield_per`` execution option is not compatible
with the :meth:`_engine.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.

.. versionchanged:: 1.4.6  An exception is raised when ORM rows are fetched
   from a :class:`_engine.Result` object that makes use of the
   :meth:`_engine.Result.unique` filter, at the same time as the ``yield_per``
   execution option is used.

When using the legacy :class:`_orm.Query` object with
:term:`1.x style` ORM use, the :meth:`_orm.Query.yield_per` method
will have the same result as that of the ``yield_per`` execution option.


.. seealso::

    :ref:`engine_stream_results`


ORM Update / Delete with Arbitrary WHERE clause
================================================

The :meth:`_orm.Session.execute` method, in addition to handling ORM-enabled
:class:`_sql.Select` objects, can also accommodate ORM-enabled
:class:`_sql.Update` and :class:`_sql.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 :class:`_orm.Session`. See the section
:ref:`orm_expression_update_delete` for background on this feature.


..  Setup code, not for display

    >>> conn.close()
    ROLLBACK

.. _queryguide_inspection:

Inspecting entities and columns from ORM-enabled SELECT and DML statements
==========================================================================

The :func:`_sql.select` construct, as well as the :func:`_sql.insert`, :func:`_sql.update`
and :func:`_sql.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 :class:`.Select` object, this information is available from the
:attr:`.Select.column_descriptions` attribute. This attribute operates in the
same way as the legacy :attr:`.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 :attr:`.Select.column_descriptions` is used with non-ORM objects
such as plain :class:`.Table` or :class:`.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()}]

.. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions` attribute now returns
   a value when used against a :class:`.Select` that is not ORM-enabled.  Previously,
   this would raise ``NotImplementedError``.


For :func:`_sql.insert`, :func:`.update` and :func:`.delete` constructs, there are
two separate attributes. One is :attr:`.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 :attr:`.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 :attr:`.UpdateBase.returning_column_descriptions` which
delivers information about the columns present in the RETURNING collection
in a manner roughly similar to that of :attr:`.Select.column_descriptions`::

    >>> pprint(stmt.returning_column_descriptions)
    [{'aliased': False,
        'entity': <class 'User'>,
        'expr': <sqlalchemy.orm.attributes.InstrumentedAttribute ...>,
        'name': 'id',
        'type': Integer()}]

.. versionadded:: 1.4.33 Added the :attr:`.UpdateBase.entity_description`
   and :attr:`.UpdateBase.returning_column_descriptions` attributes.