CODEWITHSUNDEEP
pythonpostgresql
Eri Bytyci
Eri Bytyci

Reputation: 29

Printing data results from postgresql to panda dataframe

I am trying to print the results of the joined table from postgresql to python. However when I try to print the results, the table shows up but I receive NaN data. Can someone help?

conn = psy.connect( dbname = "funda_project", host = "localhost", user = 
"postgres", password = "ledidhima2021.") 
cursor = conn.cursor()
conn.commit()

createjointable2 = '''SELECT(
    distance_data."Municipality",
    distance_data."Childcare/Nursery",
    distance_data."Leisure/Culture/Library",
    sales_details."Purchase_price",
    sales_details."Publication_date",
    sales_details."Date_of_signature",
    house_details."Type_of_house",
    house_details."Object_categorie",
    house_details."Construction_year",
    house_details."Energy_label_class",
    demo_data."Age_Group_Relation_(15-20)",
    demo_data."Age_Group_Relation_(20-25)",
    demo_data."Age_Group_Relation_(25-45)")

FROM "distance_data"
INNER JOIN "zip_data" 
    ON "distance_data"."Municipality" = "zip_data"."Municipality" 
INNER JOIN "demo_data" 
    ON "zip_data"."Municipality" = "demo_data"."Municipality"
INNER JOIN "sales_details"
    ON "zip_data"."globalId" = "sales_details"."GlobalID"
INNER JOIN "house_details"
    ON "zip_data"."globalId" = "house_details"."GlobalID"
;'''

cursor.execute(createjointable2);

from pandas import DataFrame
eri= pd.DataFrame(cursor.fetchall())
datalist = list(eri)
results = pd.DataFrame (eri, columns = ["Municipality", "Childcare/Nursery", 
"Leisure/Culture/Library", "Purchase_price", "Publication_date", "Date_of_signature", 
"Type_of_house", "Object_categorie", "Construction_year", "Energy_label_class", 
"Age_Group_Relation_(15-20)", "Age_Group_Relation_(20-25)", "Age_Group_Relation_(25-45)"])
results

Upvotes: 3

Views: 1958

Answers (1)

Tõnis Piip
Tõnis Piip

Reputation: 492

Pandas has a built-in SQL query reading function pd.read_sql_query(query, connection), which assign the returned table value to a dataframe.

dataframe = pd.read_sql_query("SELECT * FROM table;", conn)

conn being the connection object you created and is also in your code.

Another way is almost what you tried as well:

from pandas import DataFrame
df = DataFrame(cursor.fetchall())
df.columns = cursor.keys()

Upvotes: 1

Related Questions