sqlalchemy动态过滤 [英] sqlalchemy dynamic filtering

问题描述

我正在尝试使用SQLAlchemy ORM来实现动态过滤。

I'm trying to implement dynamic filtering using SQLAlchemy ORM.

我正在检查StackOverflow并发现了非常类似的问题：SQLALchemy动态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 = {User.name: 'Johny'}

它返回：

TypeError: filter() keywords must be strings

但是对于手动方法来说效果很好查询：

But it works fine for manual querying:

s.query(User).filter(User.name == 'Johny')

我的过滤器出了什么问题？

What is wrong with my filters?

BTW，它看起来像

filters = {'name':'Johny'}
s.query(User).filter_by(**filters)

但是按照提到的帖子的建议，我尝试使用绝对t 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扩展字典即可。使用filter时，通常会向其传递一个参数，该参数恰好是一个表达式。因此，当您扩展** filters字典以进行过滤时，您通过了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.

如果要根据存储的过滤器arg的字典构建一组过滤器，则可以使用查询的生成性质来继续应用过滤器。例如：

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'和'or'，可以做到< =或日期比较，等等。这比使用带有关键字的filter_by更加灵活。唯一的警告是，对于联接您必须要小心一点，不要意外地尝试两次联接一个表，并且可能必须指定复杂过滤的联接条件。我在一个相当复杂的域模型上进行了一些非常复杂的过滤，并使用了它，就像它的魅力一样，我只是保留了一个entities_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屋！