How to rename column names on select query of postgres from python?

Question

I am trying to fetch data from postgres server in python and while on the database the columns are well names such as 'id', 'name', 'email' etc. but when I pull those columns in python, I get column names as 0, 1, 2 etc.

This is my python code:

import psycopg2
import psycopg2.extras

DB_HOST = "xx.xxx.xxx.xxx"
DB_NAME = "my_db"
DB_USER = "my_user"
DB_PASS = "********"
PORT = 5432

conn = psycopg2.connect(host=DB_HOST, port = PORT, database=DB_NAME, user=DB_USER, password=DB_PASS)

# Create a cursor object
cur = conn.cursor()

cur.execute("""SELECT id, name, email FROM users""")

users = cur.fetchall()

cur.close()
conn.close()

If I check users, I get a list as follows:

[(13, 'Vaibhav Saxena', '[email protected]')]

Now, I convert this to a pandas dataframe:

pd.DataFrame(users)

I get the dataframe as follows:

0          1              2
13   Vaibhav Saxena   [email protected]

I want the columns 0, 1 and 2 as 'id', 'name' and 'email' just like it is in the database while I select the data and before I convert it to a dataframe. Any hint?

pass columns=['id','name','email'] in pd.DataFrame() method....pd.DataFrame(users,columns=['id','name','email']) — Anurag Dabas
– Anurag Dabas, Commented Jul 25, 2021 at 12:30
I wish to get the names while selecting because in my case, any arbitrary number of columns can be chosen at any time and may not be the same all the time. — vaisxn
– vaisxn, Commented Jul 25, 2021 at 12:33

ettanany · Accepted Answer · 2021-07-25 12:41:23Z

2

Can you try the following?

columns = [d[0] for d in cur.description]
pd.DataFrame(users, columns=columns)

answered Jul 25, 2021 at 12:41

ettanany

20k9 gold badges49 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to rename column names on select query of postgres from python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related