SQL Table to Pandas DataFrame

Question

I'm using a PostgreSQL database with Psycopg2 library. I want to get the SQL table and convert it to a pd.DataFrame. So I need to convert SQL table to a dict like {col1:[val1,val2], col2:[val1,val2]} but when I use SELECT * FROM tablename , it doesn't include column names. I don't want to use pandas functions directly connectiong to SQL. If you know how to get the SQL table as a dictionary like I mentioned above, I'm happy if you write.

Thanks for your help in advance.

Sebastian Baltser · Accepted Answer · 2021-03-29 06:25:14Z

4

You can extract the column-names from the cursor in the description-attribute after executing a query:

cursor.execute("SELECT * FROM tablename")
columns = [desc[0] for desc in cursor.description]

afterwards you should be able to parse the fetched data to a DataFrame with the correct column-names using the columns-argument:

data_df = pd.DataFrame(cursor.fetchall(), columns=columns)

finally convert the DataFrame to a dictionary with lists as values using to_dict:

data_dict = data_df.to_dict(orient="list")

answered Mar 29, 2021 at 6:25

Sebastian Baltser

7584 silver badges11 bronze badges

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

1 Comment

electromeow Over a year ago

Thanks. I'll try it. I think I won't need to convert it to dictionary, because I need the dataframe. Dictionary is unnecessary.

Collectives™ on Stack Overflow

SQL Table to Pandas DataFrame

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related