Construct a big json response from multiple pyodbc database query result

Question

I'm constructing json response from data in database using pyodbc. Some of the fields are direct mapping from table column, whereas some of them have to be in list, dict format.

Table structure and data looks like below

custid | custacct | invoiceperiod | amtdue | duedate | charges | balance |col8|col9|col10
abc | 101 | 20190801 | 12 | somedate | 2 | 10 |col8|col9|col10
abc | 101 | 20190701 | 13 | somedate | 3 | 13 |col8|col9|col10
abc | 101 | 20190601 | 10 | somedate | 5 | 11 |col8|col9|col10

custid='abc'
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

#get all invoiceperiod for custid
l = []
cursor.execute("select invoiceperiod from table where custid=? order by invoiceperiod desc", custid)
rows = cursor.fetchall()
for row in rows:
    l.append(row.invoiceperiod)
print("billingperiod:", l)  

#get other direct mapping fields from DB  
cursor.execute("SELECT col8,col9,col10 FROM table where custid=? and invoiceperiod=(select max(invoiceperiod) from table where custid=?)", custid, custid)

results = []
columns = [column[0] for column in cursor.description]

for row in cursor:
    results.append(dict(zip(columns, row)))
#    results.append("billingperid", l)
print(results)

For given custid ('abc') expected json response should be as below -

{
    "accounts": [{
        "custacct": 101,
        "invoiceperiods": ["20190801", "20190701","20190601"],
        "currentinvoicePeriod": "20190801", 
        "custacctsummary":{
            "amtdue":12,
            "duedate":"somedate",
            "charges":2,
            "balance":10
            },
        "col8":value1,
        "col9":value2,
        "col10":value3
        }]
}

1] How to construct "custacctsummary" json object and append to json response
2] prepare list of all invoiceperiod for given custid/custacct and append to main json response 3] get value for other attributes for current/latest invoiceperiod.

score 1 · Accepted Answer · answered Aug 26 '19 at 12:37

Your code already produces a list of str

print("billingperiod:", l)
# billingperiod: ['20190801', '20190701', '20190601']

and a list containing a single dict

print(results)
# [{'col8': 'value1', 'col9': 'value2', 'col10': 'value3'}]

If you change

results = []
columns = [column[0] for column in cursor.description]

for row in cursor:
    results.append(dict(zip(columns, row)))
#    results.append("billingperid", l)
print(results)

to ...

columns = [column[0] for column in cursor.description]

row = cursor.fetchone()
results = dict(zip(columns, row))
print(results)
# {'col8': 'value1', 'col9': 'value2', 'col10': 'value3'}

... insert the l list into the results dict and then dump to a JSON string you'll get

results['invoiceperiods'] = l
j = json.dumps(results, indent=4);
print(j)
# {
#     "col8": "value1",
#     "col9": "value2",
#     "col10": "value3",
#     "invoiceperiods": [
#         "20190801",
#         "20190701",
#         "20190601"
#     ]
# }

You can use similar logic to build out the rest of your JSON requirements.

Thanks @GordThompson, this gave the direction. When I'm trying to build the json in format below (for e.g custid 'abc' having multiple accounts -101,102). So expected json output as: { accounts:[ { }, //json response for acct 101 as shown above { } // json response for acct 102 as shown above ] } Error I'm getting : TypeError: list indices must be integers or slices, not str — more09, Aug 30 '19 at 04:01
This is what I have in my code to build list of dictionary (under the label accounts: [] ) `dicList = [] dicList.append(results) dicList['accounts'] = dicList print(dicList) j = json.dumps(dicList, indent=4) print(j) ` — more09, Aug 30 '19 at 04:10

Construct a big json response from multiple pyodbc database query result

1 Answers1