问题:SQLAlchemy:使用and和or时出现意外结果

我有一个声明性基类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)

我还有一个函数f(title),它获取一个字符串并返回 3 个字符串变体之一:'good'、'maybe' 或 'never'。我尝试获取过滤行:

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

但程序失败,引发此错误:

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

我该如何解决?

解答

问题是这样的:

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

Python 不允许覆盖布尔操作and和or的行为。您可以在 Python 3 中使用__bool__和 Python 2 中的__nonzero__来在一定程度上影响它们,但所做的只是它[定义了对象# 6 zwz1 的真值。

如果有问题的对象没有实现__bool__并抛出错误,或者实现没有抛出,那么由于and和or](https://docs.python.org/3/library/stdtypes.html#boolean-operations-and-or-not)的[短路性质,您可能会得到相当神秘的错误:

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

因为

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

这可能并且会导致以不正确的 SQL 表达式形式出现问题:

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'))

或者

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

或将多个标准传递给对Query.filter()的调用:

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

或组合对filter()的多次调用:

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