如何在python中解析内部连接

Question

假设我有两张桌子：蛋糕和花花公子

json中的一行蛋糕如下所示：

{
    "id" : 0,
    "name" : "Carrot cake",
    "slices" : 8,
}

一排穿着json的家伙看起来像这样：

{
    "id" : 0,
    "name" : "Ahsoka"
}

我想记录下每个吃蛋糕的人。 例如，json结果是：

{
    "id" : 0,
    "name" : "Carrot Cake",
    "slices" : 8,
    "eaten_by" : {
        {
            "id" : 0,
            "name" : "Ahsoka"
        },
        {
            "id" : 3,
            "name" : "CT-7567 Rex"
        },
        {
            "id" : 9,
            "name" : "CC-2224 Cody"
        },
    }
}

为此，我创建了一个cake_eaters表。当有人吃一块蛋糕时，我会在这张桌子上加一张记录

现在，为了得到上面的结果，我打这个电话：

sql_get_cake_eaters = ''' SELECT * FROM cakes
    INNER JOIN cake_eaters ON cakes.id = cake_eaters.cake_id
    INNER JOIN dudes ON cake_eaters.dude_id = dudes.id '''

但是，我得到的结果是：

{
    "id" : 0,
    "name" : "Carrot cake",
    "slices" : 8,
    "dude_id" : 0,
    "name" : "Ahsoka"
},
{
    "id" : 0,
    "name" : "Carrot cake",
    "slices" : 8,
    "dude_id" : 3,
    "name" : "CT-7567 Rex"
}

我是这样分析的：

query = db_connect.execute(sql_get_cake_eaters)
result =     {
                 'tasks': [
                     dict(zip(tuple(query.keys()), i))
                     for i in query.cursor
                 ]
             }
return (jsonify(result))

我怎样才能像我的例子那样解析返回结果呢

Answer 1

我不知道你怎么能有两个相同的键。蛋糕名和花花公子名在你的结果中的一个名为“name”的键下。那需要修理。更改dude name的查询键可以通过将SQL查询更改为SELECT *, dudes.name AS dude_name FROM ...来完成，只要查询结果的格式如下：

result = [
    {
        "id": 0,
        "name": "Carrot cake",
        "slices": 8,
        "dude_id": 0,
        "dude_name": "Ahsoka"
    },
    {
        "id": 0,
        "name": "Carrot cake",
        "slices": 8,
        "dude_id": 3,
        "dude_name": "CT-7567 Rex"
    }
]

您可以简单地执行以下操作：

cakes = {}
for row in result:
    cake = cakes.get(row["id"])
    if cake is None:
        cake = cakes[row["id"]] = {"id": row["id"], "name": row["name"], "slices": row["slices"], "eaten_by": []}
    cake["eaten_by"].append({"id": row["dude_id"], "name": row["dude_name"]})
jsonify(tuple(cakes.values()))

结果如下：

[{
    "id": 0,
    "name": "Carrot cake",
    "slices": 8,
    "eaten_by":
        [{"id": 0, "name": "Ahsoka"},
       {"id": 3, "name": "CT-7567 Rex"}]
  }]