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
The Tri 2 Final Project is 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
Notes:
- This is more of a raw way to deal wtih databases
- Abstraction --> a different way to represent something in a simpler manner to allow us to focus on one thing
- connect and cursor commands were both abstracted out in SQLAlchemy
Schema of Users table in Sqlite.db
Uses PRAGMA statement to read schema.
Describe Schema, here is resource Resource- What is a database schema? -- a database schema created the outline for how information will be organized. In our case it identifies what information will be stored in each object
- What is the purpose of identity Column in SQL database? -- when dealing with an object within the table, we can use the identity column to refer to that object. The identity column should be unique to the object
- What is the purpose of a primary key in SQL database? -- integer primary key --> numbers each row.. can also be used to refer to object tho not as ideal
- What are the Data Types in SQL table? -- integer, strings, blog, varchar?
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 is an abstraction of an open database
- Same for cursor object? --
- 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, results is an object, since it has both data and functions.
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()
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 because there is recursion; the method calls itself within the actual method. The
- Could you refactor this menu? Make it 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.
- Create a new Table or do something new, sqlite documentation
- In implementation in previous bullet, do you see procedural abstraction?
import sqlite3
def read():
conn = sqlite3.connect(database)
cursor = conn.cursor()
results = cursor.execute('SELECT * FROM meal_log').fetchall()
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
cursor.close()
conn.close()
# CREATE METHOD
def create():
name = input("Enter the name of your meal (names must be unique.. if there are multiple of the same meal, add a number):")
foodgroup = input("Enter what food group the meal is in:")
calories = input("Enter the number of calories the meal is")
time = input("enter the time you ate the meal (include AM/PM)")
conn = sqlite3.connect(database)
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO meal_log (_name, _foodgroup, _calories, _time) VALUES (?, ?, ?, ?)", (name, foodgroup, calories, time))
conn.commit()
print(f"A new meal {name} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
cursor.close()
conn.close()
# DELETE METHOD:
import sqlite3
def delete():
name = input("Enter meal name to delete")
conn = sqlite3.connect(database)
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM users WHERE _name = ?", (name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"Name {name} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with name {name} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
cursor.close()
conn.close()
def menu():
operation = input("Enter: (C)reate (R)ead or (D)elete")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'd':
delete()
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("error")