CODEWITHSUNDEEP
pythonsqlpandaspostgresql
electromeow
electromeow

Reputation: 251

SQL Table to Pandas DataFrame

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.

Upvotes: 4

Views: 5551

Answers (1)

Sebastian Baltser
Sebastian Baltser

Reputation: 758

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")

Upvotes: 4

Related Questions