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 way to orginie the database

  • What is the purpose of identity Column in SQL database? To show what each object contains and be able to identify
  • What is the purpose of a primary key in SQL database? It is unique and allows user to choose a data
  • What are the Data Types in SQL table? Boolean, String, Int, float, etc.
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()

(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? It allows you to have multiple working environment
  • Same for cursor object? It allows you to select a certain object by iterating over table
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object? Table
  • Is "results" an object? How do you know? Yes b/c it is schema
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()

(3, 'Alexander Graham Bell', 'lex', 'sha256$n8JEHHdT6ekPnp3h$c3c7103ceeec4422e40a16ba833790aad08e564934b87d1628d237174ee472b8', '2023-03-20')
(4, 'Eli Whitney', 'whit', 'sha256$7yNBclzSm6s7hP0w$7cdf6b2ee83d5713df14f928176e8d7a5934b27d8da8d3ffb872467cc456178a', '2023-03-20')
(5, 'Indiana Jones', 'indi', 'sha256$cd7Elk31boLcNrBk$3533cb2c5c06571883ae01986811805ed56532016100da2366ebbd5d7fda8198', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$a2faQU75Vy3ws9mh$4d2cd69e110bde578d07c39897b23babd62cd080fb6ca2808a30ad24ab05ac47', '1921-10-21')
(7, 'Test', '123', 'sha256$iTOUNZs1N9pL1xYv$32fddf918445d50eac902efe65eede374d6db90c2b2b237c73c73f1dea05354f', '2008-03-23')
(9, 'colin', '1341', '098', '2007-02-15')
(10, 'Applebees', '491242', 'sha256$7Fy1h5uVjsdBDIAY$b65863090172eb9e2138a49a4cf5fe156b24d24d2b575dc7e98c3e89b0c184fe', '2000-01-01')
(12, '', '11', 'sha256$RRA5TexNmR2BLk6h$1182679d2fd6b6a59abe613e8bab3b3b750f714bfc7907001c110e61dcf7967e', '2023-03-21')
(15, '', '', 'sha256$exWWdG8ycv9hqed0$6a54b53fcf611a677fe12638b6a73cdefdac5b36c4099d800690feee55bb8ef2', '2023-03-21')
(16, 'Thomas Edison', 'toby', 'sha256$1WreqoY5asyDnhXD$11efc8bdb31c6ca80d433190b7a6ca49294bbc365d77fcb4a2b5b86bded9b50d', '1847-02-11')
(18, 'Colin', '213', 'sha256$ltkeNUZoELONNFxX$a1897820a78d726e6cea52269019cacdd089630aad399aae65d67ddca6e67af2', '2007-02-15')
(19, 'Colin', 'col', 'sha256$ppIUASjIJ2SLQYFQ$c6d6d01e280c9b384ce66d2e28ba83714ad112b666d45cd8ab8423533c4b5966', '2007-02-15')

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 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? Prevent too short password
  • Explain try/except, when would except occur? Try tries to run but if it falls except goes
  • What code seems to be repeated in each of these examples to point, why is it repeated? Because some attributes are needed
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 it is permentant
  • In the print statemements, what is the "f" and what does {uid} do? It adds variable to print
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? For the UI can be reset when things change
  • Could you refactor this menu? Make it work with a List? Yes that would be data structure and it's already table
import openai
openai.api_key = "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"

def menu():
    gpt_prompt = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")

    operation = openai.Completion.create(
    engine="text-davinci-002",
    prompt="Interoperate if this statement is asking for (C)reate (R)ead (U)pdate or (D)elete or (S)chema and say the corresponding first letter if it is:" + gpt_prompt,
    temperature=0.5,
    max_tokens=256,
    top_p=1.0,
    frequency_penalty=0.0,
    presence_penalty=0.0
    )
    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")


# 
# Interoperate if this statement is asking for (C)reate (R)ead (U)pdate or (D)elete or (S)chema and say the corresponding first letter if it is: "update please"

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