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 plan that defines the structure of a database. It describes the organization and interrelationships among the various data elements or tables that make up the database.
- What is the purpose of identity Column in SQL database?
The purpose of an identity column is to provide a unique identifier for each row in the table, without requiring the user to manually specify a value for that column.
- What is the purpose of a primary key in SQL database?
A primary key is a column or set of columns that uniquely identifies each row in a table.
- What are the Data Types in SQL table?
booleans, integers, strings, blog, imperative programming, OOP
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('periods')").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 represents a connection to a database. It is used to establish and manage a connection to a database and allows the programmer to execute commands and retrieve data from the database.
- Same for cursor object?
A cursor object is an object that provides a way to iterate over the rows of a result set returned by a SQL query. It allows the programmer to fetch data from a database row by row, and perform operations on each row as it is retrieved.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
The attributes that are in the conn object include special variables, function variables, class variables, in_transaction, isolation_level, row_factory, and total_changes. The attributes taht are in the cursor object include special variables, function variables, arraysize, connection, description, lastrowid, row_factory, and rowcount.
- Is "results" an object? How do you know?
Yes it is an object because it is set equal to cursor.execute()
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 periods').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 create function of 2.4a the function uses SQLite database to take in the data that the user inputs to create the new users. The create function of 2.4b uses SQLAlchemy to create the new users. 2.4a function can be better used for more simple projects where as 2.4b function can be used for more complex projects.
- Explain purpose of SQL INSERT. Is this the same as User init?
The SQL INSERT statement is used to insert new rows or records into a database table. The User init method is used to create a new user record in the database.
import sqlite3
def create():
name = input("Enter your name:")
uid = input("Enter your user id:")
ovulation = input("Enter your ovulation date 'YYYY-MM-DD' ")
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 periods (_name, _uid, _ovulation, _dob) VALUES (?, ?, ?, ?)", (name, uid, ovulation, dob))
# Commit the changes to the database
conn.commit()
print(f"A new period 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?
Checks to make sure the length of the new password is at least 2 characters. If it is not then the code presumes that they were hacked
- Explain try/except, when would except occur?
Try/except is used for the errors that could occur. Except would occur if there is an error that did not pass through the try block of code.
- What code seems to be repeated in each of these examples to point, why is it repeated?
Try/except is repeated. This is repeated because there are many different places where checking to ensure everything is inputted correctly is necessary.
import sqlite3
def update():
uid = input("Enter user id to update")
ovulation = input("Enter updated ovulation date")
if len(ovulation) < 4:
message = "invalid date"
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 periods SET _ovulation = ? WHERE _uid = ?", (ovulation, 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 ovulation 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 dangerous in the case that you delete all of your data and do not have it anywhere else.
- In the print statemements, what is the "f" and what does {uid} do?
f is used to embed expressions inside of string literals. The {uid} is used to input the user id that is being used in the code.
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 periods 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 it is recursive.
- Could you refactor this menu? Make it work with a List?
Yes you could make it a list with c, r, u, d, s as the values
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