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 schema of a database contains the categories, or column names. examples are name, age, dob,
- 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?
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?
- 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?
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 albums').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()
import sqlite3
def create():
name = input("Enter the name:")
artist = input("Enter the artist:")
songs = input("Enter the songs")
streams = input("Enter the streams")
release = input("Enter the release date '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 albums (_name, _artist, _songs, _streams, _release) VALUES (?, ?, ?, ?, ?)", (name, artist, songs, streams, release))
# Commit the changes to the database
conn.commit()
print(f"A new album {name} 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? exposes insecure password that is less than two characters
- Explain try/except, when would except occur? try/except tries a block of code, if an error occurs the execept block runs.
- What code seems to be repeated in each of these examples to point, why is it repeated? success and fail seem to be repeated using if/else or try/except statements to improve user experience by confirming success or fail of request.
import sqlite3
def update():
name = input("Enter name to update")
streams = input("Enter updated streams")
songs = input("Enter updated songs")
# 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 albums SET _streams = ? WHERE _name = ?", (streams, name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
print(f"The row with name {name} the streams has been updated")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE albums SET _songs = ? WHERE _name = ?", (songs, name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
print(f"The row with name {name} the songs has been updated")
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():
name = input("Enter album 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 albums WHERE _name = ?", (name,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with name {name} 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()
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? One example of procedural abstraction in 2.4b is the definition of create, read, update, delete seperately and then all called in a single function at the end.
- In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example. An example of data abstraction in 2.4b: the user is asked for the schema of an album that they want to update, but don't see the backend code in action that actually updates the database.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation