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?
- A database schema is how the data in a database is saved. The schema can be considered the metadata.
- What is the purpose of identity Column in SQL database?
- Identity columns help to identify each specific row of the database. This ensures that each row has its own identity column.
- What is the purpose of a primary key in SQL database?
- A primary key in a SQL database is something which can uniquely identify the row of a database. For example, the users database uses id for the primary key. For the coders, we use the uid as the primary key.
- What are the Data Types in SQL table?
- The data types in a SQL table include integers, floats, and strings.
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 is the metadata for a database
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 object which allows us to connect with the sqlite db to be able to interact with it.
- Same for cursor object?
- A cursor object is an object which allows us to search through the sql database. These allow us to work with sqlite db.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Attributes in the object include
- Is "results" an object? How do you know?
- "results" is an object. We know this because it is a collection of information and has some methods.
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?
- The code for this create is much shorter. It requires the conn and cursor objects to be opened and closed. The cursor must execute a code which takes all of the variables and adds it to the table.
- Explain purpose of SQL INSERT. Is this the same as User init?
- SQL INSERT allows rows to be added to an SQL database table. The User init function allows a dataset which is defined to be able to added to an SQL database. This serves a similar purpose.
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?
- The hacked part of the update function outputs a message to the user if the length of the password is too short. This serves as authentication for the user to make sure that a password is long enough for security purposes.
- Explain try/except, when would except occur?
- Try and except work as things which check if certain code is running/working. Try runs if the code is working smoothly, and in this case serves to update the password. Except occurs if there is an error with the connection and cursor with the database. This serves as a good way to check errors if there is something wrong with the code.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- Code that seems to be repeated include import sqlite3, defining the conn and cursor methods, cursor execute, conn.commit(), and conn and cursor close. This code seems to be repeated because it is required to connect and use the cursor method in the database. cursor.execute() seems to run the wanted code, whether it is update, delete, read, etc. conn.commit() appears that it commits the changes to the database in order to make the changes permanent. The closing of cursor and conn appears to be present because it is important be able to close the working connections and cursors in order to be able to have different ones. This is especially important when working with servers because it makes sure to have orderly code and prevents resources from being used unnecessarily.
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?
- DELETE can be a dangerous operation because it can remove information from the database, and if placed in the wrong hands, they could delete important information by accident. However, delete is also necessary in order to maintain a clean database with proper records.
- In the print statemements, what is the "f" and what does {uid} do?
- In the print statements, the "f" and {uid} allow the coder to include the selected uid without having to add multiple strings in the print statement. It allows for easy replacement and making a statement which uses the variable which the user called for.
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 so the user has access to the functions multiple times if they want, instead of having to restart the code cell every time they want to try a new function.
- Could you refactor this menu? Make it work with a List?
- This menu could be refactored, to reduce the number of elif statements had. Refactoring allows the code to be cleaned up and become more organized. The menu could be held in a list or dictionary and based on the output, the specific function would occur.
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")
options = [('c', create), ('r', read), ('u', update), ('d', delete), ('s', schema)]
def rmenu():
while True:
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
found = False
for op, func in options:
if operation.lower() == op:
func() # call corresponding function
found = True
break
if not found and len(operation)>0:
print("Please enter c, r, u, d, or s")
elif len(operation)==0: # Escape Key
break
rmenu()
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, this implementation has a lot of procedural abstraction. There are functions created in order to be able to define what procedures we want to do in the code. For example, there are procedures defined for create, read, update, and delete. This allows us to have a broad function which can be used to add information to specific rows of the database.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
- There is data abstraction in this code. There are many things which are defined when creating a user, from their name to password to date of birth. These all need to be stored together, and using the code, we create a specific uid for the user which allows the rest of the data to be called from. The uid is essential in functions like update and delete, where data is removed or changed based on the user-specific uid.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
- I decided to use an OOP style to do data manipulation on the table which I used for my CPT project.
- Do you see data abstraction? Complement this with Debugging example.
Reference... sqlite documentation