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?
Its kind of like the skelton of the database, essentially, how its formatted
- What is the purpose of identity Column in SQL database?
The identity Column in SQL database is in order to aid in identifying different profiles so its easier to differentiate them
- What is the purpose of a primary key in SQL database?
The primary key makes each of the data collected unique preventing any confusions
- What are the Data Types in SQL table?
The data types in SQL table is Numbers, Dates, Time, and Stings (Binary, Character, and e.t.c), and e.t.c.
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
# The PRAGMA table_info command prints out the stuff
results = cursor.execute("PRAGMA table_info('users')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
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 representation of the database and is responsible for connecting to the database in order to modify it using CRUD
- Same for cursor object?
Yes, cursor enables us to use the commands while fetching the data
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
special variables, function variables, class variables, in_transaction, isolation_level, row_factory, and total_changes
- Is "results" an object? How do you know?
Yes, since when we debug it, we can see the same thing that we saw for the conn and cursor objects although they are slightly different but we can safely assume that they are the same.
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?
Imperative:Has less code and is easier to work with than OOPOOP: Has slightly more code and is somewhat harder to work with
Both have their ups and downs but I feel that Imperative makes the most sense but OOP in the end is definetly much more organized and can account for a wide variety of variable and values
- Explain purpose of SQL INSERT. Is this the same as User init?
SQL INSERT puts the profile into the table and its essentially the same thing as 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?
The hacked part recognizes if the passwor is the same and updates the password to be different
- Explain try/except, when would except occur?
Try makes it so that the if and else statement is executed and attempt to find the uid, if there is an error, then the except statement is run and it returns and error msg
- What code seems to be repeated in each of these examples to point, why is it repeated?
The connect and cursor codes seem to repeated since the code needs to co0nstantly set up new objects for these functions and the objects need to close so that the code can end or else it would just keep going on.
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()
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()
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?
Procedural Abstraction is in fact taking place inside this implementation since CRUD is being performed in this implementation across different places in the program
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
Yes, data abstraction is seen when we read the data from the database. We see the data being represented through the SQLite table as well and we use dictionaries also.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation