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 baseTo start the server:
python get_notebook_token.pyThis 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_userAccessCreate a
cloud_userdatabase and acloud_useruser with a password. Grant all priveleges to databasecloud_userby usercloud_user.Start psql
sudo -u postgres psqlCreate 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
qImports 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.csvfile.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...finallyblock to load the data fromvet.csvinto 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...finallyand.fetchall(). The user should useLIMITorTOP()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 recordsGet 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 recordsAdd 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)
