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?
- schema defines what variable types there are and what can be put in
- What is the purpose of identity Column in SQL database?
- What is the purpose of a primary key in SQL database?
- It makes the querying easier and gives each data thing a unique place
- What are the Data Types in SQL table?
- Some include String, VARCHAR, Integer, and even blog, which can represent a lot of different types of data
import sqlite3
database = '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('storage')").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 accesses the database
- Same for cursor object?
- A cursor object does stuff to the database
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- It has total_changes, and is_transaction among us
- Is "results" an object? How do you know?
- it is not because it doesn't show up, it is a list instead
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 storage').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
return results
# 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?
- The one in this lesson is shorter, but the one in the other lesson is easier to implement.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL insert is a command that creates a new row in the db. It is not the same as init which just creates a new User object.
import sqlite3
def create():
name = input("Enter your name to create:")
id = input("Enter your storage id:")
password = input("Enter your combination:")
contents = input("Enter what you want to put in it:")
# 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 storage (id, combination, name, contents) VALUES (?, ?, ?, ?)", (id, password, name, contents))
# Commit the changes to the database
conn.commit()
print(f"A new storage record {id} 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?
- tells the user that their entered password was too short and thier new password is instead 'gothackednewpassword123'
- Explain try/except, when would except occur?
- The code could be bugging and failing to interact with the db or the information the user entered was invalid
- What code seems to be repeated in each of these examples to point, why is it repeated?
- i dont know there looks to be little repetition
import sqlite3
def update():
id = 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 storage SET combination = ? WHERE id = ?", (password, id))
if cursor.rowcount == 0:
# The id was not found in the table
print(f"No id {id} was not found in the table")
else:
print(f"In the row with id {id} 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 it can easily remove a lot of data
- In the print statemements, what is the "f" and what does {id} do?
- f makes it an fstring which in python lets you pass variables directly into the string without concatination
import sqlite3
def delete():
id = 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 storage WHERE id = ?", (id,))
if cursor.rowcount == 0:
# The id was not found in the table
print(f"No id {id} was not found in the table")
else:
# The id was found in the table and the row was deleted
print(f"The row with id {id} 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 menu repeats to pass through different functions
- Could you refactor this menu? Make it work with a List?
- You could make it with a dictionary of functions as i did below
# 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
dictionary = {
'c': create,
'r': read,
'u': delete,
'd': update,
's': schema,
}
try:
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema").lower()
dictionary[operation]()
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?
- There is procedural abstraction in the last code block because what runs depends on what you type.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- I see data abstraction in the way of the cursor representing sqlite commands
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
- Do you see data abstraction? Complement this with Debugging example.
Reference... sqlite documentation