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

Question

我正在尝试从一系列词汇中进行非常简单的搜索。

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

我该如何通过编程的方式遍历这些词汇，并根据这些词汇构建条件，以便我可以使用 filter 和 or_ 或 _and 来进行查询呢？

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

Answer 1

我在这个链接中遇到了同样的问题："SQLAlchemy: 如何更高效地通过主键选择数据？"

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

Answer 2

假设你的 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 的操作符范式

如果你只有数据库表，但没有描述这个表的 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)
)

另外，你也可以使用自动加载，这样会查询数据库引擎获取数据库的结构，并自动构建 users；显然，这样会花费更多时间：

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

Answer 3

如果你有一个术语列表，想要找到某个字段与这些术语匹配的行，你可以使用 in_() 方法：

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

如果你想做一些更复杂的事情，可以使用 or_() 和 and_()，它们接受 ClauseElement 对象作为参数。ClauseElement 及其子类基本上代表了你查询的 SQL 抽象语法树。通常，你可以通过在 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 = ?