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
- Menu Interface to CRUD operations
- Hacks
- procedural abstraction
- DATA ABSTRACTION EXAMPLE WITH DEBUGGING IN 2.4b
- score
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?
- What is the purpose of identity Column in SQL database?
- What is the purpose of a primary key in SQL database?
- What are the Data Types in SQL table?
ans
A database schema is how the database is structured and defines how all of the data in the database is organized, like defining the columns in the database for ease of access.
An identity column would be used for values that can be used to identify a object in the database.
a primary key is an identifier of a unit in the database, like user id/login info
some data types include integers, strings, booleans, basically any types can be in a table.
import sqlite3
database = 'instance/sqlite.db' # this is location of database
# 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
def create_table(conn, create_table_sql):
""" create a table from the create_table_sql statement
:param conn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
c = conn.cursor()
c.execute(create_table_sql)
sql_create_comments_table = """ CREATE TABLE IF NOT EXISTS projects (
_uid integer PRIMARY KEY,
_name text NOT NULL
); """
create_table(conn, sql_create_comments_table)
conn.commit()
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?
- Same for cursor object?
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Is "results" an object? How do you know?
ans
a connection object would a way for user or program to connect to database
the cursor object is used to making specific connections to the database to do things like delete users.
It looks like the cursor has special functions for doing specific tasks. Cursor has functions like "fetch all" in its function variables
the conn object looks like the object that enables program to database connection. Conn has functions like "backup" in its function variables.
Results is also an object it has things in it like data in this case.
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()
ans
this create uses conn as method to connect to database and cursor to interact with it. the other create uses objects to store the user while it is being made, and uses a seperate user class to create those user objects. the 2nd implementation is a lot mroe longer and looks harder to read, while this one is more compact. However, the use of oop in the 4a example could have its uses. The 2nd might be better in long term as using objects for storing users may make troubleshooting easier in the future.
SQL Insert is different in that it is used to add new things into our database, and is purely for that purpose, while user init is used to initialize the entire database and all of it's objects, and is therefore much more important.
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()
ans
the hacked part makes it so that the user's password becomes a different one if they set it to be too short, probably to ensure users set protective passwords.
the try and except is used so that one thing occurs, except for when another thing would happen. the except occurs when theres an error with executing sqlite commands or accessing the database.
the conn and cursor are repeated each time, probably because these are neccessary for each function to operate.
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()
ans
the menu repeats because the end of the conditional statement causes the statement to run again. it does break when the program detects a certain input.
the menu could be refactored with a list maybe, if we used key-value pairs, but we would still have to use if statements. a for loop might work as well however.
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
procedural abstraction
this is when we define code blocks or procedures with named variables for specfic usage. with this implementation, specifically in the create function, we have a create function, which we can call upon in our menu code block for whenever we need to make a new user.
DATA ABSTRACTION EXAMPLE WITH DEBUGGING IN 2.4b
when debugging the first code block in this file, we can see an example of data abstraction in the conn variable. This conn variable does data abstraction by storing a bunch of different functions in it for the user to use. this is seen in the 2nd image, when we create a cursor object by using the cursor method of conn. this data abstraction is occuring in that conn hides a bunch of important methods and functions from the programmer/user, so that the user can use the methods in sqlite without having a bunch of different methods to sort through.
score
