Python:将PostgreSQL查询结果检索为格式化的JSON值 [英] Python: Retrieving PostgreSQL query results as formatted JSON values
问题描述
我正在使用包含几个表的postgres数据库.目的是从获得的查询结果中检索格式化的JSON.我创建了这个从表(测试用例)中获取数据集的python脚本,以便操纵查询结果:
I am working with a postgres database containing several tables. The goal is to retrieve a formatted JSON from query results obtained. I created this python script fetching dataset from table(test case) so as to manipulate the query results:
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()
为使我的问题更清楚,我在这里生成一个简化的场景,该场景用下面的3个表表示了手头的任务.
To make my question clear, I produce here a simplified scenario representing the task at hand with 3 tables as shown below.
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 ║
╚════════╩═══════════╩═══════════╩════════════════════╝
这就是我想要实现的目标:
Here's what I would want to achieve:
- 从数据库中检索记录
- 将JSON名称/值中的某些字段适当地重命名(例如,
humd
变为relativeHumidity
,wind
变为windSpeed
- 将
log
和lat
字段合并为单个JSON值,例如coordinate[log,lat]
- retrieve records from database
- rename some of the field as appropriate in the JSON name/value (e.g.
humd
to becomerelativeHumidity
andwind
towindSpeed
- merge
log
andlat
fields to single JSON value likecoordinate[log,lat]
这样返回的JSON格式为:
So that the JSON returned is in the form:
{
"relativeHumidity": 48,
"windSpeed": 119,
"address": "Bosebrucke Einkauf",
"location": {
"coordinates": [13.3986, 52.5547]
}
}
尽管这个问题似乎是重复的,但是,我尝试了许多类似问题中指出的建议,例如一个
While this question might seems a duplicate, however, I tried many suggestions pointed in similar questions such as the one here but none of these actually work.
外面有人可以提供指南吗?
Can anyone out there offer a guide please?
推荐答案
I think it should be relatively easy to do with functions-json
:
select
json_agg(to_json(d))
from (
select
e.humd as "relativeHumidity",
e.wind as "windSpeed",
l.address,
json_build_object(
'coordinates',
json_build_array(l.log, l.lat)
) as location
from environment as e
inner join placement as p on
p.placem_id = e.placem_id
inner join location as l on
l.loc_id = p.loc_id
) as d
这篇关于Python:将PostgreSQL查询结果检索为格式化的JSON值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!