flask sqlAlchemy动态构造查询

时间 2019-02-22
阅读 53
点赞 0
收藏 0
连接kot-pedalniy

我有一个输入json,如下所示:

{
  "page": 2,
  "limit": 10,
  "order": [
    {
      "field": "id",
      "type": "asc"
    },
    {
      "field": "email",
      "type": "desc"
    },
    ...
    {
      "field": "fieldN",
      "type": "desc"
    }
  ],
  "filter": [
      {
        "field": "company_id",
        "type": "=",
        "value": 1
      },
      ...
      {
        "field": "counter",
        "type": ">",
        "value": 5             
      }
  ]
}

如果我不知道字段数,如何根据输入的JSON动态构造SQLAlchemy查询?

像这样:

User.query.filter(filter.field, filter.type, filter.value).filter(filter.field1, filter.type1, filter.value1)...filter(filter.fieldN, filter.typeN, filter.valueN).order_by("id", "ask").order_by("email", "desc").order_by("x1", "y1")....order_by("fieldN"...."desc").all()
建议答案

将JSON转换为字典并检索值。

如果您的JSON在一个文件中(比如,data.json)JSON库将满足您的需求:

import json

f = open("data.json")
data = json.load(f)
f.close()
User.query.filter(company_id=1).order_by(data["id"], data["ask"]).order_by(data["email"], data["desc"]).all()

如果您的JSON是一个字符串(比如,json_data):

import json

data = json.loads(json_data)
User.query.filter(company_id=1).order_by(data["id"], data["ask"]).order_by(data["email"], data["desc"]).all()

如果您的JSON是来自python请求库的请求,即res = requests.get(...)然后res.json()将返回词典:

data = res.json()
User.query.filter(company_id=1).order_by(data["id"], data["ask"]).order_by(data["email"], data["desc"]).all()
👍 0