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?
It is like a category.
- What is the purpose of identity Column in SQL database?
The purpose of identity Column in SQL database is to organize the information and data.
- What is the purpose of a primary key in SQL database?
Primary piece should be unique, such as an ID number.
- What are the Data Types in SQL table?
Integer, string, images, text, lists, dictionary, class, and boolean.
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 object that connects two objects together. Ater Googleing it, connection object is an instance of a connection class that represents a connection between a program and a database. It contains information about the database, such as the server address, database name, authentication details, and other parameters needed to establish a connection.
- Same for cursor object?
A cursor object is an instance of a cursor class that allows a program to interact with the database by executing SQL commands and retrieving results. It maintains the current position in the result set and provides methods to navigate and modify the data.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
The cursor object may have attributes such as the SQL statement being executed, the result set, the number of rows affected, and the position in the result set. Conn objects include the database driver, connection parameters, status, and transaction state.
- Is "results" an object? How do you know?
"Results" is a variable that holds data returned from a database query, it could be an object if the data is returned, which is known in the form of a collection or a custom class instance.
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?
Object oriented programming is different and is organized. It is bigger too. However imperative might be better when doing a small action.
- Explain purpose of SQL INSERT. Is this the same as User init?
The purpse of SQL INSERT is to add new rows of data inot a database. No, it is not the same as User init because it initializes the class's attributes.
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your UID:")
password = input("Enter your password")
dob = input("Enter your DOB '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 tells users that they have been hacked due to their weak password that consists of two characters. Ultimately, it simply communicates to the user they have been hacked and suggests to change the password and to make their password stronger by adding special characters and create a password that ranges 8 to 20 characters.
- Explain try/except, when would except occur?
A try/except block is used in Python to catch and handle errors or exceptions that occur during the execution of a program. The code inside the try block is executed normally, and if an exception is raised, the code inside the corresponding except block is executed to handle the exception. It happens due to syntax errors, a type error, or a runtime errors. The except block will occur when an exception is raised during the execution of the try block.
- What code seems to be repeated in each of these examples to point, why is it repeated?
Code that seems to be repeated in each of these examples is the conn in and conn out. It is repeated because it is the function that creates a connection to the SQLite table and returns a SQLite connection object. In other words, it is the function that connects the SQLite table and the user ID, names, passwords, and dates of birth.
import sqlite3
def update():
uid = input("Enter UID 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 getting 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?
I think the DELETE function is a dangerous operation certain times, however, not all times because in some coding situations it is neccesary. For this instance, since we are working with users and adding, updating, and deleting users, the DELETE function is neccesary. Therefore, the DELETE function is neccessary at some times, but not needed and dangerous at some times.
- In the print statemements, what is the "f" and what does {uid} do?
In the print statements, the "f" refers to the order and to print the user ID number first. The {uid} function refers to the user ID number being printed. It tells the code to print the user ID number along with the corresponding message the code prints with the uid code and "f" function.
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 specific functions that are needed no matter what for the Python code to function and work properly. The menu repeats the import sqlite3 function in order to import the sqlite3 functions and table with users. Another function that is repeated in the menu continuously is the def function, the def function is repeated each code cell and is followed by a different function depending on the purpose of the code cell.
- Could you refactor this menu? Make it work with a List?
Yes, one can refactor this menu and additionally can make it work with a List because this code is built that way. The way the code is written helps and allows one to refactor because of the functions used in the code cell.
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
Completed Hacks
- In this implementation, do you see procedural abstraction?
In this implementation, yes I see procedural abstraction. To start off, procedural abstraction provides a name for a process and allows a procedure to be only knowing what it does not, how it does it. In this lesson, since we used the Create, Read, Update, and Delete, we used four different functions for different operations. With the use of CRUD, users can reuse the CRUD functions several times with the other users too.
-
In 2.4a or 2.4b lecture
-
Do you see data abstraction? Complement this with Debugging example.
Yes, I see data abstraction being used in the code because users cannot see any of the CRUD functions which are implemented almost invisibly.
-
Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
-
