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

Hasnain Khan · Accepted Answer · 2022-01-28 10:27:10Z

11

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.

answered Jan 28, 2022 at 10:27

Hasnain Khan

3834 silver badges10 bronze badges

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

6 Comments

Pythonic Over a year ago

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

Hasnain Khan Over a year ago

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

user16511234 Over a year ago

Do I need to close the connection?

BillaD Over a year ago

@Gobrel - yes you need to. Just tested!

To마SE Over a year ago

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

|

Mario · Accepted Answer · 2022-01-28 10:27:50Z

0

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"

answered Jan 28, 2022 at 10:27

Mario

5833 silver badges21 bronze badges

Collectives™ on Stack Overflow

Psycopg2 connection sql database to pandas dataframe

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related