获取Aurora数据API以返回JSON

Question

我正在使用aurora数据api从Lambda中的AWS aurora serverless（postgres）获取结果。我使用以下代码格式化结果

with aurora_data_api.connect(aurora_cluster_arn=cluster_arn, secret_arn=secret_arn, database="redwave_db_staging") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT d.uuid, d.name, dl.id, dl.temp FROM devices d LEFT JOIN device_log dl ON dl.device_uuid=d.uuid")
            fields = [i[0] for i in cursor.description]
            result = [dict(zip(fields,row)) for row in cursor.fetchall()]

这对于简单的（非联接）查询很好，但是当我进行联接时，我不会得到任何嵌套数据。相反，我得到了这样的结果

[
 {'id': 1, 'name': 'device 1', 'temp': '5.0', 'uuid': 'aaaa-bbbb-cccc'},
 {'id': 2, 'name': 'device 1', 'temp': '5.1', 'uuid': 'aaaa-bbbb-cccc'},
 {'id': 3, 'name': 'device 2', 'temp': '6.2', 'uuid': 'bbbb-cccc-dddd'}
]

我宁愿有这样的结果

[
 {
   'uuid': 'aaaa-bbbb-cccc' 
   'name': 'device 1', 
   'info': [
     {'id': 1, 'temp': '5.0'},
     {'id': 2, 'temp': '5.1'}
    ],
 },
 {
   'uuid': 'bbbb-cccc-dddd' 
   'name': 'device 2', 
   'info': [
     {'id': 3, 'temp': '6.0'}
    ],
 }
]

有人能帮助我嵌套JSON数据吗？提前谢谢

Answer 1

这并不完全是你所要求的——而是一个更有意义的数据结构

import pprint

d1 = [
    {'id': 1, 'name': 'device 1', 'temp': '5.0', 'uuid': 'aaaa-bbbb-cccc'},
    {'id': 2, 'name': 'device 1', 'temp': '5.1', 'uuid': 'aaaa-bbbb-cccc'},
    {'id': 3, 'name': 'device 2', 'temp': '6.2', 'uuid': 'bbbb-cccc-dddd'}
]
d2 = {}
for e in d1:
    if e['uuid'] not in d2:
        d2[e['uuid']] = {'name': e['name'], 'info': [{'id': e['id'], 'temp': e['temp']}]}
    else:
        d2[e['uuid']]['info'].append({'id': e['id'], 'temp': e['temp']})
pprint.pprint(d2)

输出

{'aaaa-bbbb-cccc': {'info': [{'id': 1, 'temp': '5.0'},
                             {'id': 2, 'temp': '5.1'}],
                    'name': 'device 1'},
 'bbbb-cccc-dddd': {'info': [{'id': 3, 'temp': '6.2'}], 'name': 'device 2'}}

如果你想要这份名单

lst = []
for k, v in d2.items():
    lst.append({'uuid': k})
    lst[-1].update(v)
pprint.pprint(lst)

输出

[{'info': [{'id': 1, 'temp': '5.0'}, {'id': 2, 'temp': '5.1'}],
  'name': 'device 1',
  'uuid': 'aaaa-bbbb-cccc'},
 {'info': [{'id': 3, 'temp': '6.2'}],
  'name': 'device 2',
  'uuid': 'bbbb-cccc-dddd'}]