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?
- The structure of the database with the data organized into a blueprint.
- What is the purpose of identity Column in SQL database?
- Key values are created in identity columns and provide for the identification and uniqueness of rows.
- What is the purpose of a primary key in SQL database?
- gives each row a special value that makes it possible to identify it.
- What are the Data Types in SQL table?
- virtually any data type, including strings, text, pictures, lists, and dictionary classes.
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('NFL')").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?
- An individual session with a data source is represented by a connection object. Its goal is to establish a live link with a data source.
- Same for cursor object?
- It is utilized to establish a connection for SQL query execution.
- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- Both of them contain a large number of special and function variables.
- Is "results" an object? How do you know?
- As a result of the data and the variables it includes, "results" is an 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 NFL').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?
- When the SQL database is tiny, this creation is better and can perform more functions, but it is substantially poorer. Because it performs better with less data in this instance, the alternative option is preferable.
- Explain purpose of SQL INSERT. Is this the same as User init?
- This initializes the user object using the data while the User init enters data into the table.
import sqlite3
def create():
name = input("Enter your player's name:")
uid = input("Enter your player's user id:")
position = input("Enter your player's position:")
team = input("Enter your player's team:")
number = input("Enter your player's jersey number:")
# 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 NFL (_name, _uid, _position, _team, _number) VALUES (?, ?, ?, ?, ?)", (name, uid, position, team, number))
# 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?
- When the password is fewer than two characters, the hacked portion is visible. The passwrod is changed to a new one.
- Explain try/except, when would except occur?
- Coders can test error code using try and except. If there is a try error, except happens.
- What code seems to be repeated in each of these examples to point, why is it repeated?
- Conn and cursor definitions appear to be redundant, and cursor uses the INSERT, UPDATE, DELETE, and SELECT SQL procedures.
import sqlite3
def update():
uid = input("Enter your player's user id:")
position = input("Enter your player's position:")
team = input("Enter your player's team:")
number = input("Enter your player's jersey number:")
# 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 NFL SET _position = ?, _team = ?, _number = ? WHERE _uid = ?", (position, team, number , 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}")
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 is a risky operation because if used incorrectly, it can completely erase all of the database's records and data.
- In the print statemements, what is the "f" and what does {uid} do?
- It is an f-string, or string format, because f is used. The placeholder "uid" denotes the variable that will be utilized within the string.
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 NFL 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?
- Because it is a source of recursion, the menu repeats itself. Until the user decides to stop, it remains working and doing the tedious processes.
- Could you refactor this menu? Make it work with a List?
- Loops could be used to reorganize this menu, and a list might even work.
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
Because it makes the representation of the entire database simpler, data abstraction is used in lesson 2.4a. Objects are utilized within the classes in the database to streamline the data. Like name and uid, every component of the user is an object. Also, it becomes clear during code debugging. Both local and global class variables as well as objects are utilized.
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 NFL').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()