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 blueprint or a plan for the database. It defines how the data is organized and how the relations among them are associated. It also defines the constraints on the data, the functions and operations that can be applied to the data.
- What is the purpose of identity Column in SQL database?
- The identity column is a column that is automatically incremented by 1 for each new row added to the table. It is used to uniquely identify each row in the table.
- What is the purpose of a primary key in SQL database?
- The Purpose of a primary key in SQL database is to uniquely identify each row in a table. A primary key must contain unique values, and cannot contain NULL values.
- What are the Data Types in SQL table?
- The datatypes in an SQL table are:
- Integer: Integer is a data type that stores whole numbers.
- Real: Real is a data type that stores floating point numbers.
- Text: Text is a collection of characters stored as a single entity in a database management system.
- Blob: Binary Large Object (BLOB) is a collection of binary data stored as a single entity in a database management system.
- Null: A null value is a special value that indicates that a value is missing or unknown.
- The datatypes in an SQL table are:
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 is used to create a unique session that connects to a database. Which contains all the methods needed to alter, view, and change the data within the database and it is used to create a cursor object that is used to execute SQL statements.
- Same for cursor object?
- A cursor object is an object that is used to execute SQL statements. It is used to fetch data from a table within a database along with other operations.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- The attributes of the conn object are the methods that are used to connect to the database. The attributes of the cursor object are the methods that are used to execute SQL statements.
- Is "results" an object? How do you know?
- Yes, results is an object. It contains data and functions that is related to the object.
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?
- The implementation in the imperative method is simpler to implement and understand because it is more straightforward. The implementation in the object-oriented method is more complex because it is more abstract however requires less code to implement. Due to more complex operations being abstracted away it requires less knowledge of the underlying implementation.
- Explain purpose of SQL INSERT. Is this the same as User init?
- The purpose of SQL INSERT is to add a new row to a table. It is the same as User init because it is used to create the equivalent of a new user object.
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 is if a user's password is less than 2 letters a it seen as a hack and new password is assigned to the user.
- Explain try/except, when would except occur?
- The try/except is used to catch errors that occur in the try block. The except block is executed when an error occurs in the try block.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The code conn.cursor() is repeated in each of these examples to point because it is used to create a cursor object that is used to execute SQL statements. Along with cursor.execute() which is used to execute SQL statements and conn.commit() which is used to commit the changes to the database.
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, delete is a dangerous operations because the deleted data cannot be restored
- In the print statemements, what is the "f" and what does {uid} do?
- Id the password is less than 2 letters then it is seen as a hack and the password is reset to a new password.
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 as it uses recursion to call itself. So that once the function has finished executing it will call itself again.
- Could you refactor this menu? Make it work with a List?
def listMenu():
while True:
operations = ["c","r","u","d","s"]
inputs = input("Enter operation to perform (c)reate, (r)ead, (u)pdate, (d)elete, (s)chema").strip().lower()
if inputs in operations:
if inputs == "c":
create()
listMenu()
if inputs == "r":
read()
listMenu()
if inputs == "u":
update()
listMenu()
if inputs == "d":
delete()
listMenu()
if inputs == "s":
schema()
listMenu()
if inputs not in operations and not KeyboardInterrupt:
print("Invalid operation")
if KeyboardInterrupt:
break
listMenu()
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?
- Yes, as functions are used to hide the details of the implementation to be able hide complex details from the user making the operations simpler to use.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example. Yes, we see data abstraction through the use of the database to store the data within our database and then to be able to perform operations on the data within the database.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
- Program was done in the OOP style.
- Program was done in the OOP style.
Reference... sqlite documentation
Notes
- What is an abstraction?
- An abstraction is veiling something in order to make something simpler such as classes and functions.
- What is SQL?
- SQL or Structured Query Language is a language used to communicate with databases. When using sql you are able to create, read, update, and delete data from a database. SQLAlchemy abstracts commands in SQL however as a result of this we cannot see the actual effects of what happens in SQL itself.
- The most commonly used query in SQL is called select, they will look similar but the command in the middle will be different.