python - 如何在 SQLAlchemy 中使用 "or_"或 "and_"构造稍微复杂的过滤器

Question

我正在尝试从术语列表中进行非常简单的搜索

terms = ['term1', 'term2', 'term3']

我如何以编程方式浏览术语列表并从术语列表中构造条件，以便我可以使用 filter 和 or_ 或 _and?

query.filter(or_(#something constructed from terms))

Answer 1

如果您有一个术语列表并且想要查找某个字段与其中一个匹配的行，那么您可以使用 in_() 方法:

terms = ['term1', 'term2', 'term3']
query.filter(Cls.field.in_(terms))

如果你想做更复杂的事情，那么 or_() 和 and_() 将 ClauseElement 对象作为参数。 ClauseElement 及其子类基本上代表 SQL AST您的查询。通常，您通过在 Column 或 InstrumentedAttribute 对象上调用比较运算符来创建子句元素:

# Create the clause element
clause = (users_table.columns['name'] == "something")
#    you can also use the shorthand users_table.c.name

# The clause is a binary expression ...
print(type(clause))
#    <class 'sqlalchemy.sql.expression._BinaryExpression'>
# ... that compares a column for equality with a bound value.
print(type(clause.left), clause.operator, type(clause.right))
#    <class 'sqlalchemy.schema.Column'>, <built-in function eq>,
#    <class 'sqlalchemy.sql.expression._BindParamClause'>

# str() compiles it to SQL
print(str(clause)) 
# users.name = ?

# You can also do that with ORM attributes
clause = (User.name == "something")
print(str(clause))
# users.name = ?

您可以像处理任何 Python 对象一样处理表示您的条件的子句元素，将它们放入列表中，将它们组合成其他子句元素等。因此您可以执行以下操作:

# Collect the separate conditions to a list
conditions = []
for term in terms:
    conditions.append(User.name == term)

# Combine them with or to a BooleanClauseList
condition = or_(*conditions)

# Can now use the clause element as a predicate in queries
query = query.filter(condition)
# or to view the SQL fragment
print(str(condition))
#    users.name = ? OR users.name = ? OR users.name = ?