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='72.20.208.64:21132', 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 - https://code-maven.com/serialize-datetime-object-as-json-in-python 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": "72.20.208.64:21132", "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"}']