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 the outline and the blueprint of all tables and associated attributes within the database.
-
What is the purpose of identity Column in SQL database? The identity column in SQL database is a numeric column that can be automatically populated with numeric values.
-
What is the purpose of a primary key in SQL database? The primary key in a SQL database serves to act as an identifier for a particular database entry. The values in this column must be unique and non-NULL. Similar to the identity column, each table in a database can only have 1 primary key.
-
What are the Data Types in SQL table? The data types in SQL table could be integers, string, boolean, float.
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 the connection to an open SQLite database. It contains all the attributes and methods that we may use to alter and manipulate the database.
-
Same for cursor object? A cursor object represents and invokes methods to execute SQL queries on a database. Using it we can fetch and alter database entries in our DB.
-
Look at conn object and cursor object in VSCode debugger. What attributes are in the object? Looking at the VSCode debugger, we can see that these objects contain certain special variables function variables and class variables for the Connection object. We can also see that it represents many of the methods we use to modifiy the database, such as
execute()and accessing datafetchall() -
Is "results" an object? How do you know? "results" is an object because we can see that it contains many attributes and methods of it's own in the debug menu
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? In the OOP lesson, the create() object relies on the instantiated object to extract data to upload to the database. However, in the Imperative approach, we directly edit the Database with SQL queries. From a logistic point of view, the OOP method should be better than imperative approach since it could share attributes and arguments with the other CRUD elements as well.
- Explain purpose of SQL INSERT. Is this the same as User init? SQL INSERT serves to create a new database entry within the table. On the other hand, User.init() in the OOP approach serves to instantiate an object to be added to the database. They are not the same things
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 essentially serves a joke warning against the user when they create an insecure password lesser than 2 characters. Additionally, it also sets a new password for the user
-
Explain try/except, when would except occur? An except would occur if our UPDATE query were to fail to execute. While completing the hacks, I have encountered such a case where my database was locked, thus preventing me from updating and invoking the except block.
-
What code seems to be repeated in each of these examples to point, why is it repeated? Within every code block, we are repeating the initializaion of the connection and cursor objects. This is repeated because of a programming idea called "scope". Each time we define these objects, they only exist within the function, and not elsewhere, so we must redefine them if we wish to use them again.
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 could be a dangerous function. This is because it contains the ability to drop entire database entries with no recovery. This is especially dangerous in certain attacks, such as SQL Injects, where a malicious attacker could potentially inject SQL fragments to delete other uninteded entries, causing havoc.
- In the print statements, what is the "f" and what does {uid} do? The f represents a formatted a string, while {uid} documents a variable to be inserted into the string, where uid is the name of the variable
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? This menu repeats itself until the escape key is pressed because it calls itself again repeatedly via recursion
- Could you refactor this menu? Make it work with a List? We could refactor this menu, by changing it such that we include a list of possible options that the user could select from, instead of having repeated if and elif statements.
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