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?
- the categories of a table
- What is the purpose of identity Column in SQL database?
- makes it easier to search through and access the data
- What is the purpose of a primary key in SQL database?
- to be unique to each row
- What are the Data Types in SQL table?
- strings, floats, booleans, integers, dictionaries, lists
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('reviews')").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?
- It helps people store their personal data in a sqlite table
- Same for cursor object?
- lets you have sqlite methods
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Is "results" an object? How do you know?
- Yes it is, because it stores data
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 review').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
- Compare create() in both SQL lessons. What is better or worse in the two implementations?
- In the first lesson, the create function uses object oriented programming while the second one takes a more imperative approach, In OOP , large databases stay organized while in Imperative may become difficult to navigate and add to after a certain amount of data. However, imperative is easier to understand and can therefore be easier to work with at times.
- Explain purpose of SQL INSERT. Is this the same as User
__init__?
- SQL INSERT inserts data into a specified schema of the table. User init initiallizes the dataset. They are not the same thing.
import sqlite3
def create():
rname = input("Enter the recipe you are reviewing:")
comment = input("Enter your review:")
rating = input("Enter your rating out of 10:")
uid = input("Enter your Username:")
# 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 review (_rname, _comment, _rating, _uid) VALUES (?, ?, ?, ?)", (rname, comment, rating, uid))
# 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?
- Makes sure the length of the password is more than 2 characters
- Explain try/except, when would except occur?
- When the function doesn't work properly, or in this case the updated password doesn't fit the requirements.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The conn and cursor being connected to the sqlite database are defined in each file. This is because without this the database won't exist.
import sqlite3
def update():
rname = input("Enter the recipe you are reviewing:")
comment = input("Enter your review:")
rating = input("Enter your rating out of 10:")
uid = input("Enter your Username:")
# 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 review SET _rname = ?, _comment = ?, _rating = ? WHERE _uid = ?", (rname, comment, rating , 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} was updated")
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?
- Yes, it is dangerous because it doesn't ask for any user validation and deletes the user without asking for confirmation.
- In the print statemements, what is the "f" and what does {uid} do?
- the f in print statements automatically converts the variables to strings.
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 review 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?
- Menu repeats because it's getting called several times.
- Could you refactor this menu? Make it work with a List?
- Yes, you could but would be less efficient.
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
import sqlite3
conn = sqlite3.connect(database)
cursor = conn.cursor()
try:
cursor.execute("CREATE TABLE recipes AS SELECT _rname, _comment, _rating, _uid FROM review;")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the CREATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
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()