如何使用sqlalchemy中的or_或and_构造一个更复杂的过滤器

问题描述

我试图做一个非常简单的搜索术语列表

pre code> terms = ['term1'，' term2'，'term3']

如何以编程方式查看术语列表并构建条件，以便我可以使用过滤器和or_或_and查询

  eg query.filter（或_（＃从条款构造的东西））
  

解决方案

  terms = ['term1'，'term2'，'term3'] 
 query.filter（Cls.field.in_（terms））
  

如果您想做更复杂的事情，那么或_（）和和_（）以 ClauseElement 对象作为参数。 ClauseElement和它的子类基本上代表了查询的SQL 。通常，通过调用Column或InstrumentedAttribute对象上的比较运算符来创建子句元素：
$ b $ pre $创建子句元素
子句=（users_table.columns ['name'] ==something）
＃你也可以使用简写users_table.c.name

＃该子句是一个二进制表达式...
print（type（clause））
＃< class'sqlalchemy.sql.expression._BinaryExpression'>
＃...比较一个列的相等与一个界限值。
print（type（clause.left），clause.operator，type（clause.right））
＃< class'sqlalchemy.schema.Column'> ;,<内置函数eq> ，
＃< class'sqlalchemy.sql.expression._BindParamClause'>

＃str（）将其编译为SQL
print（str（clause））
＃users.name =？

＃你也可以用ORM属性做
子句=（User.name ==something）
print（str（clause））
＃users .name =？

您可以像处理任何Python对象一样处理代表您的条件的子句元素，将它们放入列表中，其他子句元素等，所以你可以做这样的事情：

$ $ p $ $ $ c $＃收集单独的条件列表

conditions.append（User.name == term）

＃将它们组合到一个BooleanClauseList
condition =或_（*条件）

＃现在可以在查询中使用子句元素作为谓词
query = query.filter（condition）
＃或查看SQL片段
print（str（condition））
＃users.name =？或users.name =？或users.name =？

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

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

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?

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

解决方案

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

If you want to do something more complex, then or_() and and_() take ClauseElement objects as parameters. ClauseElement and it's 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 = ?

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

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