如何使用"or_"构造稍微复杂一些的过滤器.或"and_"在SQLAlchemy中 [英] How to construct a slightly more complex filter using "or_" or "and_" in SQLAlchemy

问题描述

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

I'm trying to do a very simple search from a list of terms

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

如何以编程方式浏览术语列表并从术语列表构建条件，以便可以使用filter和or_或_and进行查询?

How do I programmatically go through the list of terms and construct the conditions from the list of terms so that I can make the query using filter and or_ or _and?

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

推荐答案

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

If you have a list of terms and want to find rows where a field matches one of them, then you could use the in_() method:

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

如果要执行更复杂的操作，则or_()和and_()将ClauseElement对象作为参数. ClauseElement及其子类基本上表示查询的SQL AST .通常，您可以通过在Column或InstrumentedAttribute对象上调用比较运算符来创建子句元素:

If you want to do something more complex, then or_() and and_() take ClauseElement objects as parameters. ClauseElement and its subclasses basically represent the SQL AST of your query. Typically, you create clause elements by invoking a comparison operator on Column or InstrumentedAttribute objects:

# 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对象一样处理代表您的条件的子句元素，将它们放入列表中，将它们组合到其他子句元素中，等等.因此您可以执行以下操作:

You can handle clause elements representing your conditions like any Python objects, put them into lists, compose them into other clause elements, etc. So you can do something like this:

# 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 = ?

这篇关于如何使用"or_"构造稍微复杂一些的过滤器.或"and_"在SQLAlchemy中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！