Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# ext/hybrid.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8r"""Define attributes on ORM-mapped classes that have "hybrid" behavior.
10"hybrid" means the attribute has distinct behaviors defined at the
11class level and at the instance level.
13The :mod:`~sqlalchemy.ext.hybrid` extension provides a special form of
14method decorator and has minimal dependencies on the rest of SQLAlchemy.
15Its basic theory of operation can work with any descriptor-based expression
16system.
18Consider a mapping ``Interval``, representing integer ``start`` and ``end``
19values. We can define higher level functions on mapped classes that produce SQL
20expressions at the class level, and Python expression evaluation at the
21instance level. Below, each function decorated with :class:`.hybrid_method` or
22:class:`.hybrid_property` may receive ``self`` as an instance of the class, or
23may receive the class directly, depending on context::
25 from __future__ import annotations
27 from sqlalchemy.ext.hybrid import hybrid_method
28 from sqlalchemy.ext.hybrid import hybrid_property
29 from sqlalchemy.orm import DeclarativeBase
30 from sqlalchemy.orm import Mapped
31 from sqlalchemy.orm import mapped_column
34 class Base(DeclarativeBase):
35 pass
38 class Interval(Base):
39 __tablename__ = "interval"
41 id: Mapped[int] = mapped_column(primary_key=True)
42 start: Mapped[int]
43 end: Mapped[int]
45 def __init__(self, start: int, end: int):
46 self.start = start
47 self.end = end
49 @hybrid_property
50 def length(self) -> int:
51 return self.end - self.start
53 @hybrid_method
54 def contains(self, point: int) -> bool:
55 return (self.start <= point) & (point <= self.end)
57 @hybrid_method
58 def intersects(self, other: Interval) -> bool:
59 return self.contains(other.start) | self.contains(other.end)
61Above, the ``length`` property returns the difference between the
62``end`` and ``start`` attributes. With an instance of ``Interval``,
63this subtraction occurs in Python, using normal Python descriptor
64mechanics::
66 >>> i1 = Interval(5, 10)
67 >>> i1.length
68 5
70When dealing with the ``Interval`` class itself, the :class:`.hybrid_property`
71descriptor evaluates the function body given the ``Interval`` class as
72the argument, which when evaluated with SQLAlchemy expression mechanics
73returns a new SQL expression:
75.. sourcecode:: pycon+sql
77 >>> from sqlalchemy import select
78 >>> print(select(Interval.length))
79 {printsql}SELECT interval."end" - interval.start AS length
80 FROM interval{stop}
83 >>> print(select(Interval).filter(Interval.length > 10))
84 {printsql}SELECT interval.id, interval.start, interval."end"
85 FROM interval
86 WHERE interval."end" - interval.start > :param_1
88Filtering methods such as :meth:`.Select.filter_by` are supported
89with hybrid attributes as well:
91.. sourcecode:: pycon+sql
93 >>> print(select(Interval).filter_by(length=5))
94 {printsql}SELECT interval.id, interval.start, interval."end"
95 FROM interval
96 WHERE interval."end" - interval.start = :param_1
98The ``Interval`` class example also illustrates two methods,
99``contains()`` and ``intersects()``, decorated with
100:class:`.hybrid_method`. This decorator applies the same idea to
101methods that :class:`.hybrid_property` applies to attributes. The
102methods return boolean values, and take advantage of the Python ``|``
103and ``&`` bitwise operators to produce equivalent instance-level and
104SQL expression-level boolean behavior:
106.. sourcecode:: pycon+sql
108 >>> i1.contains(6)
109 True
110 >>> i1.contains(15)
111 False
112 >>> i1.intersects(Interval(7, 18))
113 True
114 >>> i1.intersects(Interval(25, 29))
115 False
117 >>> print(select(Interval).filter(Interval.contains(15)))
118 {printsql}SELECT interval.id, interval.start, interval."end"
119 FROM interval
120 WHERE interval.start <= :start_1 AND interval."end" > :end_1{stop}
122 >>> ia = aliased(Interval)
123 >>> print(select(Interval, ia).filter(Interval.intersects(ia)))
124 {printsql}SELECT interval.id, interval.start,
125 interval."end", interval_1.id AS interval_1_id,
126 interval_1.start AS interval_1_start, interval_1."end" AS interval_1_end
127 FROM interval, interval AS interval_1
128 WHERE interval.start <= interval_1.start
129 AND interval."end" > interval_1.start
130 OR interval.start <= interval_1."end"
131 AND interval."end" > interval_1."end"{stop}
133.. _hybrid_distinct_expression:
135Defining Expression Behavior Distinct from Attribute Behavior
136--------------------------------------------------------------
138In the previous section, our usage of the ``&`` and ``|`` bitwise operators
139within the ``Interval.contains`` and ``Interval.intersects`` methods was
140fortunate, considering our functions operated on two boolean values to return a
141new one. In many cases, the construction of an in-Python function and a
142SQLAlchemy SQL expression have enough differences that two separate Python
143expressions should be defined. The :mod:`~sqlalchemy.ext.hybrid` decorator
144defines a **modifier** :meth:`.hybrid_property.expression` for this purpose. As an
145example we'll define the radius of the interval, which requires the usage of
146the absolute value function::
148 from sqlalchemy import ColumnElement
149 from sqlalchemy import Float
150 from sqlalchemy import func
151 from sqlalchemy import type_coerce
154 class Interval(Base):
155 # ...
157 @hybrid_property
158 def radius(self) -> float:
159 return abs(self.length) / 2
161 @radius.inplace.expression
162 @classmethod
163 def _radius_expression(cls) -> ColumnElement[float]:
164 return type_coerce(func.abs(cls.length) / 2, Float)
166In the above example, the :class:`.hybrid_property` first assigned to the
167name ``Interval.radius`` is amended by a subsequent method called
168``Interval._radius_expression``, using the decorator
169``@radius.inplace.expression``, which chains together two modifiers
170:attr:`.hybrid_property.inplace` and :attr:`.hybrid_property.expression`.
171The use of :attr:`.hybrid_property.inplace` indicates that the
172:meth:`.hybrid_property.expression` modifier should mutate the
173existing hybrid object at ``Interval.radius`` in place, without creating a
174new object. Notes on this modifier and its
175rationale are discussed in the next section :ref:`hybrid_pep484_naming`.
176The use of ``@classmethod`` is optional, and is strictly to give typing
177tools a hint that ``cls`` in this case is expected to be the ``Interval``
178class, and not an instance of ``Interval``.
180.. note:: :attr:`.hybrid_property.inplace` as well as the use of ``@classmethod``
181 for proper typing support are available as of SQLAlchemy 2.0.4, and will
182 not work in earlier versions.
184With ``Interval.radius`` now including an expression element, the SQL
185function ``ABS()`` is returned when accessing ``Interval.radius``
186at the class level:
188.. sourcecode:: pycon+sql
190 >>> from sqlalchemy import select
191 >>> print(select(Interval).filter(Interval.radius > 5))
192 {printsql}SELECT interval.id, interval.start, interval."end"
193 FROM interval
194 WHERE abs(interval."end" - interval.start) / :abs_1 > :param_1
197.. _hybrid_pep484_naming:
199Using ``inplace`` to create pep-484 compliant hybrid properties
200---------------------------------------------------------------
202In the previous section, a :class:`.hybrid_property` decorator is illustrated
203which includes two separate method-level functions being decorated, both
204to produce a single object attribute referenced as ``Interval.radius``.
205There are actually several different modifiers we can use for
206:class:`.hybrid_property` including :meth:`.hybrid_property.expression`,
207:meth:`.hybrid_property.setter` and :meth:`.hybrid_property.update_expression`.
209SQLAlchemy's :class:`.hybrid_property` decorator intends that adding on these
210methods may be done in the identical manner as Python's built-in
211``@property`` decorator, where idiomatic use is to continue to redefine the
212attribute repeatedly, using the **same attribute name** each time, as in the
213example below that illustrates the use of :meth:`.hybrid_property.setter` and
214:meth:`.hybrid_property.expression` for the ``Interval.radius`` descriptor::
216 # correct use, however is not accepted by pep-484 tooling
219 class Interval(Base):
220 # ...
222 @hybrid_property
223 def radius(self):
224 return abs(self.length) / 2
226 @radius.setter
227 def radius(self, value):
228 self.length = value * 2
230 @radius.expression
231 def radius(cls):
232 return type_coerce(func.abs(cls.length) / 2, Float)
234Above, there are three ``Interval.radius`` methods, but as each are decorated,
235first by the :class:`.hybrid_property` decorator and then by the
236``@radius`` name itself, the end effect is that ``Interval.radius`` is
237a single attribute with three different functions contained within it.
238This style of use is taken from `Python's documented use of @property
239<https://docs.python.org/3/library/functions.html#property>`_.
240It is important to note that the way both ``@property`` as well as
241:class:`.hybrid_property` work, a **copy of the descriptor is made each time**.
242That is, each call to ``@radius.expression``, ``@radius.setter`` etc.
243make a new object entirely. This allows the attribute to be re-defined in
244subclasses without issue (see :ref:`hybrid_reuse_subclass` later in this
245section for how this is used).
247However, the above approach is not compatible with typing tools such as
248mypy and pyright. Python's own ``@property`` decorator does not have this
249limitation only because
250`these tools hardcode the behavior of @property
251<https://github.com/python/typing/discussions/1102>`_, meaning this syntax
252is not available to SQLAlchemy under :pep:`484` compliance.
254In order to produce a reasonable syntax while remaining typing compliant,
255the :attr:`.hybrid_property.inplace` decorator allows the same
256decorator to be reused with different method names, while still producing
257a single decorator under one name::
259 # correct use which is also accepted by pep-484 tooling
262 class Interval(Base):
263 # ...
265 @hybrid_property
266 def radius(self) -> float:
267 return abs(self.length) / 2
269 @radius.inplace.setter
270 def _radius_setter(self, value: float) -> None:
271 # for example only
272 self.length = value * 2
274 @radius.inplace.expression
275 @classmethod
276 def _radius_expression(cls) -> ColumnElement[float]:
277 return type_coerce(func.abs(cls.length) / 2, Float)
279Using :attr:`.hybrid_property.inplace` further qualifies the use of the
280decorator that a new copy should not be made, thereby maintaining the
281``Interval.radius`` name while allowing additional methods
282``Interval._radius_setter`` and ``Interval._radius_expression`` to be
283differently named.
286.. versionadded:: 2.0.4 Added :attr:`.hybrid_property.inplace` to allow
287 less verbose construction of composite :class:`.hybrid_property` objects
288 while not having to use repeated method names. Additionally allowed the
289 use of ``@classmethod`` within :attr:`.hybrid_property.expression`,
290 :attr:`.hybrid_property.update_expression`, and
291 :attr:`.hybrid_property.comparator` to allow typing tools to identify
292 ``cls`` as a class and not an instance in the method signature.
295Defining Setters
296----------------
298The :meth:`.hybrid_property.setter` modifier allows the construction of a
299custom setter method, that can modify values on the object::
301 class Interval(Base):
302 # ...
304 @hybrid_property
305 def length(self) -> int:
306 return self.end - self.start
308 @length.inplace.setter
309 def _length_setter(self, value: int) -> None:
310 self.end = self.start + value
312The ``length(self, value)`` method is now called upon set::
314 >>> i1 = Interval(5, 10)
315 >>> i1.length
316 5
317 >>> i1.length = 12
318 >>> i1.end
319 17
321.. _hybrid_bulk_update:
323Supporting ORM Bulk INSERT and UPDATE
324-------------------------------------
326Hybrids have support for use in ORM Bulk INSERT/UPDATE operations described
327at :ref:`orm_expression_update_delete`. There are two distinct hooks
328that may be used supply a hybrid value within a DML operation:
3301. The :meth:`.hybrid_property.update_expression` hook indicates a method that
331 can provide one or more expressions to render in the SET clause of an
332 UPDATE or INSERT statement, in response to when a hybrid attribute is referenced
333 directly in the :meth:`.UpdateBase.values` method; i.e. the use shown
334 in :ref:`orm_queryguide_update_delete_where` and :ref:`orm_queryguide_insert_values`
3362. The :meth:`.hybrid_property.bulk_dml` hook indicates a method that
337 can intercept individual parameter dictionaries sent to :meth:`_orm.Session.execute`,
338 i.e. the use shown at :ref:`orm_queryguide_bulk_insert` as well
339 as :ref:`orm_queryguide_bulk_update`.
341Using update_expression with update.values() and insert.values()
342^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
344The :meth:`.hybrid_property.update_expression` decorator indicates a method
345that is invoked when a hybrid is used in the :meth:`.ValuesBase.values` clause
346of an :func:`_sql.update` or :func:`_sql.insert` statement. It returns a list
347of tuple pairs ``[(x1, y1), (x2, y2), ...]`` which will expand into the SET
348clause of an UPDATE statement as ``SET x1=y1, x2=y2, ...``.
350The :func:`_sql.from_dml_column` construct is often useful as it can create a
351SQL expression that refers to another column that may also present in the same
352INSERT or UPDATE statement, alternatively falling back to referring to the
353original column if such an expression is not present.
355In the example below, the ``total_price`` hybrid will derive the ``price``
356column, by taking the given "total price" value and dividing it by a
357``tax_rate`` value that is also present in the :meth:`.ValuesBase.values` call::
359 from sqlalchemy import from_dml_column
362 class Product(Base):
363 __tablename__ = "product"
365 id: Mapped[int] = mapped_column(primary_key=True)
366 price: Mapped[float]
367 tax_rate: Mapped[float]
369 @hybrid_property
370 def total_price(self) -> float:
371 return self.price * (1 + self.tax_rate)
373 @total_price.inplace.update_expression
374 @classmethod
375 def _total_price_update_expression(
376 cls, value: Any
377 ) -> List[Tuple[Any, Any]]:
378 return [(cls.price, value / (1 + from_dml_column(cls.tax_rate)))]
380When used in an UPDATE statement, :func:`_sql.from_dml_column` creates a
381reference to the ``tax_rate`` column that will use the value passed to
382the :meth:`.ValuesBase.values` method, rather than the existing value on the column
383in the database. This allows the hybrid to access other values being
384updated in the same statement:
386.. sourcecode:: pycon+sql
388 >>> from sqlalchemy import update
389 >>> print(
390 ... update(Product).values(
391 ... {Product.tax_rate: 0.08, Product.total_price: 125.00}
392 ... )
393 ... )
394 {printsql}UPDATE product SET tax_rate=:tax_rate, price=(:total_price / (:tax_rate + :param_1))
396When the column referenced by :func:`_sql.from_dml_column` (in this case ``product.tax_rate``)
397is omitted from :meth:`.ValuesBase.values`, the rendered expression falls back to
398using the original column:
400.. sourcecode:: pycon+sql
402 >>> from sqlalchemy import update
403 >>> print(update(Product).values({Product.total_price: 125.00}))
404 {printsql}UPDATE product SET price=(:total_price / (tax_rate + :param_1))
408Using bulk_dml to intercept bulk parameter dictionaries
409^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
411.. versionadded:: 2.1
413For bulk operations that pass a list of parameter dictionaries to
414methods like :meth:`.Session.execute`, the
415:meth:`.hybrid_property.bulk_dml` decorator provides a hook that can
416receive each dictionary and populate it with new values.
418The implementation for the :meth:`.hybrid_property.bulk_dml` hook can retrieve
419other column values from the parameter dictionary::
421 from typing import MutableMapping
424 class Product(Base):
425 __tablename__ = "product"
427 id: Mapped[int] = mapped_column(primary_key=True)
428 price: Mapped[float]
429 tax_rate: Mapped[float]
431 @hybrid_property
432 def total_price(self) -> float:
433 return self.price * (1 + self.tax_rate)
435 @total_price.inplace.bulk_dml
436 @classmethod
437 def _total_price_bulk_dml(
438 cls, mapping: MutableMapping[str, Any], value: float
439 ) -> None:
440 mapping["price"] = value / (1 + mapping["tax_rate"])
442This allows for bulk INSERT/UPDATE with derived values::
444 # Bulk INSERT
445 session.execute(
446 insert(Product),
447 [
448 {"tax_rate": 0.08, "total_price": 125.00},
449 {"tax_rate": 0.05, "total_price": 110.00},
450 ],
451 )
453Note that the method decorated by :meth:`.hybrid_property.bulk_dml` is invoked
454only with parameter dictionaries and does not have the ability to use
455SQL expressions in the given dictionaries, only literal Python values that will
456be passed to parameters in the INSERT or UPDATE statement.
458.. seealso::
460 :ref:`orm_expression_update_delete` - includes background on ORM-enabled
461 UPDATE statements
464Working with Relationships
465--------------------------
467There's no essential difference when creating hybrids that work with
468related objects as opposed to column-based data. The need for distinct
469expressions tends to be greater. The two variants we'll illustrate
470are the "join-dependent" hybrid, and the "correlated subquery" hybrid.
472Join-Dependent Relationship Hybrid
473^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
475Consider the following declarative
476mapping which relates a ``User`` to a ``SavingsAccount``::
478 from __future__ import annotations
480 from decimal import Decimal
481 from typing import cast
482 from typing import List
483 from typing import Optional
485 from sqlalchemy import ForeignKey
486 from sqlalchemy import Numeric
487 from sqlalchemy import String
488 from sqlalchemy import SQLColumnExpression
489 from sqlalchemy.ext.hybrid import hybrid_property
490 from sqlalchemy.orm import DeclarativeBase
491 from sqlalchemy.orm import Mapped
492 from sqlalchemy.orm import mapped_column
493 from sqlalchemy.orm import relationship
496 class Base(DeclarativeBase):
497 pass
500 class SavingsAccount(Base):
501 __tablename__ = "account"
502 id: Mapped[int] = mapped_column(primary_key=True)
503 user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
504 balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
506 owner: Mapped[User] = relationship(back_populates="accounts")
509 class User(Base):
510 __tablename__ = "user"
511 id: Mapped[int] = mapped_column(primary_key=True)
512 name: Mapped[str] = mapped_column(String(100))
514 accounts: Mapped[List[SavingsAccount]] = relationship(
515 back_populates="owner", lazy="selectin"
516 )
518 @hybrid_property
519 def balance(self) -> Optional[Decimal]:
520 if self.accounts:
521 return self.accounts[0].balance
522 else:
523 return None
525 @balance.inplace.setter
526 def _balance_setter(self, value: Optional[Decimal]) -> None:
527 assert value is not None
529 if not self.accounts:
530 account = SavingsAccount(owner=self)
531 else:
532 account = self.accounts[0]
533 account.balance = value
535 @balance.inplace.expression
536 @classmethod
537 def _balance_expression(cls) -> SQLColumnExpression[Optional[Decimal]]:
538 return cast(
539 "SQLColumnExpression[Optional[Decimal]]",
540 SavingsAccount.balance,
541 )
543The above hybrid property ``balance`` works with the first
544``SavingsAccount`` entry in the list of accounts for this user. The
545in-Python getter/setter methods can treat ``accounts`` as a Python
546list available on ``self``.
548.. tip:: The ``User.balance`` getter in the above example accesses the
549 ``self.accounts`` collection, which will normally be loaded via the
550 :func:`.selectinload` loader strategy configured on the ``User.balance``
551 :func:`_orm.relationship`. The default loader strategy when not otherwise
552 stated on :func:`_orm.relationship` is :func:`.lazyload`, which emits SQL on
553 demand. When using asyncio, on-demand loaders such as :func:`.lazyload` are
554 not supported, so care should be taken to ensure the ``self.accounts``
555 collection is accessible to this hybrid accessor when using asyncio.
557At the expression level, it's expected that the ``User`` class will
558be used in an appropriate context such that an appropriate join to
559``SavingsAccount`` will be present:
561.. sourcecode:: pycon+sql
563 >>> from sqlalchemy import select
564 >>> print(
565 ... select(User, User.balance)
566 ... .join(User.accounts)
567 ... .filter(User.balance > 5000)
568 ... )
569 {printsql}SELECT "user".id AS user_id, "user".name AS user_name,
570 account.balance AS account_balance
571 FROM "user" JOIN account ON "user".id = account.user_id
572 WHERE account.balance > :balance_1
574Note however, that while the instance level accessors need to worry
575about whether ``self.accounts`` is even present, this issue expresses
576itself differently at the SQL expression level, where we basically
577would use an outer join:
579.. sourcecode:: pycon+sql
581 >>> from sqlalchemy import select
582 >>> from sqlalchemy import or_
583 >>> print(
584 ... select(User, User.balance)
585 ... .outerjoin(User.accounts)
586 ... .filter(or_(User.balance < 5000, User.balance == None))
587 ... )
588 {printsql}SELECT "user".id AS user_id, "user".name AS user_name,
589 account.balance AS account_balance
590 FROM "user" LEFT OUTER JOIN account ON "user".id = account.user_id
591 WHERE account.balance < :balance_1 OR account.balance IS NULL
593Correlated Subquery Relationship Hybrid
594^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
596We can, of course, forego being dependent on the enclosing query's usage
597of joins in favor of the correlated subquery, which can portably be packed
598into a single column expression. A correlated subquery is more portable, but
599often performs more poorly at the SQL level. Using the same technique
600illustrated at :ref:`mapper_column_property_sql_expressions`,
601we can adjust our ``SavingsAccount`` example to aggregate the balances for
602*all* accounts, and use a correlated subquery for the column expression::
604 from __future__ import annotations
606 from decimal import Decimal
607 from typing import List
609 from sqlalchemy import ForeignKey
610 from sqlalchemy import func
611 from sqlalchemy import Numeric
612 from sqlalchemy import select
613 from sqlalchemy import SQLColumnExpression
614 from sqlalchemy import String
615 from sqlalchemy.ext.hybrid import hybrid_property
616 from sqlalchemy.orm import DeclarativeBase
617 from sqlalchemy.orm import Mapped
618 from sqlalchemy.orm import mapped_column
619 from sqlalchemy.orm import relationship
622 class Base(DeclarativeBase):
623 pass
626 class SavingsAccount(Base):
627 __tablename__ = "account"
628 id: Mapped[int] = mapped_column(primary_key=True)
629 user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
630 balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
632 owner: Mapped[User] = relationship(back_populates="accounts")
635 class User(Base):
636 __tablename__ = "user"
637 id: Mapped[int] = mapped_column(primary_key=True)
638 name: Mapped[str] = mapped_column(String(100))
640 accounts: Mapped[List[SavingsAccount]] = relationship(
641 back_populates="owner", lazy="selectin"
642 )
644 @hybrid_property
645 def balance(self) -> Decimal:
646 return sum(
647 (acc.balance for acc in self.accounts), start=Decimal("0")
648 )
650 @balance.inplace.expression
651 @classmethod
652 def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
653 return (
654 select(func.sum(SavingsAccount.balance))
655 .where(SavingsAccount.user_id == cls.id)
656 .label("total_balance")
657 )
659The above recipe will give us the ``balance`` column which renders
660a correlated SELECT:
662.. sourcecode:: pycon+sql
664 >>> from sqlalchemy import select
665 >>> print(select(User).filter(User.balance > 400))
666 {printsql}SELECT "user".id, "user".name
667 FROM "user"
668 WHERE (
669 SELECT sum(account.balance) AS sum_1 FROM account
670 WHERE account.user_id = "user".id
671 ) > :param_1
674.. _hybrid_custom_comparators:
676Building Custom Comparators
677---------------------------
679The hybrid property also includes a helper that allows construction of
680custom comparators. A comparator object allows one to customize the
681behavior of each SQLAlchemy expression operator individually. They
682are useful when creating custom types that have some highly
683idiosyncratic behavior on the SQL side.
685.. note:: The :meth:`.hybrid_property.comparator` decorator introduced
686 in this section **replaces** the use of the
687 :meth:`.hybrid_property.expression` decorator.
688 They cannot be used together.
690The example class below allows case-insensitive comparisons on the attribute
691named ``word_insensitive``::
693 from __future__ import annotations
695 from typing import Any
697 from sqlalchemy import ColumnElement
698 from sqlalchemy import func
699 from sqlalchemy.ext.hybrid import Comparator
700 from sqlalchemy.ext.hybrid import hybrid_property
701 from sqlalchemy.orm import DeclarativeBase
702 from sqlalchemy.orm import Mapped
703 from sqlalchemy.orm import mapped_column
706 class Base(DeclarativeBase):
707 pass
710 class CaseInsensitiveComparator(Comparator[str]):
711 def __eq__(self, other: Any) -> ColumnElement[bool]: # type: ignore[override] # noqa: E501
712 return func.lower(self.__clause_element__()) == func.lower(other)
715 class SearchWord(Base):
716 __tablename__ = "searchword"
718 id: Mapped[int] = mapped_column(primary_key=True)
719 word: Mapped[str]
721 @hybrid_property
722 def word_insensitive(self) -> str:
723 return self.word.lower()
725 @word_insensitive.inplace.comparator
726 @classmethod
727 def _word_insensitive_comparator(cls) -> CaseInsensitiveComparator:
728 return CaseInsensitiveComparator(cls.word)
730Above, SQL expressions against ``word_insensitive`` will apply the ``LOWER()``
731SQL function to both sides:
733.. sourcecode:: pycon+sql
735 >>> from sqlalchemy import select
736 >>> print(select(SearchWord).filter_by(word_insensitive="Trucks"))
737 {printsql}SELECT searchword.id, searchword.word
738 FROM searchword
739 WHERE lower(searchword.word) = lower(:lower_1)
742The ``CaseInsensitiveComparator`` above implements part of the
743:class:`.ColumnOperators` interface. A "coercion" operation like
744lowercasing can be applied to all comparison operations (i.e. ``eq``,
745``lt``, ``gt``, etc.) using :meth:`.Operators.operate`::
747 class CaseInsensitiveComparator(Comparator):
748 def operate(self, op, other, **kwargs):
749 return op(
750 func.lower(self.__clause_element__()),
751 func.lower(other),
752 **kwargs,
753 )
755.. _hybrid_reuse_subclass:
757Reusing Hybrid Properties across Subclasses
758-------------------------------------------
760A hybrid can be referred to from a superclass, to allow modifying
761methods like :meth:`.hybrid_property.getter`, :meth:`.hybrid_property.setter`
762to be used to redefine those methods on a subclass. This is similar to
763how the standard Python ``@property`` object works::
765 class FirstNameOnly(Base):
766 # ...
768 first_name: Mapped[str]
770 @hybrid_property
771 def name(self) -> str:
772 return self.first_name
774 @name.inplace.setter
775 def _name_setter(self, value: str) -> None:
776 self.first_name = value
779 class FirstNameLastName(FirstNameOnly):
780 # ...
782 last_name: Mapped[str]
784 # 'inplace' is not used here; calling getter creates a copy
785 # of FirstNameOnly.name that is local to FirstNameLastName
786 @FirstNameOnly.name.getter
787 def name(self) -> str:
788 return self.first_name + " " + self.last_name
790 @name.inplace.setter
791 def _name_setter(self, value: str) -> None:
792 self.first_name, self.last_name = value.split(" ", 1)
794Above, the ``FirstNameLastName`` class refers to the hybrid from
795``FirstNameOnly.name`` to repurpose its getter and setter for the subclass.
797When overriding :meth:`.hybrid_property.expression` and
798:meth:`.hybrid_property.comparator` alone as the first reference to the
799superclass, these names conflict with the same-named accessors on the class-
800level :class:`.QueryableAttribute` object returned at the class level. To
801override these methods when referring directly to the parent class descriptor,
802add the special qualifier :attr:`.hybrid_property.overrides`, which will de-
803reference the instrumented attribute back to the hybrid object::
805 class FirstNameLastName(FirstNameOnly):
806 # ...
808 last_name: Mapped[str]
810 @FirstNameOnly.name.overrides.expression
811 @classmethod
812 def name(cls):
813 return func.concat(cls.first_name, " ", cls.last_name)
815.. _hybrid_value_objects:
817Hybrid Value Objects
818--------------------
820In the example shown previously at :ref:`hybrid_custom_comparators`,
821if we were to compare the ``word_insensitive``
822attribute of a ``SearchWord`` instance to a plain Python string, the plain
823Python string would not be coerced to lower case - the
824``CaseInsensitiveComparator`` we built, being returned by
825``@word_insensitive.comparator``, only applies to the SQL side.
827A more comprehensive form of the custom comparator is to construct a **Hybrid
828Value Object**. This technique applies the target value or expression to a value
829object which is then returned by the accessor in all cases. The value object
830allows control of all operations upon the value as well as how compared values
831are treated, both on the SQL expression side as well as the Python value side.
832Replacing the previous ``CaseInsensitiveComparator`` class with a new
833``CaseInsensitiveWord`` class::
835 from sqlalchemy import func
836 from sqlalchemy.ext.hybrid import Comparator
839 class CaseInsensitiveWord(Comparator):
840 "Hybrid value representing a lower case representation of a word."
842 def __init__(self, word):
843 if isinstance(word, str):
844 self.word = word.lower()
845 else:
846 self.word = func.lower(word)
848 def operate(self, op, other, **kwargs):
849 if not isinstance(other, CaseInsensitiveWord):
850 other = CaseInsensitiveWord(other)
851 return op(self.word, other.word, **kwargs)
853 def __clause_element__(self):
854 return self.word
856 def __str__(self):
857 return self.word
859 key = "word"
860 "Label to apply to Query tuple results"
862Above, the ``CaseInsensitiveWord`` object represents ``self.word``, which may
863be a SQL function, or may be a Python native string. The hybrid value object should
864implement ``__clause_element__()``, which allows the object to be coerced into
865a SQL-capable value when used in SQL expression constructs, as well as Python
866comparison methods such as ``__eq__()``, which is accomplished in the above
867example by subclassing :class:`.hybrid.Comparator` and overriding the
868``operate()`` method.
870.. topic:: Building the Value object with dataclasses
872 Hybrid value objects may also be implemented as Python dataclasses. If
873 modification to values upon construction is needed, use the
874 ``__post_init__()`` dataclasses method. Instance variables that work in
875 a "hybrid" fashion may be instance of a plain Python value, or an instance
876 of :class:`.SQLColumnExpression` genericized against that type. Also make sure to disable
877 dataclass comparison features, as the :class:`.hybrid.Comparator` class
878 provides these::
880 from sqlalchemy import func
881 from sqlalchemy.ext.hybrid import Comparator
882 from dataclasses import dataclass
885 @dataclass(eq=False)
886 class CaseInsensitiveWord(Comparator):
887 word: str | SQLColumnExpression[str]
889 def __post_init__(self):
890 if isinstance(self.word, str):
891 self.word = self.word.lower()
892 else:
893 self.word = func.lower(self.word)
895 def operate(self, op, other, **kwargs):
896 if not isinstance(other, CaseInsensitiveWord):
897 other = CaseInsensitiveWord(other)
898 return op(self.word, other.word, **kwargs)
900 def __clause_element__(self):
901 return self.word
903With ``__clause_element__()`` provided, our ``SearchWord`` class
904can now deliver the ``CaseInsensitiveWord`` object unconditionally from a
905single hybrid method, returning an object that behaves appropriately
906in both value-based and SQL contexts::
908 class SearchWord(Base):
909 __tablename__ = "searchword"
910 id: Mapped[int] = mapped_column(primary_key=True)
911 word: Mapped[str]
913 @hybrid_property
914 def word_insensitive(self) -> CaseInsensitiveWord:
915 return CaseInsensitiveWord(self.word)
917The class-level version of ``CaseInsensitiveWord`` will work in SQL
918constructs:
920.. sourcecode:: pycon+sql
922 >>> print(select(SearchWord).filter(SearchWord.word_insensitive == "Trucks"))
923 {printsql}SELECT searchword.id AS searchword_id, searchword.word AS searchword_word
924 FROM searchword
925 WHERE lower(searchword.word) = :lower_1
927By also subclassing :class:`.hybrid.Comparator` and providing an implementation
928for ``operate()``, the ``word_insensitive`` attribute also has case-insensitive
929comparison behavior universally, including SQL expression and Python expression
930(note the Python value is converted to lower case on the Python side here):
932.. sourcecode:: pycon+sql
934 >>> from sqlalchemy.orm import aliased
935 >>> sw1 = aliased(SearchWord)
936 >>> sw2 = aliased(SearchWord)
937 >>> print(
938 ... select(sw1.word_insensitive, sw2.word_insensitive).filter(
939 ... sw1.word_insensitive > sw2.word_insensitive
940 ... )
941 ... )
942 {printsql}SELECT lower(searchword_1.word) AS lower_1,
943 lower(searchword_2.word) AS lower_2
944 FROM searchword AS searchword_1, searchword AS searchword_2
945 WHERE lower(searchword_1.word) > lower(searchword_2.word)
947Python only expression::
949 >>> ws1 = SearchWord(word="SomeWord")
950 >>> ws1.word_insensitive == "sOmEwOrD"
951 True
952 >>> ws1.word_insensitive == "XOmEwOrX"
953 False
954 >>> print(ws1.word_insensitive)
955 someword
957The Hybrid Value pattern is very useful for any kind of value that may have
958multiple representations, such as timestamps, time deltas, units of
959measurement, currencies and encrypted passwords.
961.. seealso::
963 `Hybrids and Value Agnostic Types
964 <https://techspot.zzzeek.org/2011/10/21/hybrids-and-value-agnostic-types/>`_
965 - on the techspot.zzzeek.org blog
967 `Value Agnostic Types, Part II
968 <https://techspot.zzzeek.org/2011/10/29/value-agnostic-types-part-ii/>`_ -
969 on the techspot.zzzeek.org blog
971.. _composite_hybrid_value_objects:
973Composite Hybrid Value Objects
974^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
976The functionality of :ref:`hybrid_value_objects` may also be expanded to
977support "composite" forms; in this pattern, SQLAlchemy hybrids begin to
978approximate most (though not all) the same functionality that is available from
979the ORM natively via the :ref:`mapper_composite` feature. We can imitate the
980example of ``Point`` and ``Vertex`` from that section using hybrids, where
981``Point`` is modified to become a Hybrid Value Object::
983 from dataclasses import dataclass
985 from sqlalchemy import tuple_
986 from sqlalchemy.ext.hybrid import Comparator
987 from sqlalchemy import SQLColumnExpression
990 @dataclass(eq=False)
991 class Point(Comparator):
992 x: int | SQLColumnExpression[int]
993 y: int | SQLColumnExpression[int]
995 def operate(self, op, other, **kwargs):
996 return op(self.x, other.x) & op(self.y, other.y)
998 def __clause_element__(self):
999 return tuple_(self.x, self.y)
1001Above, the ``operate()`` method is where the most "hybrid" behavior takes
1002place, making use of ``op()`` (the Python operator function in use) along
1003with the the bitwise ``&`` operator provides us with the SQL AND operator
1004in a SQL context, and boolean "and" in a Python boolean context.
1006Following from there, the owning ``Vertex`` class now uses hybrids to
1007represent ``start`` and ``end``::
1009 from sqlalchemy.orm import DeclarativeBase, Mapped
1010 from sqlalchemy.orm import mapped_column
1011 from sqlalchemy.ext.hybrid import hybrid_property
1014 class Base(DeclarativeBase):
1015 pass
1018 class Vertex(Base):
1019 __tablename__ = "vertices"
1021 id: Mapped[int] = mapped_column(primary_key=True)
1023 x1: Mapped[int]
1024 y1: Mapped[int]
1025 x2: Mapped[int]
1026 y2: Mapped[int]
1028 @hybrid_property
1029 def start(self) -> Point:
1030 return Point(self.x1, self.y1)
1032 @start.inplace.setter
1033 def _set_start(self, value: Point) -> None:
1034 self.x1 = value.x
1035 self.y1 = value.y
1037 @hybrid_property
1038 def end(self) -> Point:
1039 return Point(self.x2, self.y2)
1041 @end.inplace.setter
1042 def _set_end(self, value: Point) -> None:
1043 self.x2 = value.x
1044 self.y2 = value.y
1046 def __repr__(self) -> str:
1047 return f"Vertex(start={self.start}, end={self.end})"
1049Using the above mapping, we can use expressions at the Python or SQL level
1050using ``Vertex.start`` and ``Vertex.end``::
1052 >>> v1 = Vertex(start=Point(3, 4), end=Point(15, 10))
1053 >>> v1.end == Point(15, 10)
1054 True
1055 >>> stmt = (
1056 ... select(Vertex)
1057 ... .where(Vertex.start == Point(3, 4))
1058 ... .where(Vertex.end < Point(7, 8))
1059 ... )
1060 >>> print(stmt)
1061 SELECT vertices.id, vertices.x1, vertices.y1, vertices.x2, vertices.y2
1062 FROM vertices
1063 WHERE vertices.x1 = :x1_1 AND vertices.y1 = :y1_1 AND vertices.x2 < :x2_1 AND vertices.y2 < :y2_1
1065DML Support for Composite Value Objects
1066~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1068Composite value objects like ``Point`` can also be used with the ORM's
1069DML features. The :meth:`.hybrid_property.update_expression` decorator allows
1070the hybrid to expand a composite value into multiple column assignments
1071in UPDATE and INSERT statements::
1073 class Location(Base):
1074 __tablename__ = "location"
1076 id: Mapped[int] = mapped_column(primary_key=True)
1077 x: Mapped[int]
1078 y: Mapped[int]
1080 @hybrid_property
1081 def coordinates(self) -> Point:
1082 return Point(self.x, self.y)
1084 @coordinates.inplace.update_expression
1085 @classmethod
1086 def _coordinates_update_expression(
1087 cls, value: Any
1088 ) -> List[Tuple[Any, Any]]:
1089 assert isinstance(value, Point)
1090 return [(cls.x, value.x), (cls.y, value.y)]
1092This allows UPDATE statements to work with the composite value:
1094.. sourcecode:: pycon+sql
1096 >>> from sqlalchemy import update
1097 >>> print(
1098 ... update(Location)
1099 ... .where(Location.id == 5)
1100 ... .values({Location.coordinates: Point(25, 17)})
1101 ... )
1102 {printsql}UPDATE location SET x=:x, y=:y WHERE location.id = :id_1
1104For bulk operations that use parameter dictionaries, the
1105:meth:`.hybrid_property.bulk_dml` decorator provides a hook to
1106convert composite values into individual column values::
1108 from typing import MutableMapping
1111 class Location(Base):
1112 # ... (same as above)
1114 @coordinates.inplace.bulk_dml
1115 @classmethod
1116 def _coordinates_bulk_dml(
1117 cls, mapping: MutableMapping[str, Any], value: Point
1118 ) -> None:
1119 mapping["x"] = value.x
1120 mapping["y"] = value.y
1122This enables bulk operations with composite values::
1124 # Bulk INSERT
1125 session.execute(
1126 insert(Location),
1127 [
1128 {"id": 1, "coordinates": Point(10, 20)},
1129 {"id": 2, "coordinates": Point(30, 40)},
1130 ],
1131 )
1133 # Bulk UPDATE
1134 session.execute(
1135 update(Location),
1136 [
1137 {"id": 1, "coordinates": Point(15, 25)},
1138 {"id": 2, "coordinates": Point(35, 45)},
1139 ],
1140 )
1142""" # noqa
1144from __future__ import annotations
1146from typing import Any
1147from typing import Callable
1148from typing import cast
1149from typing import Concatenate
1150from typing import Generic
1151from typing import List
1152from typing import Literal
1153from typing import MutableMapping
1154from typing import Optional
1155from typing import overload
1156from typing import ParamSpec
1157from typing import Protocol
1158from typing import Sequence
1159from typing import Tuple
1160from typing import Type
1161from typing import TYPE_CHECKING
1162from typing import TypeVar
1163from typing import Union
1165from .. import exc
1166from .. import util
1167from ..orm import attributes
1168from ..orm import InspectionAttrExtensionType
1169from ..orm import interfaces
1170from ..orm import ORMDescriptor
1171from ..orm.attributes import QueryableAttribute
1172from ..sql import roles
1173from ..sql._typing import is_has_clause_element
1174from ..sql.elements import ColumnElement
1175from ..sql.elements import SQLCoreOperations
1176from ..util.typing import Self
1178if TYPE_CHECKING:
1179 from ..orm.interfaces import MapperProperty
1180 from ..orm.util import AliasedInsp
1181 from ..sql import SQLColumnExpression
1182 from ..sql._typing import _ColumnExpressionArgument
1183 from ..sql._typing import _DMLColumnArgument
1184 from ..sql._typing import _HasClauseElement
1185 from ..sql._typing import _InfoType
1186 from ..sql.operators import OperatorType
1188_P = ParamSpec("_P")
1189_R = TypeVar("_R")
1190_T = TypeVar("_T", bound=Any)
1191_TE = TypeVar("_TE", bound=Any)
1192_T_co = TypeVar("_T_co", bound=Any, covariant=True)
1193_T_con = TypeVar("_T_con", bound=Any, contravariant=True)
1196class HybridExtensionType(InspectionAttrExtensionType):
1197 HYBRID_METHOD = "HYBRID_METHOD"
1198 """Symbol indicating an :class:`InspectionAttr` that's
1199 of type :class:`.hybrid_method`.
1201 Is assigned to the :attr:`.InspectionAttr.extension_type`
1202 attribute.
1204 .. seealso::
1206 :attr:`_orm.Mapper.all_orm_attributes`
1208 """
1210 HYBRID_PROPERTY = "HYBRID_PROPERTY"
1211 """Symbol indicating an :class:`InspectionAttr` that's
1212 of type :class:`.hybrid_method`.
1214 Is assigned to the :attr:`.InspectionAttr.extension_type`
1215 attribute.
1217 .. seealso::
1219 :attr:`_orm.Mapper.all_orm_attributes`
1221 """
1224class _HybridGetterType(Protocol[_T_co]):
1225 def __call__(s, self: Any, /) -> _T_co: ...
1228class _HybridSetterType(Protocol[_T_con]):
1229 def __call__(s, self: Any, value: _T_con, /) -> None: ...
1232class _HybridUpdaterType(Protocol[_T_con]):
1233 def __call__(
1234 s,
1235 cls: Any,
1236 value: Union[_T_con, _ColumnExpressionArgument[_T_con]],
1237 ) -> List[Tuple[_DMLColumnArgument, Any]]: ...
1240class _HybridBulkDMLType(Protocol[_T_co]):
1241 def __call__(
1242 s,
1243 cls: Any,
1244 mapping: MutableMapping[str, Any],
1245 value: Any,
1246 ) -> Any: ...
1249class _HybridDeleterType(Protocol[_T_co]):
1250 def __call__(s, self: Any, /) -> None: ...
1253class _HybridExprCallableType(Protocol[_T_co]):
1254 def __call__(
1255 s, cls: Any, /
1256 ) -> Union[_HasClauseElement[_T_co], SQLColumnExpression[_T_co]]: ...
1259class _HybridComparatorCallableType(Protocol[_T]):
1260 def __call__(self, cls: Any) -> Comparator[_T]: ...
1263class _HybridClassLevelAccessor(QueryableAttribute[_T]):
1264 """Describe the object returned by a hybrid_property() when
1265 called as a class-level descriptor.
1267 """
1269 if TYPE_CHECKING:
1271 def getter(
1272 self, fget: _HybridGetterType[_T]
1273 ) -> hybrid_property[_T]: ...
1275 def setter(
1276 self, fset: _HybridSetterType[_T]
1277 ) -> hybrid_property[_T]: ...
1279 def deleter(
1280 self, fdel: _HybridDeleterType[_T]
1281 ) -> hybrid_property[_T]: ...
1283 @property
1284 def overrides(self) -> hybrid_property[_T]: ...
1286 def update_expression(
1287 self, meth: _HybridUpdaterType[_T]
1288 ) -> hybrid_property[_T]: ...
1290 def bulk_dml(
1291 self, meth: _HybridBulkDMLType[_T]
1292 ) -> hybrid_property[_T]: ...
1295class hybrid_method(interfaces.InspectionAttrInfo, Generic[_P, _R]):
1296 """A decorator which allows definition of a Python object method with both
1297 instance-level and class-level behavior.
1299 """
1301 is_attribute = True
1302 extension_type = HybridExtensionType.HYBRID_METHOD
1304 def __init__(
1305 self,
1306 func: Callable[Concatenate[Any, _P], _R],
1307 expr: Optional[
1308 Callable[Concatenate[Any, _P], SQLCoreOperations[_R]]
1309 ] = None,
1310 ):
1311 """Create a new :class:`.hybrid_method`.
1313 Usage is typically via decorator::
1315 from sqlalchemy.ext.hybrid import hybrid_method
1318 class SomeClass:
1319 @hybrid_method
1320 def value(self, x, y):
1321 return self._value + x + y
1323 @value.expression
1324 @classmethod
1325 def value(cls, x, y):
1326 return func.some_function(cls._value, x, y)
1328 """
1329 self.func = func
1330 if expr is not None:
1331 self.expression(expr)
1332 else:
1333 self.expression(func) # type: ignore[arg-type]
1335 @property
1336 def inplace(self) -> Self:
1337 """Return the inplace mutator for this :class:`.hybrid_method`.
1339 The :class:`.hybrid_method` class already performs "in place" mutation
1340 when the :meth:`.hybrid_method.expression` decorator is called,
1341 so this attribute returns Self.
1343 .. versionadded:: 2.0.4
1345 .. seealso::
1347 :ref:`hybrid_pep484_naming`
1349 """
1350 return self
1352 @overload
1353 def __get__(
1354 self, instance: Literal[None], owner: Type[object]
1355 ) -> Callable[_P, SQLCoreOperations[_R]]: ...
1357 @overload
1358 def __get__(
1359 self, instance: object, owner: Type[object]
1360 ) -> Callable[_P, _R]: ...
1362 def __get__(
1363 self, instance: Optional[object], owner: Type[object]
1364 ) -> Union[Callable[_P, _R], Callable[_P, SQLCoreOperations[_R]]]:
1365 if instance is None:
1366 return self.expr.__get__(owner, owner) # type: ignore[no-any-return] # noqa: E501
1367 else:
1368 return self.func.__get__(instance, owner) # type: ignore[no-any-return] # noqa: E501
1370 def expression(
1371 self, expr: Callable[Concatenate[Any, _P], SQLCoreOperations[_R]]
1372 ) -> hybrid_method[_P, _R]:
1373 """Provide a modifying decorator that defines a
1374 SQL-expression producing method."""
1376 self.expr = expr
1377 if not self.expr.__doc__:
1378 self.expr.__doc__ = self.func.__doc__
1379 return self
1382def _unwrap_classmethod(meth: _T) -> _T:
1383 if isinstance(meth, classmethod):
1384 return meth.__func__ # type: ignore[return-value]
1385 else:
1386 return meth
1389class hybrid_property(interfaces.InspectionAttrInfo, ORMDescriptor[_T]):
1390 """A decorator which allows definition of a Python descriptor with both
1391 instance-level and class-level behavior.
1393 """
1395 is_attribute = True
1396 extension_type = HybridExtensionType.HYBRID_PROPERTY
1398 __name__: str
1400 def __init__(
1401 self,
1402 fget: _HybridGetterType[_T],
1403 fset: Optional[_HybridSetterType[_T]] = None,
1404 fdel: Optional[_HybridDeleterType[_T]] = None,
1405 expr: Optional[_HybridExprCallableType[_T]] = None,
1406 custom_comparator: Optional[Comparator[_T]] = None,
1407 update_expr: Optional[_HybridUpdaterType[_T]] = None,
1408 bulk_dml_setter: Optional[_HybridBulkDMLType[_T]] = None,
1409 ):
1410 """Create a new :class:`.hybrid_property`.
1412 Usage is typically via decorator::
1414 from sqlalchemy.ext.hybrid import hybrid_property
1417 class SomeClass:
1418 @hybrid_property
1419 def value(self):
1420 return self._value
1422 @value.setter
1423 def value(self, value):
1424 self._value = value
1426 """
1427 self.fget = fget
1428 self.fset = fset
1429 self.fdel = fdel
1430 self.expr = _unwrap_classmethod(expr)
1431 self.custom_comparator = _unwrap_classmethod(custom_comparator)
1432 self.update_expr = _unwrap_classmethod(update_expr)
1433 self.bulk_dml_setter = _unwrap_classmethod(bulk_dml_setter)
1434 util.update_wrapper(self, fget) # type: ignore[arg-type]
1436 @overload
1437 def __get__(self, instance: Any, owner: Literal[None]) -> Self: ...
1439 @overload
1440 def __get__(
1441 self, instance: Literal[None], owner: Type[object]
1442 ) -> _HybridClassLevelAccessor[_T]: ...
1444 @overload
1445 def __get__(self, instance: object, owner: Type[object]) -> _T: ...
1447 def __get__(
1448 self, instance: Optional[object], owner: Optional[Type[object]]
1449 ) -> Union[hybrid_property[_T], _HybridClassLevelAccessor[_T], _T]:
1450 if owner is None:
1451 return self
1452 elif instance is None:
1453 return self._expr_comparator(owner)
1454 else:
1455 return self.fget(instance)
1457 def __set__(
1458 self, instance: object, value: Union[SQLCoreOperations[_T], _T]
1459 ) -> None:
1460 if self.fset is None:
1461 raise AttributeError("can't set attribute")
1462 self.fset(instance, value) # type: ignore[arg-type]
1464 def __delete__(self, instance: object) -> None:
1465 if self.fdel is None:
1466 raise AttributeError("can't delete attribute")
1467 self.fdel(instance)
1469 def _copy(self, **kw: Any) -> hybrid_property[_T]:
1470 defaults = {
1471 key: value
1472 for key, value in self.__dict__.items()
1473 if not key.startswith("_")
1474 }
1475 defaults.update(**kw)
1476 return type(self)(**defaults)
1478 @property
1479 def overrides(self) -> Self:
1480 """Prefix for a method that is overriding an existing attribute.
1482 The :attr:`.hybrid_property.overrides` accessor just returns
1483 this hybrid object, which when called at the class level from
1484 a parent class, will de-reference the "instrumented attribute"
1485 normally returned at this level, and allow modifying decorators
1486 like :meth:`.hybrid_property.expression` and
1487 :meth:`.hybrid_property.comparator`
1488 to be used without conflicting with the same-named attributes
1489 normally present on the :class:`.QueryableAttribute`::
1491 class SuperClass:
1492 # ...
1494 @hybrid_property
1495 def foobar(self):
1496 return self._foobar
1499 class SubClass(SuperClass):
1500 # ...
1502 @SuperClass.foobar.overrides.expression
1503 def foobar(cls):
1504 return func.subfoobar(self._foobar)
1506 .. seealso::
1508 :ref:`hybrid_reuse_subclass`
1510 """
1511 return self
1513 class _InPlace(Generic[_TE]):
1514 """A builder helper for .hybrid_property.
1516 .. versionadded:: 2.0.4
1518 """
1520 __slots__ = ("attr",)
1522 def __init__(self, attr: hybrid_property[_TE]):
1523 self.attr = attr
1525 def _set(self, **kw: Any) -> hybrid_property[_TE]:
1526 for k, v in kw.items():
1527 setattr(self.attr, k, _unwrap_classmethod(v))
1528 return self.attr
1530 def getter(self, fget: _HybridGetterType[_TE]) -> hybrid_property[_TE]:
1531 return self._set(fget=fget)
1533 def setter(self, fset: _HybridSetterType[_TE]) -> hybrid_property[_TE]:
1534 return self._set(fset=fset)
1536 def deleter(
1537 self, fdel: _HybridDeleterType[_TE]
1538 ) -> hybrid_property[_TE]:
1539 return self._set(fdel=fdel)
1541 def expression(
1542 self, expr: _HybridExprCallableType[_TE]
1543 ) -> hybrid_property[_TE]:
1544 return self._set(expr=expr)
1546 def comparator(
1547 self, comparator: _HybridComparatorCallableType[_TE]
1548 ) -> hybrid_property[_TE]:
1549 return self._set(custom_comparator=comparator)
1551 def update_expression(
1552 self, meth: _HybridUpdaterType[_TE]
1553 ) -> hybrid_property[_TE]:
1554 return self._set(update_expr=meth)
1556 def bulk_dml(
1557 self, meth: _HybridBulkDMLType[_TE]
1558 ) -> hybrid_property[_TE]:
1559 return self._set(bulk_dml_setter=meth)
1561 @property
1562 def inplace(self) -> _InPlace[_T]:
1563 """Return the inplace mutator for this :class:`.hybrid_property`.
1565 This is to allow in-place mutation of the hybrid, allowing the first
1566 hybrid method of a certain name to be reused in order to add
1567 more methods without having to name those methods the same, e.g.::
1569 class Interval(Base):
1570 # ...
1572 @hybrid_property
1573 def radius(self) -> float:
1574 return abs(self.length) / 2
1576 @radius.inplace.setter
1577 def _radius_setter(self, value: float) -> None:
1578 self.length = value * 2
1580 @radius.inplace.expression
1581 def _radius_expression(cls) -> ColumnElement[float]:
1582 return type_coerce(func.abs(cls.length) / 2, Float)
1584 .. versionadded:: 2.0.4
1586 .. seealso::
1588 :ref:`hybrid_pep484_naming`
1590 """
1591 return hybrid_property._InPlace(self)
1593 def getter(self, fget: _HybridGetterType[_T]) -> hybrid_property[_T]:
1594 """Provide a modifying decorator that defines a getter method."""
1596 return self._copy(fget=fget)
1598 def setter(self, fset: _HybridSetterType[_T]) -> hybrid_property[_T]:
1599 """Provide a modifying decorator that defines a setter method."""
1601 return self._copy(fset=fset)
1603 def deleter(self, fdel: _HybridDeleterType[_T]) -> hybrid_property[_T]:
1604 """Provide a modifying decorator that defines a deletion method."""
1606 return self._copy(fdel=fdel)
1608 def expression(
1609 self, expr: _HybridExprCallableType[_T]
1610 ) -> hybrid_property[_T]:
1611 """Provide a modifying decorator that defines a SQL-expression
1612 producing method.
1614 When a hybrid is invoked at the class level, the SQL expression given
1615 here is wrapped inside of a specialized :class:`.QueryableAttribute`,
1616 which is the same kind of object used by the ORM to represent other
1617 mapped attributes. The reason for this is so that other class-level
1618 attributes such as docstrings and a reference to the hybrid itself may
1619 be maintained within the structure that's returned, without any
1620 modifications to the original SQL expression passed in.
1622 .. note::
1624 When referring to a hybrid property from an owning class (e.g.
1625 ``SomeClass.some_hybrid``), an instance of
1626 :class:`.QueryableAttribute` is returned, representing the
1627 expression or comparator object as well as this hybrid object.
1628 However, that object itself has accessors called ``expression`` and
1629 ``comparator``; so when attempting to override these decorators on a
1630 subclass, it may be necessary to qualify it using the
1631 :attr:`.hybrid_property.overrides` modifier first. See that
1632 modifier for details.
1634 .. seealso::
1636 :ref:`hybrid_distinct_expression`
1638 """
1640 return self._copy(expr=expr)
1642 def comparator(
1643 self, comparator: _HybridComparatorCallableType[_T]
1644 ) -> hybrid_property[_T]:
1645 """Provide a modifying decorator that defines a custom
1646 comparator producing method.
1648 The return value of the decorated method should be an instance of
1649 :class:`~.hybrid.Comparator`.
1651 .. note:: The :meth:`.hybrid_property.comparator` decorator
1652 **replaces** the use of the :meth:`.hybrid_property.expression`
1653 decorator. They cannot be used together.
1655 When a hybrid is invoked at the class level, the
1656 :class:`~.hybrid.Comparator` object given here is wrapped inside of a
1657 specialized :class:`.QueryableAttribute`, which is the same kind of
1658 object used by the ORM to represent other mapped attributes. The
1659 reason for this is so that other class-level attributes such as
1660 docstrings and a reference to the hybrid itself may be maintained
1661 within the structure that's returned, without any modifications to the
1662 original comparator object passed in.
1664 .. note::
1666 When referring to a hybrid property from an owning class (e.g.
1667 ``SomeClass.some_hybrid``), an instance of
1668 :class:`.QueryableAttribute` is returned, representing the
1669 expression or comparator object as this hybrid object. However,
1670 that object itself has accessors called ``expression`` and
1671 ``comparator``; so when attempting to override these decorators on a
1672 subclass, it may be necessary to qualify it using the
1673 :attr:`.hybrid_property.overrides` modifier first. See that
1674 modifier for details.
1676 """
1677 return self._copy(custom_comparator=comparator)
1679 def update_expression(
1680 self, meth: _HybridUpdaterType[_T]
1681 ) -> hybrid_property[_T]:
1682 """Provide a modifying decorator that defines an UPDATE tuple
1683 producing method.
1685 The method accepts a single value, which is the value to be
1686 rendered into the SET clause of an UPDATE statement. The method
1687 should then process this value into individual column expressions
1688 that fit into the ultimate SET clause, and return them as a
1689 sequence of 2-tuples. Each tuple
1690 contains a column expression as the key and a value to be rendered.
1692 E.g.::
1694 class Person(Base):
1695 # ...
1697 first_name = Column(String)
1698 last_name = Column(String)
1700 @hybrid_property
1701 def fullname(self):
1702 return first_name + " " + last_name
1704 @fullname.update_expression
1705 def fullname(cls, value):
1706 fname, lname = value.split(" ", 1)
1707 return [(cls.first_name, fname), (cls.last_name, lname)]
1709 """
1710 return self._copy(update_expr=meth)
1712 def bulk_dml(self, meth: _HybridBulkDMLType[_T]) -> hybrid_property[_T]:
1713 """Define a setter for bulk dml.
1715 .. versionadded:: 2.1
1717 """
1718 return self._copy(bulk_dml=meth)
1720 @util.memoized_property
1721 def _expr_comparator(
1722 self,
1723 ) -> Callable[[Any], _HybridClassLevelAccessor[_T]]:
1724 if self.custom_comparator is not None:
1725 return self._get_comparator(self.custom_comparator)
1726 elif self.expr is not None:
1727 return self._get_expr(self.expr)
1728 else:
1729 return self._get_expr(cast(_HybridExprCallableType[_T], self.fget))
1731 def _get_expr(
1732 self, expr: _HybridExprCallableType[_T]
1733 ) -> Callable[[Any], _HybridClassLevelAccessor[_T]]:
1734 def _expr(cls: Any) -> ExprComparator[_T]:
1735 return ExprComparator(cls, expr(cls), self)
1737 util.update_wrapper(_expr, expr)
1739 return self._get_comparator(_expr)
1741 def _get_comparator(
1742 self, comparator: Any
1743 ) -> Callable[[Any], _HybridClassLevelAccessor[_T]]:
1744 proxy_attr = attributes._create_proxied_attribute(self)
1746 def expr_comparator(
1747 owner: Type[object],
1748 ) -> _HybridClassLevelAccessor[_T]:
1749 # because this is the descriptor protocol, we don't really know
1750 # what our attribute name is. so search for it through the
1751 # MRO.
1752 for lookup in owner.__mro__:
1753 if self.__name__ in lookup.__dict__:
1754 if lookup.__dict__[self.__name__] is self:
1755 name = self.__name__
1756 break
1757 else:
1758 name = attributes._UNKNOWN_ATTR_KEY # type: ignore[assignment,unused-ignore] # noqa: E501
1760 return cast(
1761 "_HybridClassLevelAccessor[_T]",
1762 proxy_attr(
1763 owner,
1764 name,
1765 self,
1766 comparator(owner),
1767 doc=comparator.__doc__ or self.__doc__,
1768 ),
1769 )
1771 return expr_comparator
1774class Comparator(interfaces.PropComparator[_T]):
1775 """A helper class that allows easy construction of custom
1776 :class:`~.orm.interfaces.PropComparator`
1777 classes for usage with hybrids."""
1779 def __init__(
1780 self, expression: Union[_HasClauseElement[_T], SQLColumnExpression[_T]]
1781 ):
1782 self.expression = expression
1784 def __clause_element__(self) -> roles.ColumnsClauseRole:
1785 expr = self.expression
1786 if is_has_clause_element(expr):
1787 ret_expr = expr.__clause_element__()
1788 else:
1789 if TYPE_CHECKING:
1790 assert isinstance(expr, ColumnElement)
1791 ret_expr = expr
1793 if TYPE_CHECKING:
1794 # see test_hybrid->test_expression_isnt_clause_element
1795 # that exercises the usual place this is caught if not
1796 # true
1797 assert isinstance(ret_expr, ColumnElement)
1798 return ret_expr
1800 @util.non_memoized_property
1801 def property(self) -> interfaces.MapperProperty[_T]:
1802 raise NotImplementedError()
1804 def adapt_to_entity(
1805 self, adapt_to_entity: AliasedInsp[Any]
1806 ) -> Comparator[_T]:
1807 # interesting....
1808 return self
1811class ExprComparator(Comparator[_T]):
1812 def __init__(
1813 self,
1814 cls: Type[Any],
1815 expression: Union[_HasClauseElement[_T], SQLColumnExpression[_T]],
1816 hybrid: hybrid_property[_T],
1817 ):
1818 self.cls = cls
1819 self.expression = expression
1820 self.hybrid = hybrid
1822 def __getattr__(self, key: str) -> Any:
1823 return getattr(self.expression, key)
1825 @util.ro_non_memoized_property
1826 def info(self) -> _InfoType:
1827 return self.hybrid.info
1829 def _bulk_update_tuples(
1830 self,
1831 value: Any,
1832 ) -> Sequence[Tuple[_DMLColumnArgument, Any]]:
1833 if isinstance(self.expression, attributes.QueryableAttribute):
1834 return self.expression._bulk_update_tuples(value)
1835 elif self.hybrid.update_expr is not None:
1836 return self.hybrid.update_expr(self.cls, value)
1837 else:
1838 return [(self.expression, value)]
1840 def _bulk_dml_setter(self, key: str) -> Optional[Callable[..., Any]]:
1841 """return a callable that will process a bulk INSERT value"""
1843 meth = None
1845 def prop(mapping: MutableMapping[str, Any]) -> None:
1846 nonlocal meth
1847 value = mapping[key]
1849 if meth is None:
1850 if self.hybrid.bulk_dml_setter is None:
1851 raise exc.InvalidRequestError(
1852 "Can't evaluate bulk DML statement; please "
1853 "supply a bulk_dml decorated function"
1854 )
1856 meth = self.hybrid.bulk_dml_setter
1858 meth(self.cls, mapping, value)
1860 return prop
1862 @util.non_memoized_property
1863 def property(self) -> MapperProperty[_T]:
1864 # this accessor is not normally used, however is accessed by things
1865 # like ORM synonyms if the hybrid is used in this context; the
1866 # .property attribute is not necessarily accessible
1867 return self.expression.property # type: ignore[no-any-return, union-attr] # noqa: E501
1869 def operate(
1870 self, op: OperatorType, *other: Any, **kwargs: Any
1871 ) -> ColumnElement[Any]:
1872 return op(self.expression, *other, **kwargs)
1874 def reverse_operate(
1875 self, op: OperatorType, other: Any, **kwargs: Any
1876 ) -> ColumnElement[Any]:
1877 return op(other, self.expression, **kwargs) # type: ignore[no-any-return] # noqa: E501