python - 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 psycopg2 from psycopg2.extras import RealDictCursor  ps_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 python3  import psycopg2  if __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 python3  import psycopg2  if __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)) 

If you want to have a named tuple obj from db query you can use the following snippet:

from collections import namedtuple  def create_record(obj, fields):     ''' given obj from db returns named tuple with fields mapped to values '''     Record = namedtuple("Record", fields)     mappings = dict(zip(fields, obj))     return Record(**mappings)  cur.execute("Select * FROM people") colnames = [desc[0] for desc in cur.description] rows = cur.fetchall() cur.close() result = [] for row in rows:     result.append(create_record(row, colnames)) 

This allows you to access record values as if they were class properties i.e.

record.id, record.other_table_column_name, etc.

or even shorter

from psycopg2.extras import NamedTupleCursor with cursor(cursor_factory=NamedTupleCursor) as cur:    cur.execute("Select * ...")    return cur.fetchall() 

If you're looking to get a pandas data frame with column headers already associated, try this:

import psycopg2, pandas  con=psycopg2.connect(     dbname=DBNAME,      host=HOST,      port=PORT,      user=USER,      password=PASSWORD )  sql = """ select * from x """  d = pandas.read_sql_query(sql,con)  con.close()  print(type(d))  print(pandas.DataFrame.head(d))