时间戳

首页 > 解决方案 > python在处理来自数据库的响应时添加额外的引号（将json转换为字符串）

python - python在处理来自数据库的响应时添加额外的引号（将json转换为字符串）

问题描述

我有对 postgresql 数据库的 sql 查询包括这一行

..(json_agg(json_build_object('id', activities.id, 'name', activities.name))::json->0) AS activity ..

这将结果返回为

'activity': '{"id" : 1, "name" : "basketball"}'

那么活动密钥ID字符串的值，如何获得这种格式 'activity': {"id" : 1, "name" : "basketball"} ？

psql (PostgreSQL) 13.3 (Ubuntu 13.3-1.pgdg18.04+1)

ps 完整查询

SELECT events.id, events.title, events.creator, events.content, events.status, \
           (jsonb_agg(json_build_object('city', locations.city, 'street', locations.street, \
           'building', locations.building, 'lat', locations.lat, 'long', locations.long))::json -> 0)AS location ,\
           (jsonb_agg(jsonb_build_object('id', activities.id, 'name', activities.name))::json -> 0) AS activity \
           FROM events  JOIN locations  ON events.location_id=locations.id \
           JOIN activities  ON events.activities_id=activities.id \
           WHERE events.id = 2 GROUP BY events.id

这还给我

   result =  <databases.backends.postgres.Record object at 0x7fcf16399290>

在我将它转换为 python dict 对象之后

 dict(result)

并得到

{'id': 2, 'title': 'Test', 'creator': 1, 'content': 'Test Content', 'status': 'OPEN', 'location': '{"lat": 53.8498155, "city": "Minsk", "long": 27.4760262, "street": "Lenina ", "building": "1"}', 'activity': '{"id": 1, "name": "basketball"}'}

标签： pythonpostgresql

解决方案

推荐阅读