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
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)
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With