Python sqlalchemy 过滤多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3332991/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlalchemy filter multiple columns
提问by teggy
How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one column:
如何组合两列并应用过滤器？例如，我想同时在“firstname”和“lastname”列中进行搜索。如果只搜索一列，我是这样做的：
query = meta.Session.query(User).filter(User.firstname.like(searchVar))
回答by gclj5
You can use SQLAlchemy's or_functionto search in more than one column (the underscore is necessary to distinguish it from Python's own or).
您可以使用 SQLAlchemy 的or_函数在多列中进行搜索（下划线是必要的，以区别于 Python 自己的or）。
Here's an example:
下面是一个例子：
from sqlalchemy import or_
query = meta.Session.query(User).filter(or_(User.firstname.like(searchVar),
User.lastname.like(searchVar)))
回答by David Johnstone
You can simply call filtermultiple times:
您可以简单地filter多次调用：
query = meta.Session.query(User).filter(User.firstname.like(searchVar1)). \
filter(User.lastname.like(searchVar2))
回答by Vlad Bezden
There are number of ways to do it:
有多种方法可以做到：
Using filter()(andoperator)
使用filter()(和运算符)
query = meta.Session.query(User).filter(
User.firstname.like(search_var1),
User.lastname.like(search_var2)
)
Using filter_by()(andoperator)
使用filter_by()(和运算符)
query = meta.Session.query(User).filter_by(
firstname.like(search_var1),
lastname.like(search_var2)
)
Chaining filter()or filter_by()(andoperator)
链接filter()or filter_by()( and运算符)
query = meta.Session.query(User).\
filter_by(firstname.like(search_var1)).\
filter_by(lastname.like(search_var2))
Using or_(), and_(), and not()
使用or_(), and_(), 和not()
from sqlalchemy import and_, or_, not_
query = meta.Session.query(User).filter(
and_(
User.firstname.like(search_var1),
User.lastname.like(search_var2)
)
)
回答by Azharullah Shariff
A generic piece of code that will work for multiple columns. This can also be used if there is a need to conditionally implement search functionality in the application.
适用于多列的通用代码。如果需要在应用程序中有条件地实现搜索功能，也可以使用此方法。
search_key = "abc"
search_args = [col.ilike('%%%s%%' % search_key) for col in ['col1', 'col2', 'col3']]
query = Query(table).filter(or_(*search_args))
session.execute(query).fetchall()
Note: the %%are important to skip % formatting the query.
注意：%%跳过 % 格式化查询很重要。
