CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Aharon
Aharon

Reputation: 25

How to print query results in Python including column names

When printing PostgreSQL query results I only see the result values, I would like to see the column name along with the result values 

 postgreSQL_select_Query = "SELECT epic,timestamp FROM market_data_historic s1 WHERE timestamp = (SELECT MAX(timestamp) FROM market_data_historic s2 WHERE s1.epic = s2.epic)"
    cursor.execute(postgreSQL_select_Query)
    # Close the connection
    result=(cursor.fetchall())
    for row in result:
        print (row)

This is the result I get: 

('CC.D.LCO.USS.IP', datetime.datetime(2019, 11, 13, 22, 0))
('IX.D.DAX.DAILY.IP', datetime.datetime(2019, 7, 23, 4, 0))
('KB.D.ELECTY.DAILY.IP', datetime.datetime(2020, 1, 24, 16, 0))
('CS.D.ALUMINIUM.TODAY.IP', datetime.datetime(2019, 7, 23, 1, 0))
('CS.D.NZDCAD.TODAY.IP', datetime.datetime(2020, 1, 24, 21, 0))
('CS.D.CADCNH.TODAY.IP', datetime.datetime(2020, 1, 16, 8, 0))

How can I get it to be like this: 

(epic:'CC.D.LCO.USS.IP',timestamp: datetime.datetime(2019, 11, 13, 22, 0))
(epic:'IX.D.DAX.DAILY.IP',timestamp: datetime.datetime(2019, 7, 23, 4, 0))
(epic:'KB.D.ELECTY.DAILY.IP',timestamp: datetime.datetime(2020, 1, 24, 16, 0))
(epic:'CS.D.ALUMINIUM.TODAY.IP',timestamp: datetime.datetime(2019, 7, 23, 1, 0))
(epic:'CS.D.NZDCAD.TODAY.IP',timestamp: datetime.datetime(2020, 1, 24, 21, 0))
(epic:'CS.D.CADCNH.TODAY.IP',timestamp: datetime.datetime(2020, 1, 16, 8, 0))

Upvotes: 2

Views: 2475

Answers (2)

klin
klin

Reputation: 121604

Use the cursor.description attribute to retrieve column names and convert results to dictionaries:

result = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
for row in result:
    print (dict(zip(columns, row)))

Alternatively, you can use Real dictionary cursor or Named tuple cursor.

See also DictCursor vs RealDictCursor.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246463

Try the description attribute of cursor:

Read-only attribute describing the result of a query. It is a sequence of Column instances, each one describing one result column in order. The attribute is None for operations that do not return rows or if the cursor has not had an operation invoked via the execute*() methods yet.

For compatibility with the DB-API, every object can be unpacked as a 7-items sequence: the attributes retuned this way are the following. For further details and other attributes available check the Column documentation.

  • name: the name of the column returned.
  • type_code: the PostgreSQL OID of the column.
  • display_size: the actual length of the column in bytes.
  • internal_size: the size in bytes of the column associated to this column on the server.
  • precision: total number of significant digits in columns of type NUMERIC. None for other types.
  • scale: count of decimal digits in the fractional part in columns of type NUMERIC. None for other types.
  • null_ok: always None as not easy to retrieve from the libpq.

Upvotes: 0

Related Questions