我正在使用 Postgres 数据库,其中包含多个表。目标是从获得的查询结果中检索格式化的 JSON 数据。我创建了这个 Python 脚本来从表(测试用例)中获取数据集,以便操作查询结果:
import psycopg2
import json
from time import sleep
from config import config
def main():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("select * from location")
row = cur.fetchone()
while row is not None:
print(row)
#do field rename, merge(log, lat) and obtained JSON here
sleep(0.3)
row = cur.fetchone()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
main()
为了让我的问题更加明确,我在此提供一个简化的场景,用三个如下所示的表格来表示所面临的任务。
environment
╔════════╦═══════════╦══════╦══════╗
║ env_id ║ placem_id ║ humd ║ wind ║
╠════════╬═══════════╬══════╬══════╣
║ 104║ 4 ║ 48 ║ 119 ║
║ 68 ║ 9 ║ 39 ║ 141 ║
╚════════╩═══════════╩══════╩══════╝
placement
╔═══════════╦════════╦═══════════════╦══════════════════════════╗
║ placem_id ║ loc_id ║ description ║ date ║
╠═══════════╬════════╬═══════════════╬══════════════════════════╣
║ 4 ║ 64 ║ description_1 ║ 2019-03-12T20:40:35.967Z ║
║ 7 ║ 5 ║ description_2 ║ 2019-03-12T20:56:51.319Z ║
╚═══════════╩════════╩═══════════════╩══════════════════════════╝
location
╔════════╦═══════════╦═══════════╦════════════════════╗
║ loc_id ║ log ║ lat ║ address ║
╠════════╬═══════════╬═══════════╬════════════════════╣
║ 64 ║ 13.3986 ║ 52.5547 ║ Bosebrucke Einkauf ║
║ 71 ║ 21.150122 ║ -6.607044 ║ Charlotte Court ║
╚════════╩═══════════╩═══════════╩════════════════════╝
以下是我想要实现的目标:
- 从数据库中检索记录
- 将某些字段根据需要重命名为JSON名称/值(例如,将
humd
更改为relativeHumidity
,并将wind
更改为windSpeed
- 将
log
和lat
字段合并为单个JSON值,例如coordinate[log,lat]
以便返回的JSON格式如下:
{
"relativeHumidity": 48,
"windSpeed": 119,
"address": "Bosebrucke Einkauf",
"location": {
"coordinates": [13.3986, 52.5547]
}
}
虽然这个问题看起来可能重复,但是我尝试了许多类似问题中指出的建议,比如这里的建议,但是这些并没有真正起作用。
有人可以提供一个指南吗?
json_agg
- https://dbfiddle.uk/?rdbms=postgres_11&fiddle=37fd1086fb4fac53337b093969572431 - Roman Pekar