SQLAlchemy: unexpected results when using `and` and `or`

Question

I have a declarative base class News:

class News(Base):
    __tablename__ = "news"
    id = Column(Integer, primary_key = True)
    title = Column(String)
    author = Column(String)
    url = Column(String)
    comments = Column(Integer)
    points = Column(Integer)
    label = Column(String)

I also have a function f(title), that gets a string and returns one of 3 variants of strings: 'good', 'maybe' or 'never'. I try to get filtered rows:

rows = s.query(News).filter(News.label == None and f(News.title) == 'good').all()

But the program fails, raising this error:

raise TypeError("Boolean value of this clause is not defined")

How can I resolve it?

@IljaEverilä You should post that as an answer, I almost missed your comment and was already writing the same thing. — Markus Meskanen, Mar 09 '17 at 11:54
I was trying to find an existing duplicate for this, but to some surprise could not. — Ilja Everilä, Mar 09 '17 at 11:56

Ilja Everilä · Accepted Answer · 2018-10-31T10:43:52.207

The problem is this:

News.label == None and f(News.title) == 'good'
#                  ^^^ here

Python does not allow overriding the behaviour of boolean operations and and or. You can influence them to some extent with __bool__ in Python 3 and __nonzero__ in Python 2, but all that does is that it defines the truth value of your object.

If the objects in question had not implemented __bool__ and thrown the error, or the implementation had not thrown, you would've gotten possibly rather cryptic errors due to the short-circuiting nature of and and or:

In [19]: (News.label == 'asdf') and True
Out[19]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7f62c416fa58>

In [24]: (News.label == 'asdf') or True
Out[24]: True

because

In [26]: bool(News.label == 'asdf')
Out[26]: False

This could and would lead to hair pulling in the form of incorrect SQL expressions:

In [28]: print(News.label == 'asdf' or News.author == 'NOT WHAT YOU EXPECTED')
news.author = :author_1

To produce boolean SQL expressions either use the and_(), or_(), and not_() sql expression functions, or the binary &, |, and ~ operator overloads:

# Parentheses required due to operator precedence
filter((News.label == None) & (f(News.title) == 'good'))

or

filter(and_(News.label == None, f(News.title) == 'good'))

or pass multiple criterion to a call to Query.filter():

filter(News.label == None, f(News.title) == 'good')

or combine multiple calls to filter():

filter(News.label == None).filter(f(News.title) == 'good')

Very thorough, thanks . Are Any idea if one of these methods is preferrable or better performant? I kind of like just calling .filter() twice as it's very readbile and can put each statement on a newline — Adam Hughes, Sep 20 '20 at 13:48
I'd imagine there being negligible difference in performance, since calls to `filter()` simply append new criterion to the `Query` – it is compiled to SQL and emitted only when you call `all()` etc. So choose the method that suits you best. — Ilja Everilä, Sep 21 '20 at 11:52

SQLAlchemy: unexpected results when using `and` and `or`

1 Answers1

Linked

Related