Loading data to a Postgres database

After data has been extracted from a source system and transformed to align with analytics or reporting use cases, it's time to load the data to a final storage medium. Storing cleaned data in a SQL database makes it simple for data consumers to access and run queries against. In this example, you'll practice loading cleaned data to a Postgres database.

sqlalchemy has been imported, and pandas is available as pd. The first few rows of the cleaned_testing_scores DataFrame are shown below:

             street_address       city  math_score  ... best_score
01M539  111 Columbia Street  Manhattan       657.0      Math
02M545     350 Grand Street  Manhattan       613.0      Math
01M292     220 Henry Street  Manhattan       410.0      Math

This exercise is part of the course

ETL and ELT in Python

View Course

Exercise instructions

Update the connection string to write to the schools database and create a connection object using sqlalchemy.
Use pandas to write the cleaned_testing_scores DataFrame to the scores table in the schools database.
If the table is already populated with data, make sure to replace the values with the current DataFrame.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Update the connection string, create the connection object to the schools database
db_engine = sqlalchemy.____("postgresql+psycopg2://repl:password@localhost:5432/____")

# Write the DataFrame to the scores table
cleaned_testing_scores.____(
	name="____",
	con=db_engine,
	index=False,
	if_exists="____"
)

Edit and Run Code