python - 从两个查询 django 的输出创建嵌套 json

Question

我的代码片段如下所示:

views.py

def get_milestonebased_deals(request):

           cursor = connection.cursor()
           cursor.execute("SELECT id,deal_title, milestone_order_id  from myapp_milestone")
           row = cursor.fetchall()
           cursor2=connection.cursor()
           cursor2.execute("select id, title, deal_milestone_id created from myapp_deals")
           row2=cursor2.fetchall()
           data_dict = ValuesQuerySetToDict(row)
           data_json = json.dumps(data_dict)
           data_dict2 = ValuesQuerySetToDict(row2)
           data_jsonn = json.dumps([data_dict2])
           return json_response({
                               'status':data_json,
                               
                          })

我想从上面的函数创建如下 json 对象:

[{
"id":"1",
"title":"Lead",
"milestone":[{
    "id":"1",
    "deal_title":"Staff",
    "created":"date"
    },
    {
    "id":"2",
    "deal_title":"Staff2",
    "created":"date"
    },
    {"id":"1",
    "deal_title":"Staff3",
    "created":"date"
    }]
},
{"id":"2",
"title":"Lead2",
"milestone":[{
    "id":"1",
    "deal_title":"employee",
    "created":"date"
    },
    {
    "id":"2",
    "deal_title":"employee3",
    "created":"date"
    }]
}]

任何人都可以建议这是否可能，如果可以的话怎么办？

Answer 1

对于这个问题我想到了两种解决方案:

1. Using django-rest-framework nested serializers
2. 以下提供的示例:

首先，您应该读取 myapp_deals 并在记录中进行迭代，并向每个记录添加相应的里程碑，然后转储整个列表

为简单起见，我提供了一个包含 sqlite 的示例以及名为 header 和 detail 的表格。

It's written in simple python but you can use code in your django view

import sqlite3
import json

def dict_factory(cursor, row):
    '''
    Provides DictCursor for sqlite
    '''
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn = sqlite3.connect('db.sqlite')
conn.row_factory = dict_factory
cur = conn.cursor()
cur.execute('''SELECT * FROM HEADER''')
headers_list = list(cur.fetchall())
for header in headers_list:    
    cur.execute('SELECT * FROM DETAIL WHERE HEADER_ID=' + str(header['id']))
    row = cur.fetchone()
    if row is not None:
       header['details'] = row
cur.close()

print(json.dumps(headers_list))

结果:

[{"id": 1, "details": {"header_id": 1, "id": 1, "des": "des1"}, "name": "name1"}
, {"id": 2, "details": {"header_id": 2, "id": 3, "des": "des3"}, "name": "name2"
}]