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 f(title) function that gets a string and returns one of 3 string options: good, maybe, or never. I am trying to get the filtered rows:

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

But the program fails, causing this error:

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

How can I solve this?

Answer 1

The problem is this:

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

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

If the objects in question did not implement __bool__ and did not __bool__ error, or the implementation did not give you, you could get quite cryptic errors due to the short circuit 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 can lead to hair loss in the form of incorrect SQL expressions:

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

Use the and_() , or_() and not_() sql functions, or binary & , | to create logical SQL statements. 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 in multiple criteria for calling Query.filter() :

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

or combine several calls to filter() :

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