What's going on with SQLAlchemy hybrid_property for booleans?

Question

I setup a mapping...

from datetime import datetime

from sqlalchemy import (
    Column,
    Date,
    DateTime,
    Integer,
    func,
)
from sqlalchemy.ext.hybrid import hybrid_property
from orm.models import Base


class Specification(Base):
    """Specification of how a group of things will happen."""

    __tablename__ = "specifications"

    id = Column(Integer, primary_key=True)
    # other fields omitted ..

    date_range_start = Column(Date, nullable=False)
    date_range_end = Column(Date, nullable=False)

    on_hold_until = Column(DateTime, nullable=True)


    @hybrid_property
    def _utc_now(self):
        return datetime.utcnow()

    @_utc_now.expression
    def _utc_now(self):
        return func.timezone("utc", func.current_timestamp())

    @hybrid_property
    def is_on_hold(self):
        """It is on hold if the date has not passed yet."""
        return self.on_hold_until < self._utc_now

    @hybrid_property
    def is_active(self):
        """Only active within the current date ranges."""
        now = self._utc_now
        return (
            (self.is_on_hold == False)
            & (self.date_range_start <= now)
            & (self.date_range_end >= now)
        )

If I want to query Specifications which are currently active, I get the following valid SQL.

>>> print(session.query(Specification).filter_by(is_active=True))
SELECT <omited> 
FROM specifications 
WHERE ((specifications.on_hold_until < timezone(%(timezone_1)s, CURRENT_TIMESTAMP)) = false 
AND specifications.date_range_start <= timezone(%(timezone_2)s, CURRENT_TIMESTAMP) 
AND specifications.date_range_end >= timezone(%(timezone_2)s, CURRENT_TIMESTAMP)) = true

However if I change the is_on_hold comparison to

    (self.is_on_hold is False)

or even

    (not self.is_on_hold)

I get the following error:

TypeError: unsupported operand type(s) for &: 'bool' and 'BinaryExpression'

What is happening here? And how can I use the "normal" Python expressions like self.is_on_hold instead of self.is_on_hold == True/False all the time?

Related: https://stackoverflow.com/questions/42681231/sqlalchemy-unexpected-results-when-using-and-and-or, https://stackoverflow.com/questions/48274553/sqlalchemy-core-select-where-condition-contains-boolean-expression-is-false, https://stackoverflow.com/questions/18998010/flake8-complains-on-boolean-comparison-in-filter-clause — Ilja Everilä, Jun 25 '20 at 06:11

score 0 · Answer 1 · answered Aug 21 '20 at 16:12

0

The short answer: You can't.

To implement this logic SQLAlchemy overloads the operators in question. But not all operators are overloadable like in other languages. As such, only the overloadable operators can be used for these SQLAlchemy expressions.

answered Aug 21 '20 at 16:12

Joao Carlos

749
1
11
32

What's going on with SQLAlchemy hybrid_property for booleans?

1 Answers1