从多个pyodbc数据库查询结果构造一个大json响应

Question

我正在使用pyodbc从数据库中的数据构造json响应。有些字段是表列的直接映射，而有些字段必须是list、dict格式

表结构和数据如下所示

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

对于给定的custid（'abc'），预期的json响应应如下-

{
    "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]如何构造“custacctsummary”json对象并追加到json响应
2] 为给定的custid/custact准备所有invoiceperiod的列表并附加到主json响应 3] 获取当前/最新invoiceperiod的其他属性的值

Answer 1

您的代码已经生成了一个str列表

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

以及一个包含单个dict

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

如果你改变了

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)

到

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

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

。。。将l列表插入resultsdict，然后转储到您将得到的JSON字符串

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

您可以使用类似的逻辑来构建其余的JSON需求