CODEWITHSUNDEEP
pythonjsondjango
shalin
shalin

Reputation: 373

create nested json from the ouput of two queries django

My code snippet looks like this:

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,
                               
                          })

I want to create json object as below from the above function:

[{
"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"
    }]
}]

Can anyone suggest does it possible, if so then how?

Upvotes: 1

Views: 1910

Answers (1)

Serjik
Serjik

Reputation: 10941

I think of 2 solutions for this question:

  1. Using django-rest-framework nested serializers
  2. The following provided sample:

First you should read myapp_deals and iterate among the records and add correspond milestone to each record and the dumps the whole list

I've provide a sample with sqlite and my tables called header and detail as simplicity.

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))

result:

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

Upvotes: 1

Related Questions