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

Schema of Users table in Sqlite.db

Uses PRAGMA statement to read schema.

Describe Schema, here is resource Resource- What is a database schema?

  • structure or blueprint of a database that defines how the data is organized, stored, and accessed.
    • What is the purpose of identity Column in SQL database?
  • Columns are each record in the database
    • What is the purpose of a primary key in SQL database?
  • primary key is the integer that describes the column, however we normally use a uid
    • What are the Data Types in SQL table?
  • Integers, dates.
import sqlite3

database = 'instance/sqlite1.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()

(0, 'id', 'INTEGER', 1, None, 1)
(1, '_name', 'VARCHAR(255)', 1, None, 0)
(2, '_uid', 'VARCHAR(255)', 1, None, 0)
(3, '_password', 'VARCHAR(255)', 1, None, 0)
(4, '_dob', 'DATE', 0, None, 0)

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?
    • I think that the connection object connects the user to the db
  • Same for cursor object?
    • allows user to go to individual rows
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
    • There are many attributes in the debugger menu some things that we can see is the data per user
  • Is "results" an object? How do you know?

    - Results is an object as it has funcitons and variables 

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()

(1, 'Thomas Edison', 'toby', 'sha256$Hy1fgsUrtLQyLFiH$3cc83ddb860b8d0280bd58777f499233b49938f866621f2c2f8d3f89387d21cd', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$P90Vru6IpHL7U63y$006c43d8ab5a908b3efd46b2a7868fc775afb6824d90aa78f397e49b64406fc8', '2023-03-19')
(3, 'Alexander Graham Bell', 'lex', 'sha256$N3WXOyCsMWK2U1aH$12aa4bac51817a5bc267bce2f0ea8e2c0f838b6519f9a89a2f0c8a8380fa346f', '2023-03-19')
(4, 'Eli Whitney', 'whit', 'sha256$tlXlHcOJONoIQBCN$bfe767ff69482666be328ccb0066e22df2211b9b65b5d9e4a6077f6695f58b40', '2023-03-19')
(5, 'Indiana Jones', 'indi', 'sha256$JEr0jrGsiNOUAL5w$c9b2e9f38f5cb16e5159f9a59f55fce471207fe5e2fc95fc55c595ad044280eb', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$2U5NaLM79LSEezIj$2902b375a8637c42351cdac4ba1859d8e3a1eb2f2a32b38eefcebbb08c548041', '1921-10-21')

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?
    • I think that I like the OOP better as I have had more practice with it however the new way of user the cursor method does seem simple

  • Explain purpose of SQL INSERT. Is this the same as User init? 

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()

A new user record  has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?
  • Explain try/except, when would except occur?
    • The except woould happen if there is an error and it will output the error message
  • What code seems to be repeated in each of these examples to point, why is it repeated?
    • The committing is getting repeated
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()

The row with user id 15 the password has been successfully updated

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 beause if it doesnt work properly it could damage the db
  • In the print statemements, what is the "f" and what does {uid} do?
    • The f makes it a format string and prints out the message
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 repats the function by lower the letters as well
  • Could you refactor this menu? Make it work with a List?
    • Yes this could work with a list comparing the input to a function then runnning the function
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")

(1, 'Thomas Edison', 'toby', 'sha256$P6bYSHBhIPCvZes5$d023ab574a8e8f39249dddab0871e504ce70a26d92b951f9075e04b837c2e563', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$EelEuFI8O8AJ0CbA$6c1bf82f03ef89fe77e72e27ba46f4b518b2a9a085ab81b87a236162af65c873', '2023-03-16')
(3, 'Alexander Graham Bell', 'lex', 'sha256$UkCSrTDd0K9frnNA$c49478c9d697425fe7dfebcaf406d1b344fe66059d2983778c993794d0ebef20', '2023-03-16')
(4, 'Eli Whitney', 'whit', 'sha256$wB6WBIi8nm8egabE$1779947084eb70c8ee99953f11ea0edfeb031d07c76e5c8760bfd8dec19ede73', '2023-03-16')
(5, 'Indiana Jones', 'indi', 'sha256$ZT7Ld54b0mCuvHiE$d5352499d56aedc0f99e36e2f0ca222ff3787185e60f16a279c7badc641402e3', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$QSMvq4uxS2B8jOcD$265dde28ad8051e0eb62b0454206120eef8209e77c5bbfe0d1d098914f69bff0', '1921-10-21')
(7, '', '', 'sha256$vE4eNGXDN06y5z9G$6776d90abcaad9a11c422e35814ab99ba8752423375f6ef172c8f3ee82870a02', '2023-03-16')
(8, 'Alex', '15', 'teehee', '2000-15-10')
Please enter c, r, u, or d

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