Psycopg2 connection sql database to pandas dataframe

Question

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

Uncivil

Answer 1

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.

Allamerican

I just tested and it is working the way I wanted. Thank you so much for the quick reply. – Uncivil

I am glad that I could help you out. Please mark the answer as accepted. Thanks – Allamerican

Do I need to close the connection? – Conjoint

@Conjoint - yes you need to. Just tested! – Ozzie

Note that only the connection objects created through SQLAlchemy are supported, if you use a native psycopg2 connection object there's no guarantee that it will work (it works on my setup but I get a warning). – Thalweg

in a multithreaded scenario - would pd.read_sql block? seems im experiencing some blocking there... – Cubical

Answer 2

This may be helpful for your case:

import pandas.io.sql as sqlio

df = sqlio.read_sql_query(query, connection)

Where in your case, query = "select * from table"

Unpen