Table of Contents

1. Challenge

   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.

2. Challenge

   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"
   

3. Challenge

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

4. Challenge

   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)
   
   

5. Challenge

   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)