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?

    • A database schema is a template for the database information to be stored. For example, the schema involves the rows and columns of a database table.
  • What is the purpose of identity Column in SQL database?

    • The identity column in the SQL database is so the objects are different and easily identifiable when trying to iterate through them. If there were no identity column, it would be much harder to successfully complete the CRUD operations.
  • What is the purpose of a primary key in SQL database?

    • The primary key in an SQL database is to uniquely identify the different objects in the rows and columns. If there were no primary key, it would be much harder to identify specific objects in the table.
  • What are the Data Types in SQL table?

    • In an SQL table, there are strings, integers, and date/time.
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()

# THE SCHEMA IS THE METADATA FOR THE DATABSE
# A database schema:
# column in database = part of schema
# rows = each record in database
(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?

    • A connection object is a unique session with a data source. I think that this means that when you run code with SQL, that code's information will show up in the database. This is what creates a conn that connects to the database.
  • Same for cursor object?

    • An SQL cursor object "allows you to retrieve and manipulate rows from a result set one at a time". I think these objects allow for a programer to iterate through the database rows and data and change individual objects and their attributes.
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?

    • The database schema are the attributes in the object (ex:id, name, uid, password, dob, etc.).
  • Is "results" an object? How do you know?
    • Yes, "results" is an object. "results" has variables and functions, and all objects have variables and functions.
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: # database schema = properties and columns in database (NOT keys and values)
        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$hOt456L9G3UrEU19$b29c4532606f53f06933e4be063953a3fadb80093097a7504d6bf3224b53cee3', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$ZMP6epXrzK7TjZWQ$be41e1d7d84d396b043f40aefba9082d08eca28bd177a0781aec6edb7b3a53e9', '2023-03-16')
(3, 'Alexander Graham Bell', 'lex', 'sha256$wnz6ZUWmPUDT7zb7$2a52dd189ef9e60fb7042119c00e1f9ff87531c4303260aec30a8073a7774a52', '2023-03-16')
(4, 'Eli Whitney', 'whit', 'sha256$OCBizDZXnQ7aKhuP$cf478db6994adb329da10609a08957d25e060bebbb26e0b38582bb07eb7a79da', '2023-03-16')
(5, 'Indiana Jones', 'indi', 'sha256$cuRadFJDSJX6R5D3$885dfc93c2b0badb6180ba15a17d94704346553d841dc22213674f72ab9379dd', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$g29fK88JL6CdNFJa$ec7008d02be004d9f1dabbbcf85aaabc9895743b20c8cb04082e156a291fefa3', '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 prefer OOP better for the CRUD operations. To me, the OOP seems more easy to follow and memorize. Although, this method seems to have less code and is more efficient due to avoiding defining a class with setter and getter functions.
  • Explain purpose of SQL INSERT. Is this the same as User init?
    • SQL INSERT is a command that will add data into the database. This function is is written within the code of each line that is being changed/added but the init is called once.
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 hacked code helps with garbage data when someone is updating their password. If they type a password that is less than two characters, the user will get a message that says the have been hacked and will set their password to "gothackednewpassword123".
  • Explain try/except, when would except occur?

    • In this example, when a user is updating their password, the try/except looks for certain conditions. If the user tries to update an unknown user, it will send an unknown uid error. If the password is successfully changed, then the user will be notified. If neither of those are met, then the user will be notified with an alternative error.
  • What code seems to be repeated in each of these examples to point, why is it repeated?

    • Conn and cursor are redefined in every function before every try and except. Cursor.clos() and the conn.close are also redefined. This is likely due to connection objects and the need for the connection to the database to be unique every time.
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?
    • Yes, delete is a dangerous operation because once an item is deleted, it cannot be found again. This operation is permanent.
  • In the print statemements, what is the "f" and what does {uid} do?
    • The "f" and {} form formatted string literals. They allow the uid to be replaced with specific data to the situation.
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 so a user can perform multiple CRUD operations without having to run the code every time. This also allows a user to perform all of their desired edits in one place.
  • Could you refactor this menu? Make it work with a List?
    • To make this menu work with a list, I may be able to define a list that has all of the functions, and use their indices to call them. I am not sure if this would work but it is worth a shot.
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")

# recursion= this will keep running until the escape key is hit and program returns
(1, 'Thomas Edison', 'toby', 'sha256$hOt456L9G3UrEU19$b29c4532606f53f06933e4be063953a3fadb80093097a7504d6bf3224b53cee3', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$ZMP6epXrzK7TjZWQ$be41e1d7d84d396b043f40aefba9082d08eca28bd177a0781aec6edb7b3a53e9', '2023-03-16')
(3, 'Alexander Graham Bell', 'lex', 'sha256$wnz6ZUWmPUDT7zb7$2a52dd189ef9e60fb7042119c00e1f9ff87531c4303260aec30a8073a7774a52', '2023-03-16')
(4, 'Eli Whitney', 'whit', 'sha256$OCBizDZXnQ7aKhuP$cf478db6994adb329da10609a08957d25e060bebbb26e0b38582bb07eb7a79da', '2023-03-16')
(5, 'Indiana Jones', 'indi', 'sha256$cuRadFJDSJX6R5D3$885dfc93c2b0badb6180ba15a17d94704346553d841dc22213674f72ab9379dd', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$g29fK88JL6CdNFJa$ec7008d02be004d9f1dabbbcf85aaabc9895743b20c8cb04082e156a291fefa3', '1921-10-21')
(1, 'Thomas Edison', 'toby', 'sha256$hOt456L9G3UrEU19$b29c4532606f53f06933e4be063953a3fadb80093097a7504d6bf3224b53cee3', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$ZMP6epXrzK7TjZWQ$be41e1d7d84d396b043f40aefba9082d08eca28bd177a0781aec6edb7b3a53e9', '2023-03-16')
(3, 'Alexander Graham Bell', 'lex', 'sha256$wnz6ZUWmPUDT7zb7$2a52dd189ef9e60fb7042119c00e1f9ff87531c4303260aec30a8073a7774a52', '2023-03-16')
(4, 'Eli Whitney', 'whit', 'sha256$OCBizDZXnQ7aKhuP$cf478db6994adb329da10609a08957d25e060bebbb26e0b38582bb07eb7a79da', '2023-03-16')
(5, 'Indiana Jones', 'indi', 'sha256$cuRadFJDSJX6R5D3$885dfc93c2b0badb6180ba15a17d94704346553d841dc22213674f72ab9379dd', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$g29fK88JL6CdNFJa$ec7008d02be004d9f1dabbbcf85aaabc9895743b20c8cb04082e156a291fefa3', '1921-10-21')

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?

    • This imperative style does have procedural abstraction. This code uses recursion. A "recursive query is one that refers to itself", and acts like a while loop.
  • 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

MAKE A MENU TO SHOW ALL CRUD (create read empty delete) EXAMPLE OF MENU ABOVE