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?
- <mark>The columns of a database.</mark>
- What is the purpose of identity Column in SQL database?
- Can help up differentiate something. Easier to acess the data.
- What is the purpose of a primary key in SQL database?
- A primary key in the SQL database is the iD. It makes it easier to acess the data in the databse. A better primary key for the database is the userid. A name might not be a good idea because someone might have the same name, so a uid is better.
- What are the Data Types in SQL table?
- Integer, string, bollean, images, list, dictionary, class.
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, which is produced by the sqlite3.connect() function, represents each active SQLite database. Their primary functions are transaction management and the creation of cursor objects.
- Same for cursor object?
- for the customer, makes the code simpler. an instance that can be used to call methods that run SQLite statements and retrieve data from query result sets.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Special variables, function variables, and class variables are properties of the cursor object. Conn object features include function variables and unique variables.
- Is "results" an object? How do you know?
- As a result of its atributes, results is an object.
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
- Compare create() in both SQL lessons. What is better or worse in the two implementations?
- This one uses cursor and con, but the last one uses the try and accept. This one is better, because the other one seems more complex. For dob, its very complicated, but for the sqlite 3, you only need one line of code to build the table headers.
- Explain purpose of SQL INSERT. Is this the same as User init?
- The purpose of SQL insert is to add new data to a database. No, it is not the same as user _init_.
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 new password the user entered has fewer than two characters, the "hacked" portion becomes active. This is a crude method of saying that the password the user entered is invalid because it is unsafe.
- Explain try/except, when would except occur?
- You can make code run using try up until an error, at which time the except condition kicks in. This enables software to offer failsafes for mistakes or improper user interactions.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- Every block requires the creation of the connection and pointer objects because they are both closed at the conclusion of each. They must be reactivated in order to be used once more.
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, delete is a dangerous operation. This is because you can accidentally delete user data. When left to human inputs, DELETE can be hazardous. User error could result in the loss of crucial data that is hard to recover.
- In the print statemements, what is the "f" and what does {uid} do?
- The "f" sets a print statement to read "uid" rather than the literal text "uid" as the value of the variable uid.
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?
- Recursion is taking place. The code calls itself as it runs, so the menu keeps repeating.
- Could you refactor this menu? Make it work with a List?
- Yes, it is possible to refactor the menu and 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.
- In this implementation, do you see procedural abstraction?
- Yes, I have used the find_by_uid and check_credentials methods in the update and delete functionality.
- 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