SQLAlchemy: checking if a filter has been set causes `TypeError: Boolean value of this clause is not defined`

I have set up a method in python that chunks a SQL IN-Query (our DB does not like too big IN-Clauses). The method receives also optionally a filter, and this is where my error comes. When I pass the filter in the code below I receive this error message:

File "/usr/local/lib/python3.11/dist-packages/sqlalchemy/sql/elements.py", line 3938, in __ bool__
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

I have looked up similar questions in stackoverflow, but it seems like I cant connect the dots.

Below you can also find the most important parts of my python implementation:

BaseModel

class Trades(BaseModel):
  # a lot of columns...
  INITIAL_ORDER_NUM = Column(DECIMAL)
  ALLO_TN = Column(DECIAL)

The chunking method that applies the filter

def fetch_trade_data_by_allo_tn(session_maker, trade_columns: list, allo_tns, chunk_size=1000, filters=None):
    trades_df_list = []
    with session_maker() as session:
        for allo_chunk in chunk_list(allo_tns, chunk_size=chunk_size):
            query = select(*trade_columns).where(Trades.ALLO_TN.in_(allo_chunk))
            if filters:
                query=query.where(filters)

            query = query.distinct()
            print(query)

            trades_df_chunk = pd.DataFrame(session.execute(query).fetchall())
            trades_df_list.append(trades_df_chunk)

    trades_df = pd.concat(trades_df_list)
    return trades_df

The filter condition

initial_order_num_exists = (Trades.INITIAL_ORDER_NUM.is_not(None))

In another part of my Code a similar condition (Trades.ORDER_NUM != 0) is working. It is also working when not using the filter.

Solution

The issue encountered here is that the filter in the if statement being coerced to a bool resulted in this unexpected error. I have constructed the following MVCE under the SQLAlchemy 2.0 style for ease of demonstration.

The first code block is the boilerplate that includes all required imports, class declaration and database creation and setup.

from typing import Optional
from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):
    ...

class Trade(Base):
    __tablename__ = 'trade'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column()
    value: Mapped[Optional[int]] = mapped_column()

def setup_db():
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    with Session(engine) as session:
        session.add(Trade(name='XYZ', value=None))
        session.add(Trade(name='XXY', value=2))
        session.add(Trade(name='ZYX', value=1))
        session.commit()
    return engine

This code block contains the simplified example as provided in the question that fully reproduce the issue seen.

def filter_tester(session, filters=None):
    query = (select(Trade.name, Trade.value)
        .where(Trade.name.in_(['XYZ', 'ZYX']))
    )
    if filters:
        query = query.where(filters)
    return list(session.execute(query).mappings())

def main():
    engine = setup_db()
    with Session(engine) as session:
        print(filter_tester(session, None))
        extra_filter = Trade.value.is_not(None)
        print(filter_tester(session, extra_filter))

if __name__ == '__main__':
    main()

Running the above produces the following output:

[{'name': 'XYZ', 'value': None}, {'name': 'ZYX', 'value': 1}]
Traceback (most recent call last):
  File "<file>", line 44, in <module>
    main()
  File "<file>", line 41, in main
    print(filter_tester(session, extra_filter))
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<file>", line 32, in filter_tester
    if filters:
       ^^^^^^^
  File "/tmp/env/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 3922, in __bool__
    raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined

The above example show that the problem lies in the fact that the generated sqlalchemy.sql.elements.BinaryExpression object (via Trade.value.is_not(None)) can't be used in a bare if statement (if filters: as per Traceback) as the coercing to a boolean will not behave correctly outside its intended usage context (i.e. as part of a statement execution). Changing the condition to check that filter is not None will resolve this issue.

def filter_tester(session, filters=None):
    query = (select(Trade.name, Trade.value)
        .where(Trade.name.in_(['XYZ', 'ZYX']))
    )
    if filters is not None:
    #         ^^^^^^^^^^^^ inserted corrected comparison.
        query = query.where(filters)
    return list(session.execute(query).mappings())

The code will now produce the expected output:

[{'name': 'XYZ', 'value': None}, {'name': 'ZYX', 'value': 1}]
[{'name': 'ZYX', 'value': 1}]