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 is the categories in th table with the required types of input.

  • What is the purpose of identity Column in SQL database? The Id column helps make it easier to access the data by classifying is all under one id.
  • What is the purpose of a primary key in SQL database? The primary key in SQL databases is supposed to be unique for each row.
  • What are the Data Types in SQL table? The data types in SQL table are integer, string, and boolean, floats, dictionaries, lists
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('football')").fetchall()

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

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

# this makes the schema table from the sqlite database

(0, 'id', 'INTEGER', 1, None, 1)
(1, '_name', 'VARCHAR(255)', 1, None, 0)
(2, '_number', 'INTEGER', 1, None, 0)
(3, '_wins', 'INTEGER', 1, None, 0)
(4, '_losses', 'INTEGER', 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? A connection object is a way to connect to the SQLite database.
  • Same for cursor object? Cursor objects lets use run procedures in the SQLite database.
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object? The conn object connects to the SQLite database. And the cursor object basically extracts things and interacts with the database.
  • Is "results" an object? How do you know? It is an object because it consists of data.
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 football').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()

# This reads the data in the sqlite database

(1, 'Patrick Mahomes', 15, 64, 16)
(2, 'JJ Watt', 99, 77, 74)
(3, 'Russell Wilson', 3, 108, 64)
(4, 'Travis Kelce', 87, 105, 39)
(5, 'Joe Burrow', 9, 24, 17)
(6, 'Trevor Lawrence', 16, 12, 22)
(7, 'Kaiden Do', 50, 100, 0)

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? The better one is the one in this implementation. This is because this is object oriented programming. This allow the program to be more easily organizable with a very large amount of data. While imperative programming is better usually with smaller amounts of data because it is less complex and more straight forward.
  • Explain purpose of SQL INSERT. Is this the same as User init? The SQL INSERT is basically putting the new data into the data set. It is not the same as User init because that adds preset data.
import sqlite3

def create():
    name = input("Enter your name:")
    number = input("Enter your number:")
    wins = input("Enter your wins:")
    losses = input("Enter your losses:")
    
    # 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 football (_name, _number, _wins, _losses) VALUES (?, ?, ?, ?)", (name, number, wins, losses))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new player record {name} 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 player record Kaiden Do has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do? The hacked part sees if the password has a length greater or equal to 2. To ensure security.
  • Explain try/except, when would except occur? The try/except is first testing the code then the except is handling errors. The except would occur when there is an error regarding the SQLite database.
  • What code seems to be repeated in each of these examples to point, why is it repeated? The conn and cursor definition is being repeated in each of these examples. It is repeated to connect to the SQLite database because the connection is removed at the end of the method. Without a connection, the database wouldn't be changed or viewed.
import sqlite3

def update():
    name = input("Enter name to update")
    number = input("Enter updated number")
    wins = input("Enter updated wins")
    losses = input("Enter updated losses")

    # 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 football SET _number = ? WHERE _name = ?", (number, name))
        cursor.execute("UPDATE football SET _wins = ? WHERE _name = ?", (wins, name))
        cursor.execute("UPDATE football SET _losses = ? WHERE _name = ?", (losses, name))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No name {name} was not found in the table")
        else:
            print(f"The row with user id {name} the password has been successfully updated")
            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? It is dangerous because the data deleted is gone forever and it cannot be retrieved again. Also if people get a hold of the database, all of the data can be wiped.
  • In the print statemements, what is the "f" and what does {uid} do? The "f" is the fstring and this allows the print to have access to the data or lists. So when it is printed the print would display the value of the {expression}. The {uid} would show the userid of the user for the operation.
import sqlite3

def delete():
    name = input("Enter player name 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 football WHERE _name = ?", (name,))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No name {name} was not found in the table")
        else:
            # The uid was found in the table and the row was deleted
            print(f"The row with name {name} 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 because of recursion. The menu() function is called at the end of the menu() itself.
  • Could you refactor this menu? Make it work with a List? You could refactor the menu by modifying the C, R, U, D portions to merge the if statements by making it under a single line. I am pretty sure it can work with a list by putting the letters or the functions in a list. And iterating it if needed.
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")

A new player record Kaiden Do has been created
(1, 'Patrick Mahomes', 15, 64, 16)
(2, 'JJ Watt', 99, 77, 74)
(3, 'Russell Wilson', 3, 108, 64)
(4, 'Travis Kelce', 87, 105, 39)
(5, 'Joe Burrow', 9, 24, 17)
(6, 'Trevor Lawrence', 16, 12, 22)
(7, 'Kaiden Do', 50, 0, 0)
The row with user id Kaiden Do the password has been successfully updated
The row with name Kaiden Do was successfully deleted
(1, 'Patrick Mahomes', 15, 64, 16)
(2, 'JJ Watt', 99, 77, 74)
(3, 'Russell Wilson', 3, 108, 64)
(4, 'Travis Kelce', 87, 105, 39)
(5, 'Joe Burrow', 9, 24, 17)
(6, 'Trevor Lawrence', 16, 12, 22)
(0, 'id', 'INTEGER', 1, None, 1)
(1, '_name', 'VARCHAR(255)', 1, None, 0)
(2, '_number', 'INTEGER', 1, None, 0)
(3, '_wins', 'INTEGER', 1, None, 0)
(4, '_losses', 'INTEGER', 1, None, 0)

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?
    • I see procedural abstraction in the create(), read(), update(), delete(), and schema() functions. These functions are called in the menu command. This allows a more compact and efficient way of programming.
  • In 2.4a or 2.4b lecture
    • Do you see data abstraction? Complement this with Debugging example.
      • There is data abstraction. Because the data is stored in a database and is called and being used throughout the program.
    • Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

Reference... sqlite documentation