Create comma seperated string from psycopg2 fetchall function output

Using python psycopg2, I have the following code:

conn = psycopg2.connect(host=endpoint ,database=database, user=user, password=password)
cur = conn.cursor()
cur.execute("select * from pg_stat_activity;")
records = cur.fetchall()
for record in records:
    print(record)

that code dumps out records that looks like:

(16391, 'app', 9233, 16389, 'app', '', '10.20.12.17', None, 55204, datetime.datetime(2020, 1, 14, 1, 43, 19, 214161, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), datetime.datetime(2020, 1, 14, 1, 43, 19, 410905, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), datetime.datetime(2020, 1, 14, 1, 43, 19, 442050, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), datetime.datetime(2020, 1, 14, 1, 43, 19, 442051, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), None, None, 'active', None, '22332', 'select * from pg_stat_activity;')
...
...

it displays function calls like:

datetime.datetime(2020, 1, 14, 1, 43, 19, 214161, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))

however, what I really want is for it to display the string output of the function like:

2019-12-20 01:59:33.270944+00

How can I get psycopg2 to display this for me?

Thanks.

1 answer

  • answered 2020-01-14 02:42 Barmar

    Use datetime.strftime() to format the datetime element.

    for record in records:
        l = list(record[0:9])
        # format times in elements 9-12
        for i in range(9, 13):
            l.append(record[i].strftime('%Y-%m-%d %H:%M:%S.%f%z')
        l += record[13:]
        print(l)