Python psycopg2 postgres select columns including field names

Question

Hi I'd like to get a table from a database, but include the field names so I can use them from column headings in e.g. Pandas where I don't necessarily know all the field names in advance

so if my database looks like

table test1

 a | b | c 
---+---+---
 1 | 2 | 3
 1 | 2 | 3
 1 | 2 | 3
 1 | 2 | 3
 1 | 2 | 3

How can I do a

import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall()
tmp

such that tmp shows

[('a','b','c'),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3)]

Thanks

Answer 1

If what you want is a dataframe with the data from the db table as its values and the dataframe column names being the field names you read in from the db, then this should do what you want:

import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall()

# Extract the column names
col_names = []
for elt in cr.description:
    col_names.append(elt[0])

# Create the dataframe, passing in the list of col_names extracted from the description
df = pd.DataFrame(tmp, columns=col_names)

Answer 2

The column names are available as cr.description[0][0], cr.description[1][0], etc. If you want it in exactly the format you show, you need to do some work to extract it and stick it in front of the result set.

Answer 3

You can use two loop cases, to not use pandas:

temp = []
for x in result:
    temp2 = {}
    c = 0
    for col in cursor.description:
        temp2.update({str(col[0]): x[c]})
        c = c+1
    temp.append(temp2)
print(temp)

This will prints any like this:

[{'column1':'foo1','column2':'foo1'},{'column1':'foo2','column2':'foo2'},...]

I hope this help you! Cheers