如何在sqlalchemy中使用or或and构造稍微复杂一点的过滤器

Question

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

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

如何以编程方式遍历术语列表并从术语列表中构造“条件”，以便使用筛选器和/或和进行查询？

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

Answer 1

假设您的terms变量包含有效的SQL语句片段，您可以简单地将前面带星号的terms传递给or_或and_：

>>> from sqlalchemy.sql import and_, or_
>>> terms = ["name='spam'", "email='spam@eggs.com'"]
>>> print or_(*terms)
name='spam' OR email='spam@eggs.com'
>>> print and_(*terms)
name='spam' AND email='spam@eggs.com'

注意，这假设terms只包含有效的和正确转义的SQL片段，因此如果恶意用户可以以某种方式访问terms，这可能是不安全的。

与其自己构建SQL片段，不如让SQLAlchemy使用sqlalchemy.sql中的其他方法构建参数化SQL查询。我不知道您是否为表准备了Table对象；如果准备了，假设您有一个名为users的变量，它是Table的实例，它描述了数据库中的users表。然后您可以执行以下操作：

from sqlalchemy.sql import select, or_, and_
terms = [users.c.name == 'spam', users.c.email == 'spam@eggs.com']
query = select([users], and_(*terms))
for row in conn.execute(query):
    # do whatever you want here

在这里，users.c.name == 'spam'将创建一个sqlalchemy.sql.expression._BinaryExpression对象，该对象记录这是users表的name列与包含spam的字符串文本之间的二进制等式关系。当您将此对象转换为字符串时，您将得到一个类似users.name = :1的SQL片段，其中:1是参数的占位符。_BinaryExpression对象还记得:1到'spam'的绑定，但在执行SQL查询之前不会插入它。插入时，数据库引擎将确保正确转义。建议阅读：SQLAlchemy's operator paradigm

如果只有数据库表，但没有描述该表的users变量，则可以自己创建它：

from sqlalchemy import Table, MetaData, Column, String, Boolean
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('email', String),
    Column('active', Integer)
)

或者，您可以使用autoloading查询数据库引擎以获得数据库的结构，并自动构建users；显然这更耗时：

users = Table('users', metadata, autoload=True)

Answer 2

如果您有一个术语列表，并且希望查找字段与其中一个匹配的行，则可以使用in_u（）方法：

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

如果您想做更复杂的事情，那么or_()和and_()将ClauseElement对象作为参数。ClauseElement及其子类基本上表示查询的SQLAST。通常通过对列或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 = ?

Answer 3

我也有同样的问题： SQLAlchemy: an efficient/better select by primary keys?

terms = ['one', 'two', 'three']
clauses = or_( * [Table.field == x for x in terms] )
query = Session.query(Table).filter(clauses)

你喜欢这个吗？