使用关键字作为变量的flask sqlalchemy查询

Let's say I have a model like this:

假设我有这样的模型：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hometown = db.Column(db.String(140))
    university = db.Column(db.String(140))

To get a list of users from New York, this is my query:

要获取纽约的用户列表，这是我的查询：

User.query.filter_by(hometown='New York').all()

To get a list of users who go to USC, this is my query:

要获取前往USC的用户列表，这是我的查询：

User.query.filter_by(university='USC').all()

And to get a list of users from New York, and who go to USC, this is my query:

要获得来自纽约的用户列表，以及谁去USC，这是我的查询：

User.query.filter_by(hometown='New York').filter_by(university='USC').all()

Now, I would like to dynamically generate these queries based on the value of a variable.

现在，我想基于变量的值动态生成这些查询。

For example, my variable might look like this:

例如，我的变量可能如下所示：

    {'hometown': 'New York'}

Or like this:

或者像这样：

    {'university': 'USC'}

... Or even like this:

......或者甚至像这样：

    [{'hometown': 'New York'}, {'university': 'USC'}]

Can you help me out with writing a function which takes a dictionary (or list of dictionaries) as an input, and then dynamically builds the correct sqlalchemy query?

你可以帮我编写一个以字典（或字典列表）作为输入的函数，然后动态构建正确的sqlalchemy查询吗？

If I try to use a variable for the keyword, I get this err:

如果我尝试为关键字使用变量，我会得到这个错误：

key = 'university'
User.query.filter_by(key='USC').all()

InvalidRequestError: Entity '<class 'User'>' has no property 'key'

Secondly, I am not sure how to chain multiple filter_by expressions together dynamically.

其次，我不确定如何动态地将多个filter_by表达式链接在一起。

I can explicitly, call out a filter_by expression, but how do I chain several together based on a variable?

我可以明确地调出一个filter_by表达式，但是如何根据变量将几个链接在一起呢？

Hope this makes more sense.

希望这更有意义。

Thanks!

谢谢！

2 个解决方案

#1

SQLAlchemy's filter_by takes keyword arguments:

SQLAlchemy的filter_by接受关键字参数：

filter_by(**kwargs)

filter_by（** kwargs）

In other words, the function will allow you to give it any keyword parameter. This is why you can use any keyword that you want in your code: SQLAlchemy basically sees the arguments a dictionary of values. See the Python tutorial for more information on keyword arguments.

换句话说，该函数将允许您为其提供任何关键字参数。这就是为什么你可以在你的代码中使用你想要的任何关键字的原因：SQLAlchemy基本上将参数视为值的字典。有关关键字参数的更多信息，请参阅Python教程。

So that allows the developers of SQLAlchemy to receive an arbitrary bunch of keyword arguments in a dictionary form. But you're asking for the opposite: can you pass an arbitrary bunch of keyword arguments to a function?

这样，SQLAlchemy的开发人员就可以以字典形式接收任意一组关键字参数。但是你要求的是相反的结果：你可以将一组任意关键字参数传递给函数吗？

It turns out that in Python you can, using a feature called unpacking. Simply create the dictionary of arguments and pass it to the function preceded by **, like so:

事实证明，在Python中，您可以使用称为解包的功能。只需创建参数字典并将其传递给前面带**的函数，如下所示：

kwargs = {'hometown': 'New York', 'university' : 'USC'}
User.query.filter_by(**kwargs)
# This above line is equivalent to saying...
User.query.filter_by(hometown='New York', university='USC')

#2

filter_by(**request.args) doesn't work well if you have non-model query parameters, like page for pagination, otherwise you get errors like these:

如果你有非模型查询参数，例如分页页面，filter_by（** request.args）不能正常工作，否则会出现如下错误：

InvalidRequestError: Entity '<class 'flask_sqlalchemy.MyModelSerializable'>' has no property 'page'

I use something like this which ignores query parameters not in the model:

我使用这样的东西忽略了不在模型中的查询参数：

    builder = MyModel.query
    for key in request.args:
        if hasattr(MyModel, key):
            vals = request.args.getlist(key) # one or many
            builder = builder.filter(getattr(MyModel, key).in_(vals))
    if not 'page' in request.args:
        resources = builder.all()
    else:
        resources = builder.paginate(
            int(request.args['page'])).items

Considering a model with a column called valid, something like this will work:

考虑一个名为valid的列的模型，这样的东西可以工作：

curl -XGET "http://0.0.0.0/mymodel_endpoint?page=1&valid=2&invalid=whatever&valid=1"

invalid will be ignored, and page is available for pagination and best of all, the following SQL will be generated: WHERE mymodel.valid in (1,2)

invalid将被忽略，并且页面可用于分页，最重要的是，将生成以下SQL：WHERE mymodel.valid in（1,2）

(get the above snippet for free if you use this boilerplate-saving module)

（如果您使用此样板文件保存模块，请免费获取上述代码段）

#1