Common Operations on a PostgreSQL Database

Get Started
1 hour
  • 5 Learning Objectives

About this Hands-on Lab

In this lab we perform some common operations on a database. We create a database, add a table, and fill the table from a `csv` file.

Then we update the database table with a new record, change a record, and finally read form the database table to make sure these operations succeeded.

The PDF of the notebook for this lab is [here.](https://github.com/linuxacademy/content-python-for-database-and-reporting/blob/master/pdf/hol_3.1.l_solution.pdf)

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Start Jupyter Notebook Server and Access on the Local Machine

Connecting to the Jupyter Notebook Server

Make sure the virtual environment it activated!

To activate the virtual environment:

conda activate base

To start the server:

python get_notebook_token.py

This is a simple script that starts the jupyter notebook server and sets it to continue to run outside of the terminal.

On the terminal is a token. Please copy this and save it to a text file on the local machine.

On the Local Machine

In a terminal window, enter the following:

ssh -N -L localhost:8087:localhost:8086 cloud_user@<the public IP address of the Playground server>

It will ask for a password. This is the password used to log in to the Playground remote server.

Leave this terminal open, it will appear nothing has happened, but it must remain open while using the Jupyter Notebook server in this session.

In the browser, enter http://localhost:8087 in the address bar. This will open a Jupyter Notebook site that asks for the token copied from the remote server.

Create the Database and Import Packages Needed

Setup PostgreSQL for cloud_user Access

Create a cloud_user database and a cloud_user user with a password. Grant all priveleges to database cloud_user by user cloud_user.

Start psql

sudo -u postgres psql

Create Database

CREATE DATABASE cloud_user;

Create User

CREATE USER cloud_user WITH ENCRYPTED PASSWORD 'cloud_user';

Grant Access to Database by User

GRANT ALL PRIVILEGES ON DATABASE cloud_user TO cloud_user;

Leave psql

q

Imports and Database connection string.

The PostgreSQL standard port is 5432.

import pandas as pd
import psycopg2

CONNECT_DB = "host=localhost port=5432 dbname=cloud_user user=cloud_user password=cloud_user"
Create a `customers` Table in the Database and Fill It with the Data in the `vets.csv` File

Create Table

Create a table with columns matching the vets.csv file.

create_table_query = '''CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name varchar (25),
    owner varchar (25),
    type varchar (25),
    breed varchar (25),
    color varchar (25),
    age smallint,
    weight float4,
    gender varchar (1),
    health_issues boolean,
    indoor_outdoor varchar(10),
    vaccinated boolean
); '''

try:
    # Make connection to db
    cxn = psycopg2.connect(CONNECT_DB)

    # Create a cursor to db
    cur = cxn.cursor()

    # Send sql query to request
    cur.execute(create_table_query)
    records = cxn.commit()

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
    if(cxn):
        cur.close()
        cxn.close()
        print("PostgreSQL connection is closed")

    print(f'Records: {records}')

Add the Data to Table

Use a try...except...finally block to load the data from vet.csv into the table just created.

try:
    # Make connection to db
    cxn = psycopg2.connect(CONNECT_DB)

    # Create a cursor to db
    cur = cxn.cursor()

    # read file, copy to db
    with open('./vet.csv', 'r') as f:
        # skip first row, header row
        next(f)
        cur.copy_from(f, 'customers', sep=",")
        cxn.commit()

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)

finally:
    #closing database connection.
    if(cxn):
        cur.close()
        cxn.close()
        print("PostgreSQL connection is closed")
        print("customers table populated")
Create a Function to Fetch Data from the Database and Test It

Selecting Data from a Server

Create a function to execute a SQL statement to fetch records from the database. Use try...except...finally and .fetchall(). The user should use LIMIT or TOP() to limit their results.

def db_server_fetch(sql_query):
    try:
        # Make connection to db
        cxn = psycopg2.connect(CONNECT_DB)

        # Create a cursor to db
        cur = cxn.cursor()

        # Send sql query to request
        cur.execute(sql_query)
        records = cur.fetchall()

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)

    finally:
        #closing database connection.
        if(cxn):
            cur.close()
            cxn.close()
            print("PostgreSQL connection is closed")
        return records

Get all data from the database.

select_query = '''SELECT * FROM customers;'''

records = db_server_fetch(select_query)
print(records)
Create a Function to Update the Database and Make the Requested Changes

Change Data in Database

Create a function to execute a SQL statement to update records in the database. Use try...except...finally.

def db_server_change(sql_query):
    try:
        # Make connection to db
        cxn = psycopg2.connect(CONNECT_DB)

        # Create a cursor to db
        cur = cxn.cursor()

        # Send sql query to request
        cur.execute(sql_query)
        records = cxn.commit()

    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)

    finally:
        #closing database connection.
        if(cxn):
            cur.close()
            cxn.close()
            print("PostgreSQL connection is closed")
        return records

Add a new record with the following data:
Esmerelda is a 2.5 yr old female Angus cow that weighs 1250 lbs, has no health issues, is vaccinated, and owned by the Garcia Ranch.

add_data = '''INSERT INTO customers
    (id, name, owner, type, breed, color, age, weight, gender, health_issues, indoor_outdoor, vaccinated)
    VALUES
    (7, 'Esmerelda', 'Garcia Ranch', 'Cattle', 'Angus', 'black', 2.5, 1250, 'f', false, 'outdoor', true);'''

db_server_change(add_data)

Check that the record was added.

select_query = '''SELECT * FROM customers WHERE name = 'Esmerelda';'''

records = db_server_fetch(select_query)
print(records)

Make Petra’s weight 12.5.

update_data = '''UPDATE customers SET weight = 12.5 WHERE name = 'Petra';'''

db_server_change(update_data)

Check the record.

select_query = '''SELECT * FROM customers WHERE name = 'Petra';'''

records = db_server_fetch(select_query)
print(records)

Additional Resources

Setting Up a PostgreSQL Database for Sterling Vet Services

You are a freelance developer who has accepted a job to develop a database for a veterinarian company. They have given you a small CSV file and asked you to populate the database with a single table holding the data in the file. There are notes attached. One asks you to add a weight for Petra of 12.5 lbs. The other asks you to add a new pet Esmerelda who is a 2.5 yr old female Angus cow that weighs 1250 lbs, has no health issues, is vaccinated, and is owned by the GarciaRanch.

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.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!
Thanks for reaching out!

You’ll hear from us shortly. In the meantime, why not check out what our customers have to say about ACG?