How to use SQLAlchemy hybrid_method and hybrid_property

I have read various SO answers, read the SQLA docs, googled, and asked Google Bard. But I still have found it difficult to understand exactly how SQLAlchemy's hybrid_method and hybrid_property work. How do I use them correctly, and what are the differences (if any) between them? I'm running SQLAlchemy 1.0.9 and Python 2.7.

Disclaimer:

After banging my head against this for a couple of weeks, I got things to work as needed. I wanted to provide info to help others (and my future self). However, I couldn't use my actual code so I've made up some scenarios in the below answer. Full disclouse -- I didn't setup and run the examples, but they should be sufficient to get the gist of things. Please feel free to comment any suggestions/changes I should make to improve this answer. Thanks!

Documentation:

• SQLA docs on hybrid attributes
• SQLA docs on hybrid_method
• SQLA docs on hybrid_property

Answer:

SQLAlchemy has a couple of nifty things, called hybrid_method and hybrid_property. These are essentially properties/methods on a SQLAlchemy class, which can be used in a query statement. The kicker is that they can be functions and DO stuff; for example, you could do:

class Address():
    ...

class User():
    first_name = Column(String)
    last_name = Column(String)
    num_orders = Column(Integer, nullable=True)
    orders = relationship('Orders')

    @hybrid_property
    def full_name(self):
            return self.first_name + ' ' + self.last_name

result = session.query(User).filter(User.full_name == 'john doe').first()

Now, the above is a fairly contrived example. In reality, you will probably want to do more complex things. Here is another example:

class OrderStatusEnum(enum.Enum):
        REJECTED = 'rejected'
        CANCELED = 'canceled'

class Orders
    status = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    order_count = Column(Integer)
    ...

    @hybrid_property
    def status_int(self):
      for cancel_status in OrderStatusEnum:
          if self.status == cancel_status.value:
              return 0
      else:
          return 1
    
    @status_int.expression
    def status_int(cls):
        return case([
            (cls.status == OrderStatusEnum.REJECTED.value, 0),
            (cls.status == OrderStatusEnum.CANCELED.value, 0),
        ], else_=1)

# QUERY
result = session.query(func.avg(Orders.status_int)).group_by(Orders.id).all()

You’ll notice in the above that there are now two functions called status_int. This is because SQLAlchemy needs one to evaluate in Python and one to evaluate in SQL. Here’s the difference:

# == Example 1 ==
order = session.query(Order).get(<id>)
order.status_int # --> has access to the specific instance, and will run the python method

# == Example 2 ==
result = session.query(func.avg(Orders.status_int)).group_by(Orders.id).all()
# runs the SQL Expression, because it does not have access to specific instances

hybrid_property and hybrid_method functions allow for the definition of a SQL Expression. This is done by defining the function a second time, but with a decorator that shares the name of the original function (see below). Additionally, it’s important to know that the python method and SQL expression should be functionally equivalent. This seems logical, but it’s worth highlighting.

@hybrid_property
def status_int(self)
        ... # python method

@status_int.expression
def status_int(cls) #class access not instance access
        ... # SQL expression

The hybrid_method is generally very similar, but there are a couple of “gotchas” to be aware of.

1. hybrid_method has class access, not instance access. So don’t expect to be able to refer to specific instance column value.

2. hybrid_method.expression function is defined using cls, not self — again, because it has class access not instance access (it’s basically a class method).

class Orders
    ...

    @hybrid_method
    def status_for_user(self, user):
            res = session.query(Orders.order_count).filter(Orders.user_id == user.id).first()
            if res > 0:
                    return 'active'
            elif res == 0:
                    return 'registered'
            else:
                    return 'inactive'

    @status_for_user.expression
    def status_for_user(cls, user):
            query = session.query(Orders.order_count).filter(Orders.user_id == user.id).first()

            return case([
                    (literal(query) > 0, 'active'),
                    (literal(query) == 0, 'registered')
            ], else_='inactive')

user = session.query(User).get(1)
result = session.query(User, Orders.id).join(Orders, Orders.user_id == User.id).filter(Orders.status_for_user(user) == 'active').all()

Hopefully this explanation with examples is helpful to others in the future. If I missed anything important, and/or there is an error, please let me know and I am happy to update.