时间戳

首页 > 解决方案 > 从多个pyodbc数据库查询结果构造一个大的json响应

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

问题描述

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

表结构和数据如下所示

监护人 | 客户 | 发票期| 到期 | 截止日期 | 收费| 余额 |col8|col9|col10
abc | 101 | 20190801 | 12 | 某天 | 2 | 10 |col8|col9|col10
abc | 101 | 20190701 | 13 | 某天 | 3 | 13 |col8|col9|col10
abc | 101 | 20190601 | 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/custact 的所有 invoiceperiod 列表并附加到主 json 响应 3] 获取当前/最新 invoiceperiod 的其他属性的值。

标签： pythonjsonpyodbc

解决方案

您的代码已经生成了一个列表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 需求。

推荐阅读