Format PostgreSQL into JSON

Question

I am reading a PostgreSQL, and would like eventually to convert this into a nice JSON. The output from the PostgreSQL query contains e.g. "Decimal('value')" which I am trying to clean away. Unfortunately the values disappears during this process, and I am not sure how to re-write it so they remain.

Code:

columns = ("parameter", "timestamp", "epoch")
inputRecords = []
for row in cursor.fetchall():
    inputRecords.append(dict(zip(columns,row)))
inputRecordsCleaned = [tuple(str(item) for item in t) for t in inputRecords]
inputRecordsJson = json.dumps(inputRecordsCleaned, indent=2)

Output:

inputRecords = [{'parameter': Decimal('-0.9'), 'timestamp': datetime.datetime(2020, 3, 11, 12, 16, 48), 'epoch': 1583929008}]

inputRecordsCleaned = [('parameter', 'timestamp', 'epoch')]

Expected Output:

inputRecordsCleaned = [('parameter': '-0.9', 'timestamp': '2020, 3, 11, 12, 16, 48', 'epoch': '1583929008')]

Daweo · Accepted Answer · 2020-03-11 15:24:37Z

1

decimal.Decimal can be easily converted to strs. You might deliver str to json.dumps as default that is:

import datetime
import json
from decimal import Decimal
inputRecords = [{'parameter': Decimal('-0.9'), 'timestamp': datetime.datetime(2020, 3, 11, 12, 16, 48), 'epoch': 1583929008}]
records_json = json.dumps(inputRecords, default=str)
print(records_json)

Output:

[{"parameter": "-0.9", "timestamp": "2020-03-11 12:16:48", "epoch": 1583929008}]

answered Mar 11, 2020 at 15:24

Daweo

38.2k3 gold badges17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serge Ballesta · Accepted Answer · 2020-03-11 15:21:17Z

1

I think that the comprehension you need is:

inputRecordsCleaned = [{k: str(v) for k,v in record.items()} for record in inputRecords]

With the given sample, it give:

[{'parameter': '-0.9', 'timestamp': '2020-03-11 12:16:48', 'epoch': '1583929008'}]

answered Mar 11, 2020 at 15:21

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Collectives™ on Stack Overflow

Format PostgreSQL into JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related