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

Question

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

表结构和数据如下所示

卡斯蒂德 |客户 |发票期限 |到期日 |到期日 |收费|余额 |col8|col9|col10
ABC | 101 | 101 20190801 | 12 | 12有一天 | 2 | 10 |col8|col9|col10
ABC | 101 | 101 20190701 | 13 |有一天 | 3 | 13 |col8|col9|col10
ABC | 101 | 101 20190601 | 10 | 10有一天 | 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/custacct 的所有发票周期列表并附加到主 json 响应3]获取当前/最新发票期间其他属性的值。

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 列表插入到 results 字典中，然后转储到您将得到的 JSON 字符串

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

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