In SQLAlchemy, why does a hybrid_property sometimes need a separate expression form, and how do you add one?
answer
- one getter, two callers
- self can be the class
- Python logic runs once, at class level
- inplace plus classmethod decorator pair
basics
~20 sA hybrid_property runs one getter on instances, in Python, and on the class, to build SQL. Python-only logic such as an if, an f-string or abs() then builds wrong SQL or fails, so @prop.inplace.expression supplies a SQL form such as case().
solid answer
~40 sOn an instance, `hybrid_property` calls the getter with `self` and returns a Python value; on the class it calls the same function with the class, so `self.first_name` is a column and only operators that columns overload (`+`, `-`, comparisons, `&`, `|`) build SQL. Anything else does not translate. `if self.last_name is None` runs once at class level, where it is `False`, so the SQL always concatenates and rows with a NULL `last_name` never match. An f-string turns the attributes into a constant string, giving `WHERE false`, and `abs()` or `' '.join()` raise `TypeError`. The fix is a second method decorated `@full_name.inplace.expression` (plus `@classmethod` for type checkers) returning `case((cls.last_name.is_(None), cls.first_name), else_=cls.first_name + ' ' + cls.last_name)`. Without `.inplace`, a differently named method receives a modified copy and `full_name` keeps the broken SQL.
code
python · 39 linesfrom sqlalchemy import ColumnElement, String, case, select
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Member(Base):
__tablename__ = "member"
id: Mapped[int] = mapped_column(primary_key=True)
first_name: Mapped[str] = mapped_column(String(50))
last_name: Mapped[str | None] = mapped_column(String(50))
@hybrid_property
def label(self) -> str:
# Python-only: on the class this builds a plain string once
return f"{self.first_name} {self.last_name}"
@hybrid_property
def full_name(self) -> str:
if self.last_name is None:
return self.first_name
return self.first_name + " " + self.last_name
@full_name.inplace.expression
@classmethod
def _full_name_expression(cls) -> ColumnElement[str]:
return case(
(cls.last_name.is_(None), cls.first_name),
else_=cls.first_name + " " + cls.last_name,
)
print(select(Member.id).where(Member.label == "Ann Lee"))
print(select(Member.id).where(Member.full_name == "Ann Lee"))
print(Member(first_name="Bo", last_name=None).full_name)go deeper
Recall that a hybrid works on instances in Python and on the class in SQL, and that a separate expression method exists for when the two differ.
Explain why Python control flow runs once at class level, which constructs silently produce wrong SQL, and how .inplace.expression fixes it.
Show you would print the compiled SQL of every hybrid filter and check NULL handling on both sides before trusting the results.
Weigh hybrids that duplicate logic in Python and SQL against keeping derived values in one place, and set a review rule for hybrids used in filters.
## What a hybrid is `hybrid_property` (from `sqlalchemy.ext.hybrid`) defines an attribute with **two behaviours**: - **Instance level:** `member.full_name` calls the getter with the instance as `self`; it is ordinary Python on loaded values. - **Class level:** `Member.full_name` calls the *same* function with the class as `self`. Now `self.first_name` is a column attribute, and whatever the function returns is used as a SQL expression in `select()`, `where()` and `order_by()`. When the body uses only operations that columns overload the same way Python values do, one function serves both sides. `self.first_name + " " + self.last_name` works because `+` on string columns renders `first_name || :first_name_1 || last_name`. ## Where a single getter breaks At class level the getter runs **once**, while the statement is built, with no row in sight. Python control flow and Python functions are evaluated right then: | Getter body | Instance result | Class-level result | |---|---|---| | `if self.last_name is None: return self.first_name` | the right string for each row | the test is `False` (a column object is not `None`), so the SQL always concatenates | | `f"{self.first_name} {self.last_name}"` | a string, with `None` printed for a missing last name | a constant string built from the attributes' names; `== "Ann Lee"` becomes `WHERE false` | | `" ".join([self.first_name, self.last_name])` | a string, or `TypeError` when `last_name` is None | `TypeError: sequence item 0: expected str instance, InstrumentedAttribute found` | | `abs(self.balance_cents)` | the absolute value | `TypeError: bad operand type for abs(): 'InstrumentedAttribute'` | The first two are the dangerous ones: nothing fails, the query simply returns the wrong rows. In SQL, concatenating NULL yields NULL, so a member with no last name never matches. ## Adding the expression form 1. Keep the getter as the Python behaviour. 2. Add a second method and decorate it `@full_name.inplace.expression`, then `@classmethod`. 3. Return a SQL construct built from `cls`, using `case()`, `func.*` or `type_coerce()` where Python used `if`, built-ins or conversions. 4. Print `select(Member).where(Member.full_name == "x")` to read the SQL it now renders. ```python @full_name.inplace.expression @classmethod def _full_name_expression(cls) -> ColumnElement[str]: return case( (cls.last_name.is_(None), cls.first_name), else_=cls.first_name + " " + cls.last_name, ) ``` ## Why `.inplace` and `@classmethod` - **`.inplace`** makes the modifier change the existing hybrid in place. Plain `@full_name.expression` returns a **new copy**, the same way `@property.setter` does, so it only works if the method reuses the name `full_name`. On a method named `_full_name_expression` the copy lands under that name and `Member.full_name` keeps the old, wrong SQL, with no error. - **`@classmethod`** is optional. It tells type checkers that `cls` is the class, not an instance; behaviour is the same without it. - Reusing one name for every method is correct Python, but type checkers reject it; the `.inplace` style with distinct names is the form they accept. ## Related modifiers - `@full_name.inplace.setter` defines what assigning `member.full_name = ...` does on an instance. - `@full_name.inplace.update_expression` returns `(column, value)` pairs so the hybrid can appear in an ORM-enabled `update(...).values()`. - `@full_name.inplace.comparator` returns a `Comparator` subclass that redefines individual operators, such as a case-insensitive `==`. It **replaces** the expression form; the two cannot be combined on one hybrid. - `hybrid_method` applies the same idea to methods that take arguments, such as `contains(point)`. ## Deciding per derived attribute The same rule applies to every derived value on the subscriptions model. A `price_label` that formats cents for display has no use in a query, so a plain Python property is enough. A `total_cents` hybrid that sums a loaded `charges` collection in Python needs an expression form returning `select(func.sum(Charge.amount_cents)).where(Charge.subscription_id == cls.id).scalar_subquery()`, because a Python `sum()` over a collection has no class-level meaning. For each derived attribute: 1. Will any query filter, sort or select on it? If not, a plain property is enough. 2. Does the getter use only operators that columns overload? Then one hybrid getter serves both sides. 3. Otherwise, write the expression form and test it against real rows. ## Checking a hybrid - Test both sides: read the attribute on an instance, and compile a statement that filters on it. - Look for Python-only constructs in the getter: `if`, `and`/`or`/`not`, f-strings, built-ins, method calls on values. - Read NULL handling on each side separately: for string concatenation, Python raises `TypeError` on `None` where SQL returns NULL.
- When does a hybrid_property need no expression form at all?When the getter uses only operations that columns overload the way Python values do: arithmetic such as `self.end - self.start`, comparisons, string `+` (rendered as `||`), and `&` or `|` for boolean logic. Even then, check NULLs: `first_name + ' ' + last_name` raises `TypeError` in Python when `last_name` is None, but yields NULL in SQL.
- How do you make a hybrid writable, and usable in an ORM update()?`@full_name.inplace.setter` defines what assigning the attribute does on an instance, such as splitting the string into the two columns. For `update(Member).values({Member.full_name: 'Ann Lee'})`, add `@full_name.inplace.update_expression`, a class-level method returning a list of `(column, value)` pairs that SQLAlchemy places in the SET clause.
- How do hybrid_property.expression and hybrid_property.comparator differ?`expression` returns one SQL expression, and every operator then applies to it. `comparator` returns a `Comparator` subclass that redefines operators one by one, for example `==` wrapping both sides in `func.lower()`. A comparator replaces the expression form; the two are not used together on one hybrid.
- A hybrid_property reads its value from the database even on a loaded instance
- Python if statements in a hybrid getter are translated into a SQL CASE
- An f-string hybrid getter fails loudly when used in a query
- The @classmethod line is what makes the expression form take effect
- @full_name.expression on a differently named method still updates full_name