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 a column in a database that is organized by a certain piece of information
-
What is the purpose of identity Column in SQL database? The identity Column is used to diffrentiate data, meaning that each piece of data will be different. In our database the identity column is the id column. Since all the ids are different, each user will be differentiated. Things like names don't make good idenitty columns, because there is a possibility that there will be duplicate names, and if there are duplicates then not everything can be differentiated.
-
What is the purpose of a primary key in SQL database? Purpose of primary key is so that certain pieces of information in a database can be easily accessed. Primary key shouldn't be changed for a piece of data and remains constant so we can always indentify something, easily access it.
-
What are the Data Types in SQL table? Integer, string, boolean, images, etc. Any data type or structures (lists, dictionary, class) even. A table can fit any data type.
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 represents a connection to a database. I think it allows connection with a database so you can interact with it, for example execute commands and retrieve data from the databse.
-
Same for cursor object? Cursor object is object that provides a way to iterate over the rows a result sset returned by SQL query. I think it lets the programmer get/fetch data from a database each row, and preform oeprations on each row every time they are fetched.
-
Look at conn object and cursor object in VSCode debugger. What attributes are in the object? The attributes in the conn object are special variables, function variables, class variables, in_transaction, isolation_level, row_factory, and total_changes. The attrubutes in the cursor object are special variables, function variables, arraysize, connection, description, lastrowid, row_factory, and rowcount.
-
Is "results" an object? How do you know? "results" is object because it has attributes, it is set equal to cursor.execute()
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? For create function in 2.4a the function
Comparing the create() in both SQL lessons, the create function in 2.4a uses the SQLite database to take in the data that the user inputs to create new usuers. In 2.4b, the create function uses SQLAlchemy to create new usuers. The 2.4a create function by adding SQLite database can be used for more simple projects, as you just have to enter a few pieces of information, however the 2.4b function using SQLAlchemy would be better for longer more complex projects.
- Explain purpose of SQL INSERT. Is this the same as User init? The purpose of SQL INSERT is to insert new rows of data into the database table. On the other hand,User init initalizes a nd sets up new users and creates them. So, SQL INSERT and User init are not the same, as INSERT just adds new data in a database table, while User in it is used to create new user in the data system.
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 checks if the length of the password when being updated is less than two characters, if it is then the code assumes that the user is being hacked, and if it greater than 2 then the password is successfully updated.
-
Explain try/except, when would except occur? Try/except is used in case errors occur, the try method tries to see if something works and if it doesn't and there is an error it triggers the except method.
-
What code seems to be repeated in each of these examples to point, why is it repeated? Try/except is repeated. It is important and it repeated because it makes sure in different places if there are any errors, for example if an input is not valid or incorrect, then it acts according by, for example telling the user that there is an error.
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 because for this after deleting data there is no way of recovering it. You are at risk of accidentally deleting all your data and losing it forever!
- In the print statemements, what is the "f" and what does {uid} do? You can use the f to embed expressions inside of string literals. The {uid} is the put the variable uid insdie the string for the user id that is being used in the code. This is helpful so you don't have to do the concatentating strings with so many + and " all the time.
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? It repeats because it is recursion, meaning that the function keeps calling itself over and over and repeats.
- Could you refactor this menu? Make it work with a List? You could refactor this by making a list with the (C)reate (R)ead (U)pdate or (D)elete or (S)chema as the values inside. For example, CRUDS = ["C", "R", "U", "D", "S"]
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? I see procedural abstraction in several places throughout. For example, the seperation of the CRUD functions into four different functions to prevent these functions to getting too long or prone to be mixed up. In each of these methods, I created new function. See my new data table for movie tracking in different notebook!
-
In 2.4a or 2.4b lecture
-
Do you see data abstraction? Complement this with Debugging example. I saw it all throughout the 2.4a and 2.4b lectures, all the different classes and functions to seperate different things to do. A specific example, the find_by_uid(uid) function and the check_credentials(uid, password) function. These two are methods to identify user to credentials uid and password, and they are used several times throughout the code, for example find_by_uid(uid) function is used in the update and delete functions to make sure that the desired user to be updated or deleted actually exists. So, instead of having to write out the code block to go through the database to check if a uid exists, you can just call the function. The check_credentials(uid, password) function is also used in the create and update functions, and it verifies if the password inputted matches the uid. This is just a validation system to make sure that people can't randomly modify or delete other people's data. Same thing for the find_by_uid(uid), instead of having to write out the code block to go through the database to check if the password matches the uid, you can just call the function. Both of these functions are procedures that validate something based on the chaging parameters, which are uid and password. It organizes the code and makes it easier for the user to use or understand it.
-
Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation