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?
- How data is organized in a database.
- What is the purpose of identity Column in SQL database?
- Generating key values for primary key columns.
- What is the purpose of a primary key in SQL database?
- Uniquely identifies each record in a table
- What are the Data Types in SQL table?
- String, numeric, date and time
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?
- Creates a session with the data source
- Same for cursor object?
- Retrieves data one row at a time
- 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 has data and 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?
- The imperative implementation makes a little bit more sense to me as it just inserts a new row in the table. The other one creates a new object that is a user in a group of users
- Explain purpose of SQL INSERT. Is this the same as User init?
- Init builds the class into a database, sort of, while insert inserts a new row into the database
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?
- If the password someone enters a new password less than 2 characters wrong, the password would change to "gothackednewpassword123"
- Explain try/except, when would except occur?
- Except would occur when there is an error with sqlite
- What code seems to be repeated in each of these examples to point, why is it repeated?
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?
- Yes, it can be, because it removes information from a database that may not be recovered if lost.
- In the print statemements, what is the "f" and what does {uid} do?
- f allows an object to be placed directly in the print statement with {} around it. {uid} means to print whatever the uid is right there.
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()
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
# establish connection and such
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
# creating the table
cars = '''CREATE TABLE reservations(
_name TEXT,
_car TEXT,
_dates TEXT
)'''
cursor.execute(cars)
print("Table created successfully!")
# commit new table and close connection
conn.commit()
conn.close()
import sqlite3
def create_entry():
name = input("Enter your name:")
car = input("What car will you be using?")
dates = input("Which dates will you plan on using the car? (MM/DD - MM/DD)")
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO reservations (_name, _car, _dates) VALUES (?, ?, ?)", (name, car, dates))
# Commit the changes to the database
conn.commit()
print(f"A record for {name} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
conn.close()
create_entry()
import sqlite3
def update_entry():
name = input("Enter your name to update")
choice = input("What do you want to change? Enter c for car and d for dates.")
if choice == "c":
car = input("What car do you want to drive instead?")
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
try:
cursor.execute("UPDATE reservations SET _car = ? where _name = ?", (car, name))
if cursor.rowcount == 0:
print(f"The name {name} was not found")
else:
print(f"The car for {name} was updated to {car}")
conn.commit()
except sqlite3.Error as error:
print("Error while updating", error)
if choice == "d":
dates = input("What dates will you be driving instead?")
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
try:
cursor.execute("UPDATE reservations SET _dates = ? where _name = ?", (dates, name))
if cursor.rowcount == 0:
print(f"The name {name} was not found")
else:
print(f"The dates for {name} have been updated to {dates}")
conn.commit()
except sqlite3.Error as error:
print("Error while updating", error)
update_entry()
import sqlite3
def delete_entry():
name = input("Enter your name to delete your entry")
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM reservations WHERE _name = ?", (name,))
if cursor.rowcount == 0:
print(f"The name {name} was not found in the table")
else:
print(f"The entry for {name} has been removed")
conn.commit()
except sqlite3.Error as error:
print("Error while deleting", error)
delete_entry()
import sqlite3
def read_entry():
conn = sqlite3.connect('instance/sqlite.db')
cursor = conn.cursor()
data = cursor.execute('SELECT * FROM reservations').fetchall()
for row in data:
print(row)
read_entry()
def db_menu():
option = input("(C)reate, (R)ead, (U)pdate, or (D)elete?")
if option == "C":
create_entry()
elif option == "R":
read_entry()
elif option == "U":
update_entry()
elif option == "D":
delete_entry()
else:
print("You did not enter one of the correct options. Please try again.")
db_menu()
