SQLAlchemy - TypeError: Boolean value of this clause is not defined

Question

I'm creating a python application that has a database with results from different soccer matches.

I want the model to set its result field based on the values of the home_score and away_score fields.

I've worked a lot with Django - but not this time as its going to be a simple terminal application. If I was I would have sub-classed the save method with some of the code in the result method below.

Being somewhat new to SQLAlchemy I assumed that the @hybrid_property decorator was a good proxy for what I was trying to achieve.

However, when I run my unittests for this model it fails on the following line elif self.home_score > self.away_score:

with the following error:

TypeError: Boolean value of this clause is not defined

I've included the model below, does anyone know what I am doing wrong?

class Match(Base):
    __tablename__ = 'matches'

    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False)
    home_id = Column(Integer, ForeignKey('teams.id'))
    home = relationship(
        "Team", back_populates='home_matches', foreign_keys=[home_id]
    )
    away_id = Column(Integer, ForeignKey('teams.id'))
    away = relationship(
        "Team", back_populates='away_matches', foreign_keys=[away_id]
    )
    home_score = Column(Integer, nullable=False)
    away_score = Column(Integer, nullable=False)


    @hybrid_property
    def result(self):
        """ Set the match result, based on the team scores """
        if self.home_score == self.away_score:
            return 'draw'
        elif self.home_score > self.away_score:
            return 'home'
        elif self.home_score < self.away_score:
            return 'away'

Answer 1

The idea of a hybrid property is to produce equivalent SQL, when used in query context. For some simple expressions the same code works for both, but if not you must define the expression separately. In this case you can replace your Python with an SQL CASE expression:

from sqlalchemy import case

...

@hybrid_property
def result(self):
    """ Set the match result, based on the team scores """
    if self.home_score == self.away_score:
        return 'draw'
    elif self.home_score > self.away_score:
        return 'home'
    elif self.home_score < self.away_score:
        return 'away'

@result.expression
def result(cls):
    """ Set the match result, based on the team scores """
    return case([(cls.home_score == cls.away_score, 'draw'),
                 (cls.home_score > cls.away_score, 'home')],
                else_='away')