sqlalchemy 动态过滤 [英] sqlalchemy dynamic filtering
问题描述
我正在尝试使用 SQLAlchemy ORM 实现动态过滤.
I'm trying to implement dynamic filtering using SQLAlchemy ORM.
我浏览了 StackOverflow 并发现了非常相似的问题:SQLALchemy dynamic filter_by
I was looking through StackOverflow and found very similar question:SQLALchemy dynamic filter_by
这对我有用,但还不够.
It's useful for me, but not enough.
所以,这是一些代码示例,我正在尝试编写:
So, here is some example of code, I'm trying to write:
# engine - MySQL engine
session_maker = sessionmaker(bind=engine)
session = session_maker()
# my custom model
model = User
def get_query(session, filters):
if type(filters) == tuple:
query = session.query(model).filter(*filters)
elif type(filters) == dict:
query = session.query(model).filter(**filters)
return query
然后我试图用非常相似的东西重用它:
then I'm trying to reuse it with something very similar:
filters = (User.name == 'Johny')
get_query(s, filters) # it works just fine
filters = {'name': 'Johny'}
get_query(s, filters)
第二次运行后,有一些问题:
After the second run, there are some issues:
TypeError: filter() got an unexpected keyword argument 'name'
当我尝试将我的 filters
更改为:
When I'm trying to change my filters
to:
filters = {User.name: 'Johny'}
它返回:
TypeError: filter() keywords must be strings
但它适用于手动查询:
s.query(User).filter(User.name == 'Johny')
我的过滤器有什么问题?
What is wrong with my filters?
顺便说一句,看起来它适用于案例:
BTW, it looks like it works fine for case:
filters = {'name':'Johny'}
s.query(User).filter_by(**filters)
但是按照上述帖子的建议,我正在尝试仅使用 filter
.
But following the recommendations from mentioned post I'm trying to use just filter
.
如果只能用filter_by
代替filter
,这两种方法有什么区别吗?
If it's just one possible to use filter_by
instead of filter
, is there any differences between these two methods?
推荐答案
您的问题是 filter_by 采用关键字参数,而 filter 采用表达式.因此,为 filter_by **mydict 扩展 dict 将起作用.对于过滤器,您通常会向它传递一个参数,该参数恰好是一个表达式.因此,当您将 **filters dict 扩展为 filter 时,您传递了 filter 一堆它不理解的关键字参数.
Your problem is that filter_by takes keyword arguments, but filter takes expressions. So expanding a dict for filter_by **mydict will work. With filter, you normally pass it one argument, which happens to be an expression. So when you expand your **filters dict to filter, you pass filter a bunch of keyword arguments that it doesn't understand.
如果您想从存储的过滤器参数的字典中构建一组过滤器,您可以使用查询的生成特性来继续应用过滤器.例如:
If you want to build up a set of filters from a dict of stored filter args, you can use the generative nature of the query to keep applying filters. For example:
# assuming a model class, User, with attributes, name_last, name_first
my_filters = {'name_last':'Duncan', 'name_first':'Iain'}
query = session.query(User)
for attr,value in my_filters.iteritems():
query = query.filter( getattr(User,attr)==value )
# now we can run the query
results = query.all()
上述模式的伟大之处在于您可以在多个连接的列中使用它,您可以使用 and_ 和 or_ 构造 'ands' 和 'ors',您可以进行 <= 或日期比较,等等.它比使用带有关键字的 filter_by 灵活得多.唯一需要注意的是,对于连接,您必须小心一点,不要意外地尝试连接表两次,并且您可能必须为复杂过滤指定连接条件.我在一个非常复杂的域模型上使用它来进行一些非常复杂的过滤,它的作用就像一个魅力,我只是保持一个 entity_joined 的字典来跟踪连接.
The great thing about the above pattern is you can use it across multiple joined columns, you can construct 'ands' and 'ors' with and_ and or_, you can do <= or date comparisons, whatever. It's much more flexible than using filter_by with keywords. The only caveat is that for joins you have to be a bit careful you don't accidentally try to join a table twice, and you might have to specify the join condition for complex filtering. I use this in some very complex filtering over a pretty involved domain model and it works like a charm, I just keep a dict going of entities_joined to keep track of the joins.
这篇关于sqlalchemy 动态过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!