Use PostgreSQL with Python

45 minutes
  • 4 Learning Objectives

About this Hands-on Lab

In this lab, you will learn how to perform Create Read Update and Delete (CRUD) processes on a PostgreSQL database. You will first create a table and then add data to it. You will practice reading data from the table and making corrections to the data.

You will need basic Python programming and SQL skills for this lab:
– [Certified Associate in Python Programming Certification](
– [SQL Deep Dive](

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Create DB Connection (
  1. First, you will need to create a database user to use in your application. You can do this by connecting to the database as the postgres user using the psql command.

  2. Once connected as the postgres user, you can create a database user named cloud_user. Be sure to grant the user access to the contracts database.

  3. With the user created, you are ready to create the get_database_connection function in the file. This function will return a connection object that can be used to access the database.

Create and Populate Table (

With the connection created, create a table and populate it with the create_table function.

Read Data (

Now you can read the data from the table with the read_data_from_db function. The skeleton also has code to compare the read data to a known good copy and print it to the screen.

Update and Delete (

With the data successfully loaded, you are now ready to make some updates via the update_data function.

  1. First, you need to update the due date for the author “Smith, Jackson”.

  2. Lastly, you’ll need to resolve the failed assertation the number of Smith Jackson rows is incorrect by deleting the extra rows.

Additional Resources

The Scenario

Atlantic Publishing is a start-up publishing house with the motto, "We print what others won't!" They started keeping written lists that summarize the authors' contracts. Finally realizing they need to have a more robust method, they decided to move to a database. The first step they took was to create a Python list of their written summarized data — but they were unsure how to proceed past that point! They have hired you to create and maintain their database as well as provide them needed data on the spot.

Log in to the server over SSH using the credentials provided.

Note: This can also be run on your local machine assuming you already have PostgreSQL set up and listening on localhost.

You can find the skeleton Python files used in this lab here:

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?