SQLAlchemy:使用“and"和“or"时出现意外结果

2022-01-19 00:00:00 python sqlalchemy boolean

问题描述

我有一个声明性基类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'.我尝试获取过滤后的行:

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

我该如何解决?

解决方案

问题是这样的:

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

Python 不允许覆盖布尔 操作 and 和 or 的行为.您可以通过 __bool__<在一定程度上影响它们/a> 在 Python 3 和 __nonzero__ 在 Python 2 中，但所做的只是它 定义对象的真值.

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.

如果有问题的对象没有实现 __bool__ 并抛出错误，或者没有抛出实现，那么由于 and 和 or 的短路特性:

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

因为

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

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

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

要生成布尔 SQL 表达式，请使用 and_(), or_() 和 not_() sql 表达式函数，或二进制 &, | 和 ~ 运算符重载:

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():

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

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

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

or combine multiple calls to filter():

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

相关文章