Connecting to a PostgreSQL database
In these exercises, you will be working with real databases hosted on the cloud via Amazon Web Services (AWS)!
Let's begin by connecting to a PostgreSQL database. When connecting to a PostgreSQL database, many prefer to use the psycopg2 database driver as it supports practically all of PostgreSQL's features efficiently and is the standard dialect for PostgreSQL in SQLAlchemy.
You might recall from Chapter 1 that we use the create_engine() function and a
connection string to connect to a database. In general, connection strings have the form "dialect+driver://username:password@host:port/database"
There are three components to the connection string in this exercise: the dialect and driver ('postgresql+psycopg2://'), followed by the username and password ('student:datacamp'), followed by the host and port ('@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:5432/'), and finally, the database name ('census'). You will have to pass this string as an argument to create_engine() in order to connect to the database.
This is a part of the course
“Introduction to Databases in Python”
Exercise instructions
- Import
create_enginefromsqlalchemy. - Create an engine to the
censusdatabase by concatenating the following strings:'postgresql+psycopg2://''student:datacamp''@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com'':5432/census'
- Use the
.table_names()method onengineto print the table names.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Import create_engine function
from ____ import create_engine
# Create an engine to the census database
engine = create_engine(____)
# Use the .table_names() method on the engine to print the table names
print(____)This exercise is part of the course
Introduction to Databases in Python
In this course, you'll learn the basics of relational databases and how to interact with them.
In this chapter, you will build on your database knowledge by writing more nuanced queries that allow you to filter, order, and count your data—all within the Pythonic framework provided by SQLAlchemy.
Exercise 1: Filtering and targeting dataExercise 2: Connecting to a PostgreSQL databaseExercise 3: Filter data selected from a Table - SimpleExercise 4: Filter data selected from a Table - ExpressionsExercise 5: Filter data selected from a Table - AdvancedExercise 6: Ordering query resultsExercise 7: Ordering by a single columnExercise 8: Ordering in descending order by a single columnExercise 9: Ordering by multiple columnsExercise 10: Counting, summing, and grouping dataExercise 11: Counting distinct dataExercise 12: Count of records by stateExercise 13: Determining the population sum by stateExercise 14: SQLAlchemy and pandas for visualizationExercise 15: ResultsSets and pandas DataFramesExercise 16: From SQLAlchemy results to a plotWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.