How to get list of column names from psycopg2 cursor?

Question

I need a general way to create column labels directly from selected column names and a reminder that the python module psycopg2 supports this function.

Answer 1

From "Python Programming" by Mark Lutz:

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

Answer 2

Another thing you can do is create a cursor with which you can link to your columns by their names (which led me to this page first):

 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 

Answer 3

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 the column names in the same query as the data rows , you can use the cursor description field:

 #!/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)) 

Answer 4

If you want namedtuple obj from a db request, you can use the following snippet:

 from collections import namedtuple def create_record(obj, fields): ''' given obj from db returns namedtuple 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 evaluate the values ​​of the records as if they were properties of the ie class

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() 

Answer 5

After executing the SQL query, write the following python script written in 2.7

 total_fields = len(cursor.description) fields_names = [i[0] for i in cursor.description Print fields_names 

Answer 6

I noticed that you should use cursor.fetchone() after the query to get a list of columns in cursor.description (ie in [desc[0] for desc in curs.description] )

Answer 7

I also came across a similar problem. I am using a simple trick to solve this problem. Suppose you have column names in a list, e.g.

 col_name = ['a', 'b', 'c'] 

Then you can do the following

 for row in cursor.fetchone(): print zip(col_name, row) 

Answer 8

 #!/usr/bin/python import psycopg2 #note that we have to import the Psycopg2 extras library! import psycopg2.extras import sys def main(): conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'" # print the connection string we will use to connect print "Connecting to database\n ->%s" % (conn_string) # get a connection, if a connect cannot be made an exception will be raised here conn = psycopg2.connect(conn_string) # conn.cursor will return a cursor object, you can use this query to perform queries # note that in this example we pass a cursor_factory argument that will # dictionary cursor so COLUMNS will be returned as a dictionary so we # can access columns by their name instead of index. cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # tell postgres to use more work memory work_mem = 2048 # by passing a tuple as the 2nd argument to the execution function our # %s string variable will get replaced with the order of variables in # the list. In this case there is only 1 variable. # Note that in python you specify a tuple with one item in it by placing # a comma after the first variable and surrounding it in parentheses. cursor.execute('SET work_mem TO %s', (work_mem,)) # Then we get the work memory we just set -> we know we only want the # first ROW so we call fetchone. # then we use bracket access to get the FIRST value. # Note that even though we've returned the columns by name we can still # access columns by numeric index as well - which is really nice. cursor.execute('SHOW work_mem') # Call fetchone - which will fetch the first row returned from the # database. memory = cursor.fetchone() # access the column by numeric index: # even though we enabled columns by name I'm showing you this to # show that you can still access columns by index and iterate over them. print "Value: ", memory[0] # print the entire row print "Row: ", memory if __name__ == "__main__": main()