Hybrid Properties and Methods in SQLAlchemy: hybrid_property and hybrid_method

SQLAlchemy provides many ORM functionality extensions in its sqlalchemy.ext library. Today, we’re going to introduce the very powerful Hybrid Attributes feature.

This article’s code examples use Flask-SQLAlchemy for illustration. The differences with native SQLAlchemy are minor, mainly:

• db.Model is roughly equivalent to sqlalchemy.ext.declarative.declarative_base
• Model.query is roughly equivalent to db.session.query(Model)

hybrid_property

The @property decorator is a very useful piece of syntax sugar in Python, effectively hiding internal class logic from the outside, thereby improving code aesthetics. For example, we often define classes like this:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.SmallInteger(), default=1)
    
    @property
    def disabled(self):
        return self.status == 0

Thus, when we access User().disabled, we don’t need to worry about what the actual value of disabled is.

However, when we need to query the database, we still need to know the corresponding value to filter by. To address this issue, SQLAlchemy provides the hybrid_property decorator.

We just need to simply modify the above code like this:

from sqlalchemy.ext.hybrid import hybrid_property

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.SmallInteger(), default=1)
    
    @hybrid_property
    def disabled(self):
        return self.status == 0

After that, we can use a very concise syntax for querying, like this:

disabled_users = User.query.filter(User.disabled).all()

The principle behind this is that the hybrid_property on the class will return an sqlalchemy.sql.elements.BinaryExpression, which is a SQL expression, similar to directly using

disabled_users = User.query.filter(User.status == 0).all()

It is equivalent to directly using, but undoubtedly more elegant and easier to maintain. This is one of the additional benefits brought by SQLAlchemy's approach of "overriding Python's standard operators", providing a more object-oriented feature compared to Django ORM's `FIELD__OPERATOR=VALUE` syntax, which might be simpler to grasp initially but lacks the object-oriented nature found in SQLAlchemy.

### `hybrid_method`

`hybrid_property` is already very powerful, but SQLAlchemy has an even more magical `hybrid_method`. This means that your methods can also be used in queries.

For example, let's define a function like this:


```python
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method

USER_STATUS = (
    (0, "disabled"),
    (1, "active")
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.SmallInteger(), default=1)

    @hybrid_property
    def disabled(self):
        return self.status == 0

    @hybrid_method
    def is_(self, status):
        return self.status == dict((y, x) for x, y in USER_STATUS)[status]

Here, we implemented a simplest hybrid_method, which serves the same function as the hybrid_property example above, except it accepts a string as input.

This method can also be used in both queries and on instances:

>>> user = query.first()
>>> print(user.is_("disabled"))
True
>>> print(User.query.filter(User.is_("disabled")).all())
[<__main__.User object at 0x10dd0f610>]

Limitations

We’ve introduced the two simplest uses of hybrid attributes, but in actual application, these methods are quite limited (of course, we can’t expect to write complex logic in a hybrid_method with hundreds of lines of code and then have SQLAlchemy automatically translate all Python logic into SQL).

Some readers might have already realized that there’s nothing particularly mystical about hybrid attributes; they are just methods that are both classmethods and instancemethods.

In SQLAlchemy, a class property (Model or Mapper) represents a field, while an instance property of the class represents the value of that field.

This means that the self we call in our hybrid attributes is equivalent to cls during queries. Therefore, any property or method we call on self must also exist on cls.

For example, we can’t use the following approach in a hybrid attribute:

"peter" in self.name          # 不合法, User.name 字段不能使用 in 进行比较， 应该使用 User.name.contains()
self.name.contains("peter")   # 不合法, 实例的 name 字段为 unicode 值，没有 contains 方法
self.name.startswith("peter") # 合法, unicode 与 InstrumentedAttribute 都有 startswith 方法

In general, methods implemented on both InstrumentedAttribute and its corresponding python_type, usually are built-in methods, such as

• __eq__: self.name == "peter" is valid
• __gt__: self.id > 5 is valid

Additionally, bitwise operators (<<, >>, &, |, ~, ^) can also be safely used, and some logical judgments can be handled with bitwise operations to solve.

Breaking the Limitations

As seen above, if hybrid attributes stopped there, they wouldn’t be very flexible, and in fact, their applications would be quite limited. Therefore, SQLAlchemy also provides a mechanism that allows users to separately define the logic on Python and the logic for generating SQL statements. Let’s extend the previous example a bit:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    @hybrid_property
    def called_jack(self):
        return "Jack" in self.name

    @called_jack.expression
    def called_jack(cls):
        return cls.name.contains("Jack")

This way, we can break through the limitations mentioned above, and we can define functions used in queries as database functions according to the actual situation, improving query efficiency.

A Little Bonus

hybrid_property can also define a setter, with the syntax being the same as a regular setter. Here’s an example directly from the documentation:

class Interval(object):
    # ...

    @hybrid_property
    def length(self):
        return self.end - self.start

    @length.setter
    def length(self, value):
        self.end = self.start + value

Some Thoughts

At this point, I’ve only introduced less than half of the content about hybrid attributes. The second half involves more advanced usage, such as handling relationships, custom Comparators, etc. I won’t expand on those here but may write another article in the future if needed. However, my thought is that, overall, the hybrid attribute module is a piece of syntax sugar that makes the logic clearer and more OOP. To some extent, it can also reduce the amount of code, making it suitable for those obsessed with object-oriented programming (like me). However, I believe this syntax sugar should not be abused due to its inherent limitations. For overly complex logic, it might actually increase the amount of code and reduce readability. Therefore, it should be used in moderation.

References

• Hybrid Attributes