Unit 2.4b Using Programs with Data, SQL
Using Programs with Data is focused on SQL and database actions. Part B focuses on learning SQL commands, connections, and curses using an Imperative programming style,
Database Programming is Program with Data
Each Tri 2 Final Project should be an example of a Program with Data.
Prepare to use SQLite in common Imperative Technique
- Explore SQLite Connect object to establish database connection- Explore SQLite Cursor Object to fetch data from a table within a database
Schema of Users table in Sqlite.db
Uses PRAGMA statement to read schema.
Describe Schema, here is resource Resource- What is a database schema?
You can use data in dictionaries, lists, APIs, or pulling csv files into pandas. There are many different sources.
- What is the purpose of identity Column in SQL database?
A column in the SQL database table is the vertical part. It stores the primary key used for user identificatin and is unique to every user.
- What is the purpose of a primary key in SQL database?
A primary key is a unique number assigned to each user. This is used when users are called upon, so as not to confuse those with the same name, password, or other features.
- What are the Data Types in SQL table?
Some data types included in the SQL table include strings, int, boolean, and many more including blobs. Blobs allow for dictionaries to be put directly in the database. An example could be a user survey.
import sqlite3
database = 'instance/sqlite.db' # this is location of database
def schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('recipes')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
Reading Users table in Sqlite.db
Uses SQL SELECT statement to read data
- What is a connection object? After you google it, what do you think it does?
- Same for cursor object?
Connecion (conn) and cursor are ways of working with raw data. Connection objects contain info like the server address, username/ password, and provides methods for executing SQL queries and transactions. Cursor objects allows fetching of a specific number of rows from the results database, and the "cursor" moves from one row to another until all the rows are processed.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
Attributes of the objects include name, username, password, date of birth, and a unique user id key.
- Is "results" an object? How do you know?
According to programiz, an object is "a collection of data (variables) and methods (functions)." "Results" is considered an object, because it returns a data set containing record from the users table from SQLite.
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM recipes').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
Create a new User in table in Sqlite.db
Uses SQL INSERT to add row
- Compore create() in both SQL lessons. What is better or worse in the two implementations?
In the last lesson, there is a flask object used to update the sqlite table, while this one is connecting to the database file directly. The 4a lesson was more focused on
- Explain purpose of SQL INSERT. Is this the same as User init?
insert adds more rows to the database users table. init however uses the db model table as a blueprint for inheritance, as we learned in 4a.
import sqlite3
def create():
recipename = input("Enter recipe name:")
recipelink = input("Enter recipe link:")
recipetype = input("Enter recipe type:")
recipecuisine = input("Enter recipe cuisine:")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO recipes (_recipename, _recipelink, _recipetype, _recipecuisine) VALUES (?, ?, ?, ?)", (recipename, recipelink, recipetype, recipecuisine))
# Commit the changes to the database
conn.commit()
print(f"A new recipe record {recipename} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do?
The hacked part is meant to control for passwords that do not follow the requirements; they would be easy to hack.
- Explain try/except, when would except occur?
this is how test data is passed to see if there are errors during code execution. This is a debugging method that includes a try block surrounding code that must be monitored. If there is an exception or runtime error, then the program execution goes to the exception block. This also catches bad or duplicated data. This mechanism gracefully exit out of a runtime error rather than crashing an entire application.
- What code seems to be repeated in each of these examples to point, why is it repeated?
try/ except is repeated many times, which is important for gracefully exiting when there is an error; otherwise the entire application might crash or go in an endless loop.
import sqlite3
def update():
recipename = input("Enter recipe name to update")
#recipelink = input("Enter recipe link to update")
recipetype = input("Enter recipe type to update")
#recipecuisine = input("Enter recipe cuisine to update")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE recipes SET _recipetype = ? WHERE _recipename = ?", (recipename, recipetype))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {recipename} was not found in the table")
else:
print(f"The row with recipe name {recipename} the recipe type has updated successfully")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
update()
Delete a User in table in Sqlite.db
Uses a delete function to remove a user based on a user input of the id.
- Is DELETE a dangerous operation? Why? Delete is a very dangerous operation, because it makes it easy to remove data permenantly.
- In the print statemements, what is the "f" and what does {uid} do? f is used to denote the beginning of a quotation and write the words to print.
import sqlite3
def delete():
recipename = input("Enter recipe name to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM recipes WHERE _recipename = ?", (recipename,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No recipe {recipename} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {recipename} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
delete()
Menu Interface to CRUD operations
CRUD and Schema interactions from one location by running menu. Observe input at the top of VSCode, observe output underneath code cell.
- Why does the menu repeat?
- Could you refactor this menu? Make it work with a List?
This menu makes it easier to play all the CRUDS commands. It repeats in order to perform each desired function.
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif operation.lower() == 's':
schema()
elif len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
Hacks
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- In this implementation, do you see procedural abstraction?
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation
Imperitive: focused more on the data first, rather than prioritizing functionality Object oriented: working with objects, methods, and class
Connect (conn) and Cursor are ways of working with raw data Extraction: makes a databse simpler by using object oriented paradime
Database column: vertical part; primary key for databse Users database: primary case was uid and number primary key; unique number Datatypes: strings, int, boolean, and many more including blobs (allows for dictionaries to be put directly in databse; for example a user survey)
import sqlite3
database = 'instance/sqlite.db' # this is location of database
def schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('users')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
import sqlite3
def read():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM users').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your user id:")
password = input("Enter your password")
dob = input("Enter your date of birth 'YYYY-MM-DD'")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (name, uid, password, dob))
# Commit the changes to the database
conn.commit()
print(f"A new user record {uid} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
import sqlite3
def update():
uid = input("Enter user id to update")
password = input("Enter updated password")
if len(password) < 2:
message = "hacked"
password = 'gothackednewpassword123'
else:
message = "successfully updated"
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE users SET _password = ? WHERE _uid = ?", (password, uid))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
print(f"The row with user id {uid} the password has been {message}")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
#update()
import sqlite3
def delete():
uid = input("Enter user id to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM users WHERE _uid = ?", (uid,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {uid} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {uid} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
#delete()