Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python psycopg2 postgres select columns including field names

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

like image 275
Tahnoon Pasha Avatar asked Jun 17 '13 08:06

Tahnoon Pasha


3 Answers

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)
like image 58
EdTech Avatar answered Sep 30 '22 06:09

EdTech


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.

like image 27
Peter Eisentraut Avatar answered Sep 30 '22 07:09

Peter Eisentraut


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

like image 21
cdvillagra Avatar answered Sep 30 '22 07:09

cdvillagra