Psycopg2 connection sql database to pandas dataframe

I am working on a project where I am using psycopg2 connection to fetch the data from the database like this,

cursor = connection.execute("select * from table")
cursor.fetchall()

Now after getting the data from the table, I am running some extra operations to convert the data from cursor to pandas dataframe. I am looking for some library or some more robust way to convert the data to pandas dataframe from psycopg2 connection.

Any help of guidance will be appreciated. Thanks

Answer

You can use pandas sqlio module to run and save query within pandas dataframe.

Let’s say you have a connection of psycopg2 connection then you can use pandas sqlio like this.

import pandas.io.sql as sqlio
data = sqlio.read_sql_query("SELECT * FROM table", connection)
# Now data is a pandas dataframe having the results of above query.
data.head()

For me, sqlio pandas module is working fine. Please have a look at it and let me know if this is what you are looking for.