codekingpro/portable-devtools
114k
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
7
8r"""Define attributes on ORM-mapped classes that have "hybrid" behavior.
9
10"hybrid" means the attribute has distinct behaviors defined at the
11class level and at the instance level.
12
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.
17
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::
24
25 from __future__ import annotations
26
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
32
33
34 class Base(DeclarativeBase):
35 pass
36
37
38 class Interval(Base):
39 __tablename__ = "interval"
40
41 id: Mapped[int] = mapped_column(primary_key=True)
42 start: Mapped[int]
43 end: Mapped[int]
44
45 def __init__(self, start: int, end: int):
46 self.start = start
47 self.end = end
48
49 @hybrid_property
50 def length(self) -> int:
51 return self.end - self.start
52
53 @hybrid_method
54 def contains(self, point: int) -> bool:
55 return (self.start <= point) & (point <= self.end)
56
57 @hybrid_method
58 def intersects(self, other: Interval) -> bool:
59 return self.contains(other.start) | self.contains(other.end)
60
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::
65
66 >>> i1 = Interval(5, 10)
67 >>> i1.length
68 5
69
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:
74
75.. sourcecode:: pycon+sql
76
77 >>> from sqlalchemy import select
78 >>> print(select(Interval.length))
79 {printsql}SELECT interval."end" - interval.start AS length
80 FROM interval{stop}
81
82
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
87
88Filtering methods such as :meth:`.Select.filter_by` are supported
89with hybrid attributes as well:
90
91.. sourcecode:: pycon+sql
92
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
97
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:
105
106.. sourcecode:: pycon+sql
107
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
116
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}
121
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}
132
133.. _hybrid_distinct_expression:
134
135Defining Expression Behavior Distinct from Attribute Behavior
136--------------------------------------------------------------
137
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::
147
148 from sqlalchemy import ColumnElement
149 from sqlalchemy import Float
150 from sqlalchemy import func
151 from sqlalchemy import type_coerce
152
153
154 class Interval(Base):
155 # ...
156
157 @hybrid_property
158 def radius(self) -> float:
159 return abs(self.length) / 2
160
161 @radius.inplace.expression
162 @classmethod
163 def _radius_expression(cls) -> ColumnElement[float]:
164 return type_coerce(func.abs(cls.length) / 2, Float)
165
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``.
179
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.
183
184With ``Interval.radius`` now including an expression element, the SQL
185function ``ABS()`` is returned when accessing ``Interval.radius``
186at the class level:
187
188.. sourcecode:: pycon+sql
189
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
195
196
197.. _hybrid_pep484_naming:
198
199Using ``inplace`` to create pep-484 compliant hybrid properties
200---------------------------------------------------------------
201
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`.
208
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::
215
216 # correct use, however is not accepted by pep-484 tooling
217
218
219 class Interval(Base):
220 # ...
221
222 @hybrid_property
223 def radius(self):
224 return abs(self.length) / 2
225
226 @radius.setter
227 def radius(self, value):
228 self.length = value * 2
229
230 @radius.expression
231 def radius(cls):
232 return type_coerce(func.abs(cls.length) / 2, Float)
233
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).
246
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.
253
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::
258
259 # correct use which is also accepted by pep-484 tooling
260
261
262 class Interval(Base):
263 # ...
264
265 @hybrid_property
266 def radius(self) -> float:
267 return abs(self.length) / 2
268
269 @radius.inplace.setter
270 def _radius_setter(self, value: float) -> None:
271 # for example only
272 self.length = value * 2
273
274 @radius.inplace.expression
275 @classmethod
276 def _radius_expression(cls) -> ColumnElement[float]:
277 return type_coerce(func.abs(cls.length) / 2, Float)
278
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.
284
285
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.
293
294
295Defining Setters
296----------------
297
298The :meth:`.hybrid_property.setter` modifier allows the construction of a
299custom setter method, that can modify values on the object::
300
301 class Interval(Base):
302 # ...
303
304 @hybrid_property
305 def length(self) -> int:
306 return self.end - self.start
307
308 @length.inplace.setter
309 def _length_setter(self, value: int) -> None:
310 self.end = self.start + value
311
312The ``length(self, value)`` method is now called upon set::
313
314 >>> i1 = Interval(5, 10)
315 >>> i1.length
316 5
317 >>> i1.length = 12
318 >>> i1.end
319 17
320
321.. _hybrid_bulk_update:
322
323Allowing Bulk ORM Update
324------------------------
325
326A hybrid can define a custom "UPDATE" handler for when using
327ORM-enabled updates, allowing the hybrid to be used in the
328SET clause of the update.
329
330Normally, when using a hybrid with :func:`_sql.update`, the SQL
331expression is used as the column that's the target of the SET. If our
332``Interval`` class had a hybrid ``start_point`` that linked to
333``Interval.start``, this could be substituted directly::
334
335 from sqlalchemy import update
336
337 stmt = update(Interval).values({Interval.start_point: 10})
338
339However, when using a composite hybrid like ``Interval.length``, this
340hybrid represents more than one column. We can set up a handler that will
341accommodate a value passed in the VALUES expression which can affect
342this, using the :meth:`.hybrid_property.update_expression` decorator.
343A handler that works similarly to our setter would be::
344
345 from typing import List, Tuple, Any
346
347
348 class Interval(Base):
349 # ...
350
351 @hybrid_property
352 def length(self) -> int:
353 return self.end - self.start
354
355 @length.inplace.setter
356 def _length_setter(self, value: int) -> None:
357 self.end = self.start + value
358
359 @length.inplace.update_expression
360 def _length_update_expression(
361 cls, value: Any
362 ) -> List[Tuple[Any, Any]]:
363 return [(cls.end, cls.start + value)]
364
365Above, if we use ``Interval.length`` in an UPDATE expression, we get
366a hybrid SET expression:
367
368.. sourcecode:: pycon+sql
369
370
371 >>> from sqlalchemy import update
372 >>> print(update(Interval).values({Interval.length: 25}))
373 {printsql}UPDATE interval SET "end"=(interval.start + :start_1)
374
375This SET expression is accommodated by the ORM automatically.
376
377.. seealso::
378
379 :ref:`orm_expression_update_delete` - includes background on ORM-enabled
380 UPDATE statements
381
382
383Working with Relationships
384--------------------------
385
386There's no essential difference when creating hybrids that work with
387related objects as opposed to column-based data. The need for distinct
388expressions tends to be greater. The two variants we'll illustrate
389are the "join-dependent" hybrid, and the "correlated subquery" hybrid.
390
391Join-Dependent Relationship Hybrid
392^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
393
394Consider the following declarative
395mapping which relates a ``User`` to a ``SavingsAccount``::
396
397 from __future__ import annotations
398
399 from decimal import Decimal
400 from typing import cast
401 from typing import List
402 from typing import Optional
403
404 from sqlalchemy import ForeignKey
405 from sqlalchemy import Numeric
406 from sqlalchemy import String
407 from sqlalchemy import SQLColumnExpression
408 from sqlalchemy.ext.hybrid import hybrid_property
409 from sqlalchemy.orm import DeclarativeBase
410 from sqlalchemy.orm import Mapped
411 from sqlalchemy.orm import mapped_column
412 from sqlalchemy.orm import relationship
413
414
415 class Base(DeclarativeBase):
416 pass
417
418
419 class SavingsAccount(Base):
420 __tablename__ = "account"
421 id: Mapped[int] = mapped_column(primary_key=True)
422 user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
423 balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
424
425 owner: Mapped[User] = relationship(back_populates="accounts")
426
427
428 class User(Base):
429 __tablename__ = "user"
430 id: Mapped[int] = mapped_column(primary_key=True)
431 name: Mapped[str] = mapped_column(String(100))
432
433 accounts: Mapped[List[SavingsAccount]] = relationship(
434 back_populates="owner", lazy="selectin"
435 )
436
437 @hybrid_property
438 def balance(self) -> Optional[Decimal]:
439 if self.accounts:
440 return self.accounts[0].balance
441 else:
442 return None
443
444 @balance.inplace.setter
445 def _balance_setter(self, value: Optional[Decimal]) -> None:
446 assert value is not None
447
448 if not self.accounts:
449 account = SavingsAccount(owner=self)
450 else:
451 account = self.accounts[0]
452 account.balance = value
453
454 @balance.inplace.expression
455 @classmethod
456 def _balance_expression(cls) -> SQLColumnExpression[Optional[Decimal]]:
457 return cast(
458 "SQLColumnExpression[Optional[Decimal]]",
459 SavingsAccount.balance,
460 )
461
462The above hybrid property ``balance`` works with the first
463``SavingsAccount`` entry in the list of accounts for this user. The
464in-Python getter/setter methods can treat ``accounts`` as a Python
465list available on ``self``.
466
467.. tip:: The ``User.balance`` getter in the above example accesses the
468 ``self.accounts`` collection, which will normally be loaded via the
469 :func:`.selectinload` loader strategy configured on the ``User.balance``
470 :func:`_orm.relationship`. The default loader strategy when not otherwise
471 stated on :func:`_orm.relationship` is :func:`.lazyload`, which emits SQL on
472 demand. When using asyncio, on-demand loaders such as :func:`.lazyload` are
473 not supported, so care should be taken to ensure the ``self.accounts``
474 collection is accessible to this hybrid accessor when using asyncio.
475
476At the expression level, it's expected that the ``User`` class will
477be used in an appropriate context such that an appropriate join to
478``SavingsAccount`` will be present:
479
480.. sourcecode:: pycon+sql
481
482 >>> from sqlalchemy import select
483 >>> print(
484 ... select(User, User.balance)
485 ... .join(User.accounts)
486 ... .filter(User.balance > 5000)
487 ... )
488 {printsql}SELECT "user".id AS user_id, "user".name AS user_name,
489 account.balance AS account_balance
490 FROM "user" JOIN account ON "user".id = account.user_id
491 WHERE account.balance > :balance_1
492
493Note however, that while the instance level accessors need to worry
494about whether ``self.accounts`` is even present, this issue expresses
495itself differently at the SQL expression level, where we basically
496would use an outer join:
497
498.. sourcecode:: pycon+sql
499
500 >>> from sqlalchemy import select
501 >>> from sqlalchemy import or_
502 >>> print(
503 ... select(User, User.balance)
504 ... .outerjoin(User.accounts)
505 ... .filter(or_(User.balance < 5000, User.balance == None))
506 ... )
507 {printsql}SELECT "user".id AS user_id, "user".name AS user_name,
508 account.balance AS account_balance
509 FROM "user" LEFT OUTER JOIN account ON "user".id = account.user_id
510 WHERE account.balance < :balance_1 OR account.balance IS NULL
511
512Correlated Subquery Relationship Hybrid
513^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
514
515We can, of course, forego being dependent on the enclosing query's usage
516of joins in favor of the correlated subquery, which can portably be packed
517into a single column expression. A correlated subquery is more portable, but
518often performs more poorly at the SQL level. Using the same technique
519illustrated at :ref:`mapper_column_property_sql_expressions`,
520we can adjust our ``SavingsAccount`` example to aggregate the balances for
521*all* accounts, and use a correlated subquery for the column expression::
522
523 from __future__ import annotations
524
525 from decimal import Decimal
526 from typing import List
527
528 from sqlalchemy import ForeignKey
529 from sqlalchemy import func
530 from sqlalchemy import Numeric
531 from sqlalchemy import select
532 from sqlalchemy import SQLColumnExpression
533 from sqlalchemy import String
534 from sqlalchemy.ext.hybrid import hybrid_property
535 from sqlalchemy.orm import DeclarativeBase
536 from sqlalchemy.orm import Mapped
537 from sqlalchemy.orm import mapped_column
538 from sqlalchemy.orm import relationship
539
540
541 class Base(DeclarativeBase):
542 pass
543
544
545 class SavingsAccount(Base):
546 __tablename__ = "account"
547 id: Mapped[int] = mapped_column(primary_key=True)
548 user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
549 balance: Mapped[Decimal] = mapped_column(Numeric(15, 5))
550
551 owner: Mapped[User] = relationship(back_populates="accounts")
552
553
554 class User(Base):
555 __tablename__ = "user"
556 id: Mapped[int] = mapped_column(primary_key=True)
557 name: Mapped[str] = mapped_column(String(100))
558
559 accounts: Mapped[List[SavingsAccount]] = relationship(
560 back_populates="owner", lazy="selectin"
561 )
562
563 @hybrid_property
564 def balance(self) -> Decimal:
565 return sum(
566 (acc.balance for acc in self.accounts), start=Decimal("0")
567 )
568
569 @balance.inplace.expression
570 @classmethod
571 def _balance_expression(cls) -> SQLColumnExpression[Decimal]:
572 return (
573 select(func.sum(SavingsAccount.balance))
574 .where(SavingsAccount.user_id == cls.id)
575 .label("total_balance")
576 )
577
578The above recipe will give us the ``balance`` column which renders
579a correlated SELECT:
580
581.. sourcecode:: pycon+sql
582
583 >>> from sqlalchemy import select
584 >>> print(select(User).filter(User.balance > 400))
585 {printsql}SELECT "user".id, "user".name
586 FROM "user"
587 WHERE (
588 SELECT sum(account.balance) AS sum_1 FROM account
589 WHERE account.user_id = "user".id
590 ) > :param_1
591
592
593.. _hybrid_custom_comparators:
594
595Building Custom Comparators
596---------------------------
597
598The hybrid property also includes a helper that allows construction of
599custom comparators. A comparator object allows one to customize the
600behavior of each SQLAlchemy expression operator individually. They
601are useful when creating custom types that have some highly
602idiosyncratic behavior on the SQL side.
603
604.. note:: The :meth:`.hybrid_property.comparator` decorator introduced
605 in this section **replaces** the use of the
606 :meth:`.hybrid_property.expression` decorator.
607 They cannot be used together.
608
609The example class below allows case-insensitive comparisons on the attribute
610named ``word_insensitive``::
611
612 from __future__ import annotations
613
614 from typing import Any
615
616 from sqlalchemy import ColumnElement
617 from sqlalchemy import func
618 from sqlalchemy.ext.hybrid import Comparator
619 from sqlalchemy.ext.hybrid import hybrid_property
620 from sqlalchemy.orm import DeclarativeBase
621 from sqlalchemy.orm import Mapped
622 from sqlalchemy.orm import mapped_column
623
624
625 class Base(DeclarativeBase):
626 pass
627
628
629 class CaseInsensitiveComparator(Comparator[str]):
630 def __eq__(self, other: Any) -> ColumnElement[bool]: # type: ignore[override] # noqa: E501
631 return func.lower(self.__clause_element__()) == func.lower(other)
632
633
634 class SearchWord(Base):
635 __tablename__ = "searchword"
636
637 id: Mapped[int] = mapped_column(primary_key=True)
638 word: Mapped[str]
639
640 @hybrid_property
641 def word_insensitive(self) -> str:
642 return self.word.lower()
643
644 @word_insensitive.inplace.comparator
645 @classmethod
646 def _word_insensitive_comparator(cls) -> CaseInsensitiveComparator:
647 return CaseInsensitiveComparator(cls.word)
648
649Above, SQL expressions against ``word_insensitive`` will apply the ``LOWER()``
650SQL function to both sides:
651
652.. sourcecode:: pycon+sql
653
654 >>> from sqlalchemy import select
655 >>> print(select(SearchWord).filter_by(word_insensitive="Trucks"))
656 {printsql}SELECT searchword.id, searchword.word
657 FROM searchword
658 WHERE lower(searchword.word) = lower(:lower_1)
659
660
661The ``CaseInsensitiveComparator`` above implements part of the
662:class:`.ColumnOperators` interface. A "coercion" operation like
663lowercasing can be applied to all comparison operations (i.e. ``eq``,
664``lt``, ``gt``, etc.) using :meth:`.Operators.operate`::
665
666 class CaseInsensitiveComparator(Comparator):
667 def operate(self, op, other, **kwargs):
668 return op(
669 func.lower(self.__clause_element__()),
670 func.lower(other),
671 **kwargs,
672 )
673
674.. _hybrid_reuse_subclass:
675
676Reusing Hybrid Properties across Subclasses
677-------------------------------------------
678
679A hybrid can be referred to from a superclass, to allow modifying
680methods like :meth:`.hybrid_property.getter`, :meth:`.hybrid_property.setter`
681to be used to redefine those methods on a subclass. This is similar to
682how the standard Python ``@property`` object works::
683
684 class FirstNameOnly(Base):
685 # ...
686
687 first_name: Mapped[str]
688
689 @hybrid_property
690 def name(self) -> str:
691 return self.first_name
692
693 @name.inplace.setter
694 def _name_setter(self, value: str) -> None:
695 self.first_name = value
696
697
698 class FirstNameLastName(FirstNameOnly):
699 # ...
700
701 last_name: Mapped[str]
702
703 # 'inplace' is not used here; calling getter creates a copy
704 # of FirstNameOnly.name that is local to FirstNameLastName
705 @FirstNameOnly.name.getter
706 def name(self) -> str:
707 return self.first_name + " " + self.last_name
708
709 @name.inplace.setter
710 def _name_setter(self, value: str) -> None:
711 self.first_name, self.last_name = value.split(" ", 1)
712
713Above, the ``FirstNameLastName`` class refers to the hybrid from
714``FirstNameOnly.name`` to repurpose its getter and setter for the subclass.
715
716When overriding :meth:`.hybrid_property.expression` and
717:meth:`.hybrid_property.comparator` alone as the first reference to the
718superclass, these names conflict with the same-named accessors on the class-
719level :class:`.QueryableAttribute` object returned at the class level. To
720override these methods when referring directly to the parent class descriptor,
721add the special qualifier :attr:`.hybrid_property.overrides`, which will de-
722reference the instrumented attribute back to the hybrid object::
723
724 class FirstNameLastName(FirstNameOnly):
725 # ...
726
727 last_name: Mapped[str]
728
729 @FirstNameOnly.name.overrides.expression
730 @classmethod
731 def name(cls):
732 return func.concat(cls.first_name, " ", cls.last_name)
733
734Hybrid Value Objects
735--------------------
736
737Note in our previous example, if we were to compare the ``word_insensitive``
738attribute of a ``SearchWord`` instance to a plain Python string, the plain
739Python string would not be coerced to lower case - the
740``CaseInsensitiveComparator`` we built, being returned by
741``@word_insensitive.comparator``, only applies to the SQL side.
742
743A more comprehensive form of the custom comparator is to construct a *Hybrid
744Value Object*. This technique applies the target value or expression to a value
745object which is then returned by the accessor in all cases. The value object
746allows control of all operations upon the value as well as how compared values
747are treated, both on the SQL expression side as well as the Python value side.
748Replacing the previous ``CaseInsensitiveComparator`` class with a new
749``CaseInsensitiveWord`` class::
750
751 class CaseInsensitiveWord(Comparator):
752 "Hybrid value representing a lower case representation of a word."
753
754 def __init__(self, word):
755 if isinstance(word, basestring):
756 self.word = word.lower()
757 elif isinstance(word, CaseInsensitiveWord):
758 self.word = word.word
759 else:
760 self.word = func.lower(word)
761
762 def operate(self, op, other, **kwargs):
763 if not isinstance(other, CaseInsensitiveWord):
764 other = CaseInsensitiveWord(other)
765 return op(self.word, other.word, **kwargs)
766
767 def __clause_element__(self):
768 return self.word
769
770 def __str__(self):
771 return self.word
772
773 key = "word"
774 "Label to apply to Query tuple results"
775
776Above, the ``CaseInsensitiveWord`` object represents ``self.word``, which may
777be a SQL function, or may be a Python native. By overriding ``operate()`` and
778``__clause_element__()`` to work in terms of ``self.word``, all comparison
779operations will work against the "converted" form of ``word``, whether it be
780SQL side or Python side. Our ``SearchWord`` class can now deliver the
781``CaseInsensitiveWord`` object unconditionally from a single hybrid call::
782
783 class SearchWord(Base):
784 __tablename__ = "searchword"
785 id: Mapped[int] = mapped_column(primary_key=True)
786 word: Mapped[str]
787
788 @hybrid_property
789 def word_insensitive(self) -> CaseInsensitiveWord:
790 return CaseInsensitiveWord(self.word)
791
792The ``word_insensitive`` attribute now has case-insensitive comparison behavior
793universally, including SQL expression vs. Python expression (note the Python
794value is converted to lower case on the Python side here):
795
796.. sourcecode:: pycon+sql
797
798 >>> print(select(SearchWord).filter_by(word_insensitive="Trucks"))
799 {printsql}SELECT searchword.id AS searchword_id, searchword.word AS searchword_word
800 FROM searchword
801 WHERE lower(searchword.word) = :lower_1
802
803SQL expression versus SQL expression:
804
805.. sourcecode:: pycon+sql
806
807 >>> from sqlalchemy.orm import aliased
808 >>> sw1 = aliased(SearchWord)
809 >>> sw2 = aliased(SearchWord)
810 >>> print(
811 ... select(sw1.word_insensitive, sw2.word_insensitive).filter(
812 ... sw1.word_insensitive > sw2.word_insensitive
813 ... )
814 ... )
815 {printsql}SELECT lower(searchword_1.word) AS lower_1,
816 lower(searchword_2.word) AS lower_2
817 FROM searchword AS searchword_1, searchword AS searchword_2
818 WHERE lower(searchword_1.word) > lower(searchword_2.word)
819
820Python only expression::
821
822 >>> ws1 = SearchWord(word="SomeWord")
823 >>> ws1.word_insensitive == "sOmEwOrD"
824 True
825 >>> ws1.word_insensitive == "XOmEwOrX"
826 False
827 >>> print(ws1.word_insensitive)
828 someword
829
830The Hybrid Value pattern is very useful for any kind of value that may have
831multiple representations, such as timestamps, time deltas, units of
832measurement, currencies and encrypted passwords.
833
834.. seealso::
835
836 `Hybrids and Value Agnostic Types
837 <https://techspot.zzzeek.org/2011/10/21/hybrids-and-value-agnostic-types/>`_
838 - on the techspot.zzzeek.org blog
839
840 `Value Agnostic Types, Part II
841 <https://techspot.zzzeek.org/2011/10/29/value-agnostic-types-part-ii/>`_ -
842 on the techspot.zzzeek.org blog
843
844
845""" # noqa
846
847from __future__ import annotations
848
849from typing import Any
850from typing import Callable
851from typing import cast
852from typing import Generic
853from typing import List
854from typing import Optional
855from typing import overload
856from typing import Sequence
857from typing import Tuple
858from typing import Type
859from typing import TYPE_CHECKING
860from typing import TypeVar
861from typing import Union
862
863from .. import util
864from ..orm import attributes
865from ..orm import InspectionAttrExtensionType
866from ..orm import interfaces
867from ..orm import ORMDescriptor
868from ..orm.attributes import QueryableAttribute
869from ..sql import roles
870from ..sql._typing import is_has_clause_element
871from ..sql.elements import ColumnElement
872from ..sql.elements import SQLCoreOperations
873from ..util.typing import Concatenate
874from ..util.typing import Literal
875from ..util.typing import ParamSpec
876from ..util.typing import Protocol
877from ..util.typing import Self
878
879if TYPE_CHECKING:
880 from ..orm.interfaces import MapperProperty
881 from ..orm.util import AliasedInsp
882 from ..sql import SQLColumnExpression
883 from ..sql._typing import _ColumnExpressionArgument
884 from ..sql._typing import _DMLColumnArgument
885 from ..sql._typing import _HasClauseElement
886 from ..sql._typing import _InfoType
887 from ..sql.operators import OperatorType
888
889_P = ParamSpec("_P")
890_R = TypeVar("_R")
891_T = TypeVar("_T", bound=Any)
892_TE = TypeVar("_TE", bound=Any)
893_T_co = TypeVar("_T_co", bound=Any, covariant=True)
894_T_con = TypeVar("_T_con", bound=Any, contravariant=True)
895
896
897class HybridExtensionType(InspectionAttrExtensionType):
898 HYBRID_METHOD = "HYBRID_METHOD"
899 """Symbol indicating an :class:`InspectionAttr` that's
900 of type :class:`.hybrid_method`.
901
902 Is assigned to the :attr:`.InspectionAttr.extension_type`
903 attribute.
904
905 .. seealso::
906
907 :attr:`_orm.Mapper.all_orm_attributes`
908
909 """
910
911 HYBRID_PROPERTY = "HYBRID_PROPERTY"
912 """Symbol indicating an :class:`InspectionAttr` that's
913 of type :class:`.hybrid_method`.
914
915 Is assigned to the :attr:`.InspectionAttr.extension_type`
916 attribute.
917
918 .. seealso::
919
920 :attr:`_orm.Mapper.all_orm_attributes`
921
922 """
923
924
925class _HybridGetterType(Protocol[_T_co]):
926 def __call__(s, __self: Any) -> _T_co: ...
927
928
929class _HybridSetterType(Protocol[_T_con]):
930 def __call__(s, __self: Any, value: _T_con) -> None: ...
931
932
933class _HybridUpdaterType(Protocol[_T_con]):
934 def __call__(
935 s,
936 cls: Any,
937 value: Union[_T_con, _ColumnExpressionArgument[_T_con]],
938 ) -> List[Tuple[_DMLColumnArgument, Any]]: ...
939
940
941class _HybridDeleterType(Protocol[_T_co]):
942 def __call__(s, __self: Any) -> None: ...
943
944
945class _HybridExprCallableType(Protocol[_T_co]):
946 def __call__(
947 s, __cls: Any
948 ) -> Union[_HasClauseElement[_T_co], SQLColumnExpression[_T_co]]: ...
949
950
951class _HybridComparatorCallableType(Protocol[_T]):
952 def __call__(self, cls: Any) -> Comparator[_T]: ...
953
954
955class _HybridClassLevelAccessor(QueryableAttribute[_T]):
956 """Describe the object returned by a hybrid_property() when
957 called as a class-level descriptor.
958
959 """
960
961 if TYPE_CHECKING:
962
963 def getter(
964 self, fget: _HybridGetterType[_T]
965 ) -> hybrid_property[_T]: ...
966
967 def setter(
968 self, fset: _HybridSetterType[_T]
969 ) -> hybrid_property[_T]: ...
970
971 def deleter(
972 self, fdel: _HybridDeleterType[_T]
973 ) -> hybrid_property[_T]: ...
974
975 @property
976 def overrides(self) -> hybrid_property[_T]: ...
977
978 def update_expression(
979 self, meth: _HybridUpdaterType[_T]
980 ) -> hybrid_property[_T]: ...
981
982
983class hybrid_method(interfaces.InspectionAttrInfo, Generic[_P, _R]):
984 """A decorator which allows definition of a Python object method with both
985 instance-level and class-level behavior.
986
987 """
988
989 is_attribute = True
990 extension_type = HybridExtensionType.HYBRID_METHOD
991
992 def __init__(
993 self,
994 func: Callable[Concatenate[Any, _P], _R],
995 expr: Optional[
996 Callable[Concatenate[Any, _P], SQLCoreOperations[_R]]
997 ] = None,
998 ):
999 """Create a new :class:`.hybrid_method`.
1000
1001 Usage is typically via decorator::
1002
1003 from sqlalchemy.ext.hybrid import hybrid_method
1004
1005
1006 class SomeClass:
1007 @hybrid_method
1008 def value(self, x, y):
1009 return self._value + x + y
1010
1011 @value.expression
1012 @classmethod
1013 def value(cls, x, y):
1014 return func.some_function(cls._value, x, y)
1015
1016 """
1017 self.func = func
1018 if expr is not None:
1019 self.expression(expr)
1020 else:
1021 self.expression(func) # type: ignore
1022
1023 @property
1024 def inplace(self) -> Self:
1025 """Return the inplace mutator for this :class:`.hybrid_method`.
1026
1027 The :class:`.hybrid_method` class already performs "in place" mutation
1028 when the :meth:`.hybrid_method.expression` decorator is called,
1029 so this attribute returns Self.
1030
1031 .. versionadded:: 2.0.4
1032
1033 .. seealso::
1034
1035 :ref:`hybrid_pep484_naming`
1036
1037 """
1038 return self
1039
1040 @overload
1041 def __get__(
1042 self, instance: Literal[None], owner: Type[object]
1043 ) -> Callable[_P, SQLCoreOperations[_R]]: ...
1044
1045 @overload
1046 def __get__(
1047 self, instance: object, owner: Type[object]
1048 ) -> Callable[_P, _R]: ...
1049
1050 def __get__(
1051 self, instance: Optional[object], owner: Type[object]
1052 ) -> Union[Callable[_P, _R], Callable[_P, SQLCoreOperations[_R]]]:
1053 if instance is None:
1054 return self.expr.__get__(owner, owner) # type: ignore
1055 else:
1056 return self.func.__get__(instance, owner) # type: ignore
1057
1058 def expression(
1059 self, expr: Callable[Concatenate[Any, _P], SQLCoreOperations[_R]]
1060 ) -> hybrid_method[_P, _R]:
1061 """Provide a modifying decorator that defines a
1062 SQL-expression producing method."""
1063
1064 self.expr = expr
1065 if not self.expr.__doc__:
1066 self.expr.__doc__ = self.func.__doc__
1067 return self
1068
1069
1070def _unwrap_classmethod(meth: _T) -> _T:
1071 if isinstance(meth, classmethod):
1072 return meth.__func__ # type: ignore
1073 else:
1074 return meth
1075
1076
1077class hybrid_property(interfaces.InspectionAttrInfo, ORMDescriptor[_T]):
1078 """A decorator which allows definition of a Python descriptor with both
1079 instance-level and class-level behavior.
1080
1081 """
1082
1083 is_attribute = True
1084 extension_type = HybridExtensionType.HYBRID_PROPERTY
1085
1086 __name__: str
1087
1088 def __init__(
1089 self,
1090 fget: _HybridGetterType[_T],
1091 fset: Optional[_HybridSetterType[_T]] = None,
1092 fdel: Optional[_HybridDeleterType[_T]] = None,
1093 expr: Optional[_HybridExprCallableType[_T]] = None,
1094 custom_comparator: Optional[Comparator[_T]] = None,
1095 update_expr: Optional[_HybridUpdaterType[_T]] = None,
1096 ):
1097 """Create a new :class:`.hybrid_property`.
1098
1099 Usage is typically via decorator::
1100
1101 from sqlalchemy.ext.hybrid import hybrid_property
1102
1103
1104 class SomeClass:
1105 @hybrid_property
1106 def value(self):
1107 return self._value
1108
1109 @value.setter
1110 def value(self, value):
1111 self._value = value
1112
1113 """
1114 self.fget = fget
1115 self.fset = fset
1116 self.fdel = fdel
1117 self.expr = _unwrap_classmethod(expr)
1118 self.custom_comparator = _unwrap_classmethod(custom_comparator)
1119 self.update_expr = _unwrap_classmethod(update_expr)
1120 util.update_wrapper(self, fget) # type: ignore[arg-type]
1121
1122 @overload
1123 def __get__(self, instance: Any, owner: Literal[None]) -> Self: ...
1124
1125 @overload
1126 def __get__(
1127 self, instance: Literal[None], owner: Type[object]
1128 ) -> _HybridClassLevelAccessor[_T]: ...
1129
1130 @overload
1131 def __get__(self, instance: object, owner: Type[object]) -> _T: ...
1132
1133 def __get__(
1134 self, instance: Optional[object], owner: Optional[Type[object]]
1135 ) -> Union[hybrid_property[_T], _HybridClassLevelAccessor[_T], _T]:
1136 if owner is None:
1137 return self
1138 elif instance is None:
1139 return self._expr_comparator(owner)
1140 else:
1141 return self.fget(instance)
1142
1143 def __set__(
1144 self, instance: object, value: Union[SQLCoreOperations[_T], _T]
1145 ) -> None:
1146 if self.fset is None:
1147 raise AttributeError("can't set attribute")
1148 self.fset(instance, value) # type: ignore[arg-type]
1149
1150 def __delete__(self, instance: object) -> None:
1151 if self.fdel is None:
1152 raise AttributeError("can't delete attribute")
1153 self.fdel(instance)
1154
1155 def _copy(self, **kw: Any) -> hybrid_property[_T]:
1156 defaults = {
1157 key: value
1158 for key, value in self.__dict__.items()
1159 if not key.startswith("_")
1160 }
1161 defaults.update(**kw)
1162 return type(self)(**defaults)
1163
1164 @property
1165 def overrides(self) -> Self:
1166 """Prefix for a method that is overriding an existing attribute.
1167
1168 The :attr:`.hybrid_property.overrides` accessor just returns
1169 this hybrid object, which when called at the class level from
1170 a parent class, will de-reference the "instrumented attribute"
1171 normally returned at this level, and allow modifying decorators
1172 like :meth:`.hybrid_property.expression` and
1173 :meth:`.hybrid_property.comparator`
1174 to be used without conflicting with the same-named attributes
1175 normally present on the :class:`.QueryableAttribute`::
1176
1177 class SuperClass:
1178 # ...
1179
1180 @hybrid_property
1181 def foobar(self):
1182 return self._foobar
1183
1184
1185 class SubClass(SuperClass):
1186 # ...
1187
1188 @SuperClass.foobar.overrides.expression
1189 def foobar(cls):
1190 return func.subfoobar(self._foobar)
1191
1192 .. versionadded:: 1.2
1193
1194 .. seealso::
1195
1196 :ref:`hybrid_reuse_subclass`
1197
1198 """
1199 return self
1200