Python3: Connecting into Postgresql database with Python, how do I get resulting query to return as rows in a pandas dataframe?

Question

I have connected my Python via a Jupyter Notebook to my local Postgresql database. I am able to run a SELECT query successfully and extract out the data from my table.

However, I want to show the rows of data in my Postgresql table as a dataframe instead of what I currently have.

Below is my code:

conn = psycopg2.connect("dbname=juke user=postgres")

cur = conn.cursor()
cur.execute('SELECT * FROM albums')

for i in cur:
    print(i)

This is my output from the code:

How do I get the output to show as rows in a dataframe instead?

I looked at and tried a bunch of different possible solutions from recommended duplicate post that people shared. Unfortunately, none of them worked.

Possible duplicate of [How to convert SQL Query result to PANDAS Data Structure?](https://stackoverflow.com/questions/12047193/how-to-convert-sql-query-result-to-pandas-data-structure) — Grismar, Sep 10 '19 at 03:39

score 0 · Accepted Answer · answered Sep 10 '19 at 04:39

create dataframe using pandas - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html:

import pandas as pd
conn = psycopg2.connect("dbname=juke user=postgres")
df = pd.read_sql_query("SELECT * FROM albums", conn)
df.head()

Python3: Connecting into Postgresql database with Python, how do I get resulting query to return as rows in a pandas dataframe?

1 Answers1