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- The structure of the database with the data organized into a blueprint.
- What is a database schema? Identity columns are used for generating key values, which enable the rows to be unique and identified.
- What is the purpose of identity Column in SQL database? Gives a unique value to each row and allows them to be identified.
- What is the purpose of a primary key in SQL database?
- What are the Data Types in SQL table? Integer ,string, booleans, structures, lists, ect
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()
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? A connection object represents a unique session with a data source. Its purpose is to create an open connection to a data source.
- Same for cursor object? It is used to make a connection for executing SQL queries.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object? They both have many special variables and function variables.
- Is "results" an object? How do you know? Yes since "results" is an object because it contains data and many variables.
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()
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? This create is better and can do more functions but is relatively worse when the SQL database is small. In this case, the other method is better because it works better in less data.
- Explain purpose of SQL INSERT. Is this the same as User init? This inserts data into the table while the User init initializes user object with the data.
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()
Updating a User in table in Sqlite.db
Uses SQL UPDATE to modify password
- What does the hacked part do? The hacked part appears when the password is less than 2 characters. It sets the passwrod to a new one.
- Explain try/except, when would except occur? Try and except enable coders to test error code. Except occurs if there is an error with try.
- What code seems to be repeated in each of these examples to point, why is it repeated? Defining conn and cursor seem to repeat, and cursor uses SQL commands UPDATE, DELETE, INSERT, and SELECT.
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()
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 dangerous operation because if done incorrectly, it can delete all the data and all the records from the database.
- In the print statemements, what is the "f" and what does {uid} do? The use of f makes it an f-string, or string format. The {uid} is a placeholder and the variable will be used within the string.
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()
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? The menue repeats because it is a sorce of recursion. It keeps going and does the crud operations until the user decides to stop.
- Could you refactor this menu? Make it work with a List? This menu could be refacted using loops and it could possibly work with a list.
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. You can see data abstraction within the code
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation
Hacks
Data Abstraction
Data Abstraction is used in 2.4a, as it simplifies the representation of the whole database. Within the classes in the database, objects are used to simplify the data. Each aspect of the user, like name and uid, are an object. It is also apparent when the code is debugged. There are many class variables in local and global, and objects are used.
conn = sqlite3.connect('recipes.db')
# create a new table called "recipes" with columns "id", "name", "ingredients", and "instructions"
cursor = conn.cursor()
cursor.execute('''CREATE TABLE recipes
(id INTEGER PRIMARY KEY,
name TEXT,
ingredients TEXT,
instructions TEXT)''')
conn.commit()
import sqlite3
# create a new SQLite database and establish a connection to it
conn = sqlite3.connect('recipes.db')
# define function to perform CRUD operations on the "recipes" table
def create_recipe(conn, name, ingredients, instructions):
cursor = conn.cursor()
cursor.execute('''INSERT INTO recipes (name, ingredients, instructions)
VALUES (?, ?, ?)''', (name, ingredients, instructions))
conn.commit()
print("Recipe created successfully!")
def read_all_recipes(conn):
cursor = conn.cursor()
cursor.execute('''SELECT * FROM recipes''')
rows = cursor.fetchall()
for row in rows:
print(row)
def read_recipe_by_id(conn, id):
cursor = conn.cursor()
cursor.execute('''SELECT * FROM recipes WHERE id = ?''', (id,))
row = cursor.fetchone()
if row is not None:
print(row)
else:
print("Recipe not found.")
def update_recipe(conn, id, name, ingredients, instructions):
cursor = conn.cursor()
cursor.execute('''UPDATE recipes SET name = ?, ingredients = ?, instructions = ?
WHERE id = ?''', (name, ingredients, instructions, id))
conn.commit()
print("Recipe updated successfully!")
def delete_recipe(conn, id):
cursor = conn.cursor()
cursor.execute('''DELETE FROM recipes WHERE id = ?''', (id,))
conn.commit()
print("Recipe deleted successfully!")
# prompt user for CRUD operation and inputs
while True:
print("What would you like to do?")
print("1. Create a new recipe")
print("2. Read all recipes")
print("3. Read a recipe by ID")
print("4. Update a recipe")
print("5. Delete a recipe")
print("6. Exit")
choice = input("Enter your choice (1-6): ")
if choice == '1':
name = input("Enter the recipe name: ")
ingredients = input("Enter the recipe ingredients: ")
instructions = input("Enter the recipe instructions: ")
create_recipe(conn, name, ingredients, instructions)
elif choice == '2':
read_all_recipes(conn)
elif choice == '3':
id = input("Enter the recipe ID: ")
read_recipe_by_id(conn, id)
elif choice == '4':
id = input("Enter the recipe ID: ")
name = input("Enter the new recipe name: ")
ingredients = input("Enter the new recipe ingredients: ")
instructions = input("Enter the new recipe instructions: ")
update_recipe(conn, id, name, ingredients, instructions)
elif choice == '5':
id = input("Enter the recipe ID: ")
delete_recipe(conn, id)
elif choice == '6':
print("Goodbye!")
break
else:
print("Invalid choice. Please try again.")