2.4b

• Database Programming is Program with Data
  • Schema of Users table in Sqlite.db
  • Reading Users table in Sqlite.db
  • Create a new User in table in Sqlite.db
  • Updating a User in table in Sqlite.db
  • Delete a User in table in Sqlite.db
• Menu Interface to CRUD operations
• Hacks

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? Describes how objects are organized within a database

• What is the purpose of identity Column in SQL database? Connects the information and gives a label to the object. This can allows us to perform CRUD operations and identify which object we want to modify, create, or delete.
• What is the purpose of a primary key in SQL database? It is automatically populated, and provides a key to identify the object with that cannot be changed by the user or admin.
• What are the Data Types in SQL table? String, numbers, dates

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? Unique session with a data source. I think it creates a unique table which cannot be duplicated. This may be why I could only define my class once, and a change would mean having to give it a new name.
• Same for cursor object? Makes connection to use SQL queries. May facilitate the display of objects in our sql database.
• Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
• Is "results" an object? How do you know? No, it is not defines using the class Client, so it is only a representation of objects in the database

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

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 find imperative programming for straight forward, as it doesnt require the find_by_xxx in order to determine whether there is a duplicate. However, this one only says that there was an error, rather than saying that the object already exists in the database.
• Explain purpose of SQL INSERT. Is this the same as User init? The SQL insert tells the program where to store the user input information. I am not sure about how it related to init, because to my understanding this is not where the object is initialize, though I could be wrong.

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? It displays when the user's passwork they input is not enoguh characters, and therefore redefined the password (hacks the user)
• Explain try/except, when would except occur? Try tells the program to attempt to update the uid according to the product number, while exept tells the user this was unsuccessful. This would occue when the password is not long enough.
• What code seems to be repeated in each of these examples to point, why is it repeated? conn and cursor seems to repeat, as it establishes the connection between the coding lanuages and ensure the SQL and the datavase are connected and running.

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? Once is it completed, it cannot be reversed. This runs risk of losing important information.
• In the print statemements, what is the "f" and what does {uid} do? I think f allows {uid} to be accessed from the function. uid displays uid of the object that was deleted using the function.

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? To allows the user to complete multiple CRUD operations in one go.
• Could you refactor this menu? Make it work with a List? Yes, this can be done. I am not exactly sure how, but I will discuss with my table mates to better understand how to complete this.

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? Yes, procedural abstraction is when a set of data or functions can be used in many different ways or situations. In this implementation, we define objects and then use CRUD operations to manipulate them. We can expand this to more than just CRUD to fit multiple situations. Additionally, I was able to manipulate the code initially used for Users and make it store products for my CPT.
• 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