Common Operations on a PostgreSQL Database

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.

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 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")

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)

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)