namedtuple to JSON – Python

In pgdb – PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query.
An example of a row from the list of named tuples –

Row(log_time=datetime.datetime(2019, 3, 20, 5, 41, 29, 888000, tzinfo=), user_name='admin', connection_from='', command_tag='INSERT', message='AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);",', application_name='psql')

To convert these named tuple row to a JSON format, the easiest way is using _asdict() which returns a new dictionary mapping the field names to their corresponding values.

for row in scanForDml:
    violation.append(json.dumps(row._asdict(), default=jsondatetimeconverter))

Converting the datatime.datetime into JSON format throws a TypeError. To avoid this situation, you can use the below function –

Reference -
def jsondatetimeconverter(o):
    """To avoid TypeError: datetime.datetime(...) is not JSON serializable"""
    if isinstance(o, datetime.datetime):
        return o.__str__()

Final converted JSON data in list –

['{"log_time": "2019-03-20 05:41:29.888000+00:00", "user_name": "admin", "connection_from": "", "command_tag": "INSERT", "message": "AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,\\"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);\\",", "application_name": "psql"}']

Leave a Reply