How do I get a list of column names from a psycopg2 cursor?

From "Programming Python" by Mark Lutz:

curs.execute("Select * FROM people LIMIT 0")colnames = [desc[0] for desc in curs.description]

Another thing you can do is to create a cursor with which you will be able to reference your columns by their names (that's a need which led me to this page in the first place):

import psycopg2from psycopg2.extras import RealDictCursorps_conn = psycopg2.connect(...)ps_cursor = psql_conn.cursor(cursor_factory=RealDictCursor)ps_cursor.execute('select 1 as col_a, 2 as col_b')my_record = ps_cursor.fetchone()print (my_record['col_a'],my_record['col_b'])>> 1, 2

To get the column names in a separate query, you can query the information_schema.columns table.

#!/usr/bin/env python3import psycopg2if __name__ == '__main__':  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'  column_names = []  with psycopg2.connect(DSN) as connection:      with connection.cursor() as cursor:          cursor.execute("select column_name from information_schema.columns where table_schema = 'YOUR_SCHEMA_NAME' and table_name='YOUR_TABLE_NAME'")          column_names = [row[0] for row in cursor]  print("Column names: {}\n".format(column_names))

To get column names in the same query as data rows, you can use the description field of the cursor:

#!/usr/bin/env python3import psycopg2if __name__ == '__main__':  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'  column_names = []  data_rows = []  with psycopg2.connect(DSN) as connection:    with connection.cursor() as cursor:      cursor.execute("select field1, field2, fieldn from table1")      column_names = [desc[0] for desc in cursor.description]      for row in cursor:        data_rows.append(row)  print("Column names: {}\n".format(column_names))