mirror of
https://github.com/sqlalchemy/sqlalchemy.git
synced 2026-06-23 00:51:10 -04:00
Federico Caselli
16d3dad449
Change-Id: If278ea170e0a17b1e8ace2d470fb2fbdb7a6e9c1 References: #9370 References: #9365
348 lines
13 KiB
ReStructuredText
348 lines
13 KiB
ReStructuredText
.. currentmodule:: sqlalchemy.orm
|
|
|
|
.. _mapper_sql_expressions:
|
|
|
|
SQL Expressions as Mapped Attributes
|
|
====================================
|
|
|
|
Attributes on a mapped class can be linked to SQL expressions, which can
|
|
be used in queries.
|
|
|
|
Using a Hybrid
|
|
--------------
|
|
|
|
The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called
|
|
"hybrid attribute",
|
|
described in the section :ref:`hybrids_toplevel`. The hybrid provides
|
|
for an expression that works at both the Python level as well as at the
|
|
SQL expression level. For example, below we map a class ``User``,
|
|
containing attributes ``firstname`` and ``lastname``, and include a hybrid that
|
|
will provide for us the ``fullname``, which is the string concatenation of the two::
|
|
|
|
from sqlalchemy.ext.hybrid import hybrid_property
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
firstname = mapped_column(String(50))
|
|
lastname = mapped_column(String(50))
|
|
|
|
@hybrid_property
|
|
def fullname(self):
|
|
return self.firstname + " " + self.lastname
|
|
|
|
Above, the ``fullname`` attribute is interpreted at both the instance and
|
|
class level, so that it is available from an instance::
|
|
|
|
some_user = session.scalars(select(User).limit(1)).first()
|
|
print(some_user.fullname)
|
|
|
|
as well as usable within queries::
|
|
|
|
some_user = session.scalars(
|
|
select(User).where(User.fullname == "John Smith").limit(1)
|
|
).first()
|
|
|
|
The string concatenation example is a simple one, where the Python expression
|
|
can be dual purposed at the instance and class level. Often, the SQL expression
|
|
must be distinguished from the Python expression, which can be achieved using
|
|
:meth:`.hybrid_property.expression`. Below we illustrate the case where a conditional
|
|
needs to be present inside the hybrid, using the ``if`` statement in Python and the
|
|
:func:`_expression.case` construct for SQL expressions::
|
|
|
|
from sqlalchemy.ext.hybrid import hybrid_property
|
|
from sqlalchemy.sql import case
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
firstname = mapped_column(String(50))
|
|
lastname = mapped_column(String(50))
|
|
|
|
@hybrid_property
|
|
def fullname(self):
|
|
if self.firstname is not None:
|
|
return self.firstname + " " + self.lastname
|
|
else:
|
|
return self.lastname
|
|
|
|
@fullname.expression
|
|
def fullname(cls):
|
|
return case(
|
|
(cls.firstname != None, cls.firstname + " " + cls.lastname),
|
|
else_=cls.lastname,
|
|
)
|
|
|
|
.. _mapper_column_property_sql_expressions:
|
|
|
|
Using column_property
|
|
---------------------
|
|
|
|
The :func:`_orm.column_property` function can be used to map a SQL
|
|
expression in a manner similar to a regularly mapped :class:`_schema.Column`.
|
|
With this technique, the attribute is loaded
|
|
along with all other column-mapped attributes at load time. This is in some
|
|
cases an advantage over the usage of hybrids, as the value can be loaded
|
|
up front at the same time as the parent row of the object, particularly if
|
|
the expression is one which links to other tables (typically as a correlated
|
|
subquery) to access data that wouldn't normally be
|
|
available on an already loaded object.
|
|
|
|
Disadvantages to using :func:`_orm.column_property` for SQL expressions include that
|
|
the expression must be compatible with the SELECT statement emitted for the class
|
|
as a whole, and there are also some configurational quirks which can occur
|
|
when using :func:`_orm.column_property` from declarative mixins.
|
|
|
|
Our "fullname" example can be expressed using :func:`_orm.column_property` as
|
|
follows::
|
|
|
|
from sqlalchemy.orm import column_property
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
firstname = mapped_column(String(50))
|
|
lastname = mapped_column(String(50))
|
|
fullname = column_property(firstname + " " + lastname)
|
|
|
|
Correlated subqueries may be used as well. Below we use the
|
|
:func:`_expression.select` construct to create a :class:`_sql.ScalarSelect`,
|
|
representing a column-oriented SELECT statement, that links together the count
|
|
of ``Address`` objects available for a particular ``User``::
|
|
|
|
from sqlalchemy.orm import column_property
|
|
from sqlalchemy import select, func
|
|
from sqlalchemy import Column, Integer, String, ForeignKey
|
|
|
|
from sqlalchemy.orm import DeclarativeBase
|
|
|
|
|
|
class Base(DeclarativeBase):
|
|
pass
|
|
|
|
|
|
class Address(Base):
|
|
__tablename__ = "address"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
user_id = mapped_column(Integer, ForeignKey("user.id"))
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
address_count = column_property(
|
|
select(func.count(Address.id))
|
|
.where(Address.user_id == id)
|
|
.correlate_except(Address)
|
|
.scalar_subquery()
|
|
)
|
|
|
|
In the above example, we define a :func:`_expression.ScalarSelect` construct like the following::
|
|
|
|
stmt = (
|
|
select(func.count(Address.id))
|
|
.where(Address.user_id == id)
|
|
.correlate_except(Address)
|
|
.scalar_subquery()
|
|
)
|
|
|
|
Above, we first use :func:`_sql.select` to create a :class:`_sql.Select`
|
|
construct, which we then convert into a :term:`scalar subquery` using the
|
|
:meth:`_sql.Select.scalar_subquery` method, indicating our intent to use this
|
|
:class:`_sql.Select` statement in a column expression context.
|
|
|
|
Within the :class:`_sql.Select` itself, we select the count of ``Address.id`` rows
|
|
where the ``Address.user_id`` column is equated to ``id``, which in the context
|
|
of the ``User`` class is the :class:`_schema.Column` named ``id`` (note that ``id`` is
|
|
also the name of a Python built in function, which is not what we want to use
|
|
here - if we were outside of the ``User`` class definition, we'd use ``User.id``).
|
|
|
|
The :meth:`_sql.Select.correlate_except` method indicates that each element in the
|
|
FROM clause of this :func:`_expression.select` may be omitted from the FROM list (that is, correlated
|
|
to the enclosing SELECT statement against ``User``) except for the one corresponding
|
|
to ``Address``. This isn't strictly necessary, but prevents ``Address`` from
|
|
being inadvertently omitted from the FROM list in the case of a long string
|
|
of joins between ``User`` and ``Address`` tables where SELECT statements against
|
|
``Address`` are nested.
|
|
|
|
For a :func:`.column_property` that refers to columns linked from a
|
|
many-to-many relationship, use :func:`.and_` to join the fields of the
|
|
association table to both tables in a relationship::
|
|
|
|
from sqlalchemy import and_
|
|
|
|
|
|
class Author(Base):
|
|
# ...
|
|
|
|
book_count = column_property(
|
|
select(func.count(books.c.id))
|
|
.where(
|
|
and_(
|
|
book_authors.c.author_id == authors.c.id,
|
|
book_authors.c.book_id == books.c.id,
|
|
)
|
|
)
|
|
.scalar_subquery()
|
|
)
|
|
|
|
Adding column_property() to an existing Declarative mapped class
|
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
|
|
If import issues prevent the :func:`.column_property` from being defined
|
|
inline with the class, it can be assigned to the class after both
|
|
are configured. When using mappings that make use of a Declarative
|
|
base class (i.e. produced by the :class:`_orm.DeclarativeBase` superclass
|
|
or legacy functions such as :func:`_orm.declarative_base`),
|
|
this attribute assignment has the effect of calling :meth:`_orm.Mapper.add_property`
|
|
to add an additional property after the fact::
|
|
|
|
# only works if a declarative base class is in use
|
|
User.address_count = column_property(
|
|
select(func.count(Address.id)).where(Address.user_id == User.id).scalar_subquery()
|
|
)
|
|
|
|
When using mapping styles that don't use Declarative base classes
|
|
such as the :meth:`_orm.registry.mapped` decorator, the :meth:`_orm.Mapper.add_property`
|
|
method may be invoked explicitly on the underlying :class:`_orm.Mapper` object,
|
|
which can be obtained using :func:`_sa.inspect`::
|
|
|
|
from sqlalchemy.orm import registry
|
|
|
|
reg = registry()
|
|
|
|
|
|
@reg.mapped
|
|
class User:
|
|
__tablename__ = "user"
|
|
|
|
# ... additional mapping directives
|
|
|
|
|
|
# later ...
|
|
|
|
# works for any kind of mapping
|
|
from sqlalchemy import inspect
|
|
|
|
inspect(User).add_property(
|
|
column_property(
|
|
select(func.count(Address.id))
|
|
.where(Address.user_id == User.id)
|
|
.scalar_subquery()
|
|
)
|
|
)
|
|
|
|
.. seealso::
|
|
|
|
:ref:`orm_declarative_table_adding_columns`
|
|
|
|
|
|
.. _mapper_column_property_sql_expressions_composed:
|
|
|
|
Composing from Column Properties at Mapping Time
|
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
|
|
It is possible to create mappings that combine multiple
|
|
:class:`.ColumnProperty` objects together. The :class:`.ColumnProperty` will
|
|
be interpreted as a SQL expression when used in a Core expression context,
|
|
provided that it is targeted by an existing expression object; this works by
|
|
the Core detecting that the object has a ``__clause_element__()`` method which
|
|
returns a SQL expression. However, if the :class:`.ColumnProperty` is used as
|
|
a lead object in an expression where there is no other Core SQL expression
|
|
object to target it, the :attr:`.ColumnProperty.expression` attribute will
|
|
return the underlying SQL expression so that it can be used to build SQL
|
|
expressions consistently. Below, the ``File`` class contains an attribute
|
|
``File.path`` that concatenates a string token to the ``File.filename``
|
|
attribute, which is itself a :class:`.ColumnProperty`::
|
|
|
|
|
|
class File(Base):
|
|
__tablename__ = "file"
|
|
|
|
id = mapped_column(Integer, primary_key=True)
|
|
name = mapped_column(String(64))
|
|
extension = mapped_column(String(8))
|
|
filename = column_property(name + "." + extension)
|
|
path = column_property("C:/" + filename.expression)
|
|
|
|
When the ``File`` class is used in expressions normally, the attributes
|
|
assigned to ``filename`` and ``path`` are usable directly. The use of the
|
|
:attr:`.ColumnProperty.expression` attribute is only necessary when using
|
|
the :class:`.ColumnProperty` directly within the mapping definition::
|
|
|
|
stmt = select(File.path).where(File.filename == "foo.txt")
|
|
|
|
Using Column Deferral with ``column_property()``
|
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
|
|
The column deferral feature introduced in the :ref:`queryguide_toplevel`
|
|
at :ref:`orm_queryguide_column_deferral` may be applied at mapping time
|
|
to a SQL expression mapped by :func:`_orm.column_property` by using the
|
|
:func:`_orm.deferred` function in place of :func:`_orm.column_property`::
|
|
|
|
from sqlalchemy.orm import deferred
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
firstname: Mapped[str] = mapped_column()
|
|
lastname: Mapped[str] = mapped_column()
|
|
fullname: Mapped[str] = deferred(firstname + " " + lastname)
|
|
|
|
.. seealso::
|
|
|
|
:ref:`orm_queryguide_deferred_imperative`
|
|
|
|
|
|
|
|
Using a plain descriptor
|
|
------------------------
|
|
|
|
In cases where a SQL query more elaborate than what :func:`_orm.column_property`
|
|
or :class:`.hybrid_property` can provide must be emitted, a regular Python
|
|
function accessed as an attribute can be used, assuming the expression
|
|
only needs to be available on an already-loaded instance. The function
|
|
is decorated with Python's own ``@property`` decorator to mark it as a read-only
|
|
attribute. Within the function, :func:`.object_session`
|
|
is used to locate the :class:`.Session` corresponding to the current object,
|
|
which is then used to emit a query::
|
|
|
|
from sqlalchemy.orm import object_session
|
|
from sqlalchemy import select, func
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "user"
|
|
id = mapped_column(Integer, primary_key=True)
|
|
firstname = mapped_column(String(50))
|
|
lastname = mapped_column(String(50))
|
|
|
|
@property
|
|
def address_count(self):
|
|
return object_session(self).scalar(
|
|
select(func.count(Address.id)).where(Address.user_id == self.id)
|
|
)
|
|
|
|
The plain descriptor approach is useful as a last resort, but is less performant
|
|
in the usual case than both the hybrid and column property approaches, in that
|
|
it needs to emit a SQL query upon each access.
|
|
|
|
.. _mapper_querytime_expression:
|
|
|
|
Query-time SQL expressions as mapped attributes
|
|
-----------------------------------------------
|
|
|
|
In addition to being able to configure fixed SQL expressions on mapped classes,
|
|
the SQLAlchemy ORM also includes a feature wherein objects may be loaded
|
|
with the results of arbitrary SQL expressions which are set up at query time as part
|
|
of their state. This behavior is available by configuring an ORM mapped
|
|
attribute using :func:`_orm.query_expression` and then using the
|
|
:func:`_orm.with_expression` loader option at query time. See the section
|
|
:ref:`orm_queryguide_with_expression` for an example mapping and usage.
|
|
|