将JSON对象转换为可用的sqlalchemy过滤器语句

From the flask component I have request that I call:

我要求从烧瓶组件中调用:

request_json = request.get_json()

This is a JSON object containing the following:

这是一个JSON对象，包含以下内容:

{'filter1' : '123', 'filter2' : '456', 'filter3' : '789' }

Also these filters can vary in size depending on the front end user. It could be just one filter or multiple.

此外，这些过滤器的大小可以根据前端用户的不同而有所不同。它可以是一个过滤器或多个。

I am wondering how you would convert an object such as this to a query that would be usable? I believe or_() and and_() is what I need to use to build the filter. Is it possible to do something such as...

我想知道如何将这样的对象转换成可用的查询?我相信or_()和and_()是我构建过滤器所需要的。有可能做某事，如……

query.filter_by(and_(*request_json))

I am very new to the entire tech stack...any help would be appreciated!

我对整个科技行业都很陌生……如有任何帮助，我们将不胜感激!

1 个解决方案

#1

filter_by takes keyword arguments to perform basic equality filters. Remove the and_ and splat the dict directly into filter_by.

filter_by接受关键字参数来执行基本的平等过滤器。删除and_并将该命令直接插入filter_by中。

query.filter_by(**request_json)

This does no validation on the keys or values, however. So if someone passed in 'fish': 3 and there was no "fish" column, or passed in 'column4': 'abc' and column4 is actually an array type, you would get an error.

但是，这对键或值没有验证。因此，如果有人传入了“fish”:3，并且没有“fish”列，或者传入了“column4”:“abc”，而column4实际上是一个数组类型，那么您将会得到一个错误。

So it's probably safer and more straightforward to validate and do the filtering manually.

所以手工验证和过滤可能更安全更简单。

query = MyModel.query

if 'column1' in data:
    try:
        value = int(data['column1'])
    except (TypeError, ValueError):
        pass
    else:
        query = query.filter(MyModel.column1 == value)

if 'column2' in data:
    try:
        value = datetime.utcfromtimestamp(int(data['column2']))
    except (TypeError, ValueError):
        pass
    else:
        query = query.filter(MyModel.column2 >= value)

# etc.

return query

#1