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