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
The Tri 2 Final Project is 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 s metadata for a database
- What is the purpose of identity Column in SQL database?
- Columns are part of the schema and identify the parts of the data
- What is the purpose of a primary key in SQL database?
- The id in a database is the primary key, and is the record number. This differentiates the records, convinient when they may have same data.
What are the Data Types in SQL table?
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()
# metadata for database
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?
- Connection object instantiates an object, and connects to our database. It allows our code to interact with the database directly by executing SQL statements.
- Same for cursor object?
- Cursor objects can access individual rows returned by database queries. It keeps track of where the current position of the database is, to organize everything.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- conn object includes variables, as well as 'row_factory', 'isolation_level', and 'in_transaction'. This attributes determines how rows are represented, how a transaction is handled, and whether it is active.
- cursor object includes variables, as well as 'connection', 'description', 'lastrowid', 'row_factory', and 'rowcount'. These attributes basically keep track of the current row, to maintain its position.
- conn object includes variables, as well as 'row_factory', 'isolation_level', and 'in_transaction'. This attributes determines how rows are represented, how a transaction is handled, and whether it is active.
- Is "results" an object? How do you know?
- Results is not an object, but simply the tuples that are returned when fetched by the cursor 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?
- In 2.4a, SQLite is used, taking user input to create users. It is suited for simple projects and simple relationships between the tables. The input is entered into the database using a SQL query. In 2.4b, SQLAlchemy is used, creating new records. It simplifies complex database operations.
- Explain purpose of SQL INSERT. Is this the same as User init?
- While both SQL INSERT and the init method are used to add new data to a database, they serve different purposes. The SQL INSERT command is used to add new rows to a table, while the init method is used to initialize the attributes of a new object of a Python class.
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 if statement in the update() function checks the length of the user-provided password. If the length of the password is less than two characters, it sets the message variable to "hacked" and changes the password to a predetermined value.
- Explain try/except, when would except occur?
- Code that might raise an error is placed inside the try block, and code to handle the error is placed inside the except block. If an error is raised in the try block, the code in the except block is executed, and the program continues to run. In this code, the try block executes an SQL command to update data in a table. If an error occurs while executing the SQL command, the except block catches the error and prints an error message.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- The repeated code in the examples is the code for connecting to the database and creating a cursor object to execute SQL commands. This code helps interact with the database and must be executed before executing any SQL commands.
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 can be a dangerous operation because it permanently removes data from a table, and there is no way to undo this action. If the DELETE statement is not carefully constructed, it can delete more data than intended or delete the wrong data entirely.
- What is the "f" and {uid} do?
- The "f" in the print statements stands for "formatted" string. It allows the creation of strings that contain values that are computed at runtime. {uid} is a placeholder that will be replaced with the value of the uid variable when the string is printed.
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 because the menu() function is called recursively at the end of each operation. Once an operation is completed, the function calls itself to display the menu again and wait for the user to enter another operation.
- Could you refactor this menu? Make it work with a List?
- You can refactor this menu, by setting the list's values as(C)reate (R)ead (U)pdate or (D)elete or (S)chema.
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