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?

- It defines how the data is organized and how it can be accessed and modified by users and applications.
  • What is the purpose of identity Column in SQL database?
    • It can be used as a primary key or as a reference in foreign keys to establish relationships between tables.
  • What is the purpose of a primary key in SQL database?
    • It ensures that no two rows have the same values in the primary key column, and it can be used as a reference in foreign keys to establish relationships between tables.
  • What are the Data Types in SQL table?
    • Common data types include integer, real, text, and blob, and each has a specific range of values and storage requirements.
import sqlite3

database = 'instance/hw.db' # this is location of database


def hw_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('homework')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()
    
hw_schema()

(0, 'id', 'INTEGER', 1, None, 1)
(1, '_teacher', 'VARCHAR(255)', 1, None, 0)
(2, '_uid', 'VARCHAR(255)', 1, None, 0)
(3, '_password', 'VARCHAR(255)', 1, None, 0)
(4, '_hw', 'VARCHAR(255)', 1, 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?
    • It provides methods for connecting to a database, executing SQL statements, and managing transactions.
  • Same for cursor object?
    • It provides methods for navigating the result set, fetching rows, and executing SQL statements.
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
    • Connection object:- database: the name of the connected database - user: the name of the user used to connect to the database
      • host: the name of the host or IP address of the database server
      • port: the port number used to connect to the database server
    • Cursor object:
      • description: a list of tuples that describes the columns in the current result set
      • rowcount: the number of rows affected by the last SQL statement
  • Is "results" an object? How do you know?
    • Yes because it has attriubutes
import sqlite3

def hw_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 homework').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()
    
# hw_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?
  • Explain purpose of SQL INSERT. Is this the same as User init?
import sqlite3

def hw_create():
    Teacher = input("Enter your name: Ms/Mr (blank)")
    uid = input("Enter your user id:")
    homework = input("Enter the homework")
    
    # 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 hw (_name, _uid, _homework) VALUES (?, ?, ?)", (Teacher, uid, homework))
        
        # 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()
        
# hw_create()

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?
  • What code seems to be repeated in each of these examples to point, why is it repeated?
import sqlite3

def hw_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?
  • In the print statemements, what is the "f" and what does {uid} do?
import sqlite3

def hw_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?
    • recursion
  • Could you refactor this menu? Make it work with a List?
    • To refactor the menu and make it work with a list, you could define a list of tuples that map each operation to a function, like this:
def menu():
    options = ['c', 'r', 'u', 'd', 's']
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    operation = operation.lower()

    if operation in options:
        index = options.index(operation)
        functions = [hw_create, hw_read, hw_update, hw_delete, hw_schema]
        functions[index]()
    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")

def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        hw_create()
    elif operation.lower() == 'r':
        hw_read()
    elif operation.lower() == 'u':
        hw_update()
    elif operation.lower() == 'd':
        hw_delete()
    elif operation.lower() == 's':
        hw_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, 'Mr. Mortensen', 'bigmort123', 'sha256$kGommW6W5TJ3p3dO$0475451392d5ab06e6428a94b5e92fdd6927e976b031b098c8dce7f231c69b1e', 'Hacks for 2.4a and b')
(2, 'Ms. Calicot', 'ilovechem', 'sha256$hJDevADSaOeip72W$b77661397759b6834b26c345f30d9876faae9cb7a4323b3bb9e07cdf863b8e74', 'Compound Worksheet')
(3, 'Mr. Curry', 'CurrysWorld', 'sha256$Sib0Zs3iVLkRAbtr$7204866d80e2ce0aad847bb62f0cd79c439f185fd99c00b34db0b097373be5e4', 'Russia Worksheet')
(4, 'Ms. Boehm', 'englishrocks', 'sha256$v1RceQM5cOD9vdeF$634d4584d5faee7c0f0bd03c944fc20def099da8f3569ba0d7a9686b75224a45', 'Logical Fallicies Worksheet')
(5, 'Ms. Huang', 'mathontop', 'sha256$Tb6sRPjo7mvixAZs$643d7dbb399b4849902dd8c6244b4b5c347a664e902ffdb89df9c3d22f05af34', 'Problems 7-45 to 7-50')
(6, '', '', 'sha256$JzNyE8rRDTDDHUIo$4a0b6280df7c452d9750eb788a9e21baf35a390091965fa40100808cae6d8bdb', '')

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.
      • If you debug the menu, and step into one of the hw_functions, it will show the code for them, which abstracts more code itself
    • Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

Reference... sqlite documentation