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 blueprint that defines the structure of a database. It describes the organization and interrelationships among the various data elements or tables that make up the database.

  • What is the purpose of identity Column in SQL database? The purpose of an identity column is to provide a unique identifier for each row in the table, without requiring the user to manually specify a value for that column.
  • What is the purpose of a primary key in SQL database? A primary key is a column or set of columns that uniquely identifies each row in a table.
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? A connection object is an object that represents a connection to a database. It is used to establish and manage a connection to a database and allows the programmer to execute commands and retrieve data from the database.
  • Same for cursor object? A cursor object is an object that provides a way to iterate over the rows of a result set returned by a SQL query. It allows the programmer to fetch data from a database row by row, and perform operations on each row as it is retrieved.
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object? The attributes that are in the conn object include special variables, function variables, class variables, in_transaction, isolation_level, row_factory, and total_changes. The attributes taht are in the cursor object include special variables, function variables, arraysize, connection, description, lastrowid, row_factory, and rowcount.
  • Is "results" an object? How do you know? Yes it is an object because it is set equal to cursor.execute()
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()

(1, 'Thomas Edison', 'toby', 'sha256$pES4PKpl7o8oRD3f$f31cf221d36deea5a7af57f0d883706c77ae89059057c3cd6ef72ab429300060', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$2jMSq8cQusKCSWlc$11fd74bc6dd924b331871639c34b6cfa0b347c183dea75f2563dfe19949effd6', '2023-03-18')
(3, 'Alexander Graham Bell', 'lex', 'sha256$zK4iQtllFL1AzO4p$d20a9d6f54983a2234d23e4ec5794c63f4eae4114ed75f552de2c36738acfdc0', '2023-03-18')
(4, 'Eli Whitney', 'whit', 'sha256$RVaL6TtYa9TfcLzG$48227601861a94a94a2c1a17e9621b8c2c1f52fec645d8ba371c17f8abfe4c41', '2023-03-18')
(5, 'Indiana Jones', 'indi', 'sha256$OBkwp54IazY9DKPd$57927f998f7c5f70b332558849437a150abe139f7ce43fe9a4eae62e0b4c1df8', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$u8a39LO3lAUahEit$56e8b1aa5d1da7e382089bab14933034be11253e3dcbd22317f985e7429d2ba4', '1921-10-21')
(7, 'Nathan', 'nshk', 'sha256$aD9kWf4pWYb4jLS7$3b18ffd678525f1776e6de22f8dd700606c2e7b01a47bfc881bcfc58c8a8197c', '2005-12-07')
(8, 'Nathan Kim', 'nsk', 'sha256$26NpqoRE6yBkQosH$154c9afaf2eb582e64050314cf04b5285a94987360bd44bf1d5e0909b9fa662a', '2005-12-07')
(9, '', '', 'gothackednewpassword123', '2023-03-18')
(10, 'Nathan', 'maxwu', 'mdiasojd', '1887-05-19')

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? In the create function of 2.4a the function uses SQLite database to take in the data that the user inputs to create the new users. The create function of 2.4b uses SQLAlchemy to create the new users. 2.4a function can be better used for more simple projects where as 2.4b function can be used for more complex projects.
  • Explain purpose of SQL INSERT. Is this the same as User init? The SQL INSERT statement is used to insert new rows or records into a database table. The User init method is used to create a new user record in the database.
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()

A new user record max has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do? Checks to make sure the length of the new password is at least 2 characters. If it is not then the code presumes that they were hacked
  • Explain try/except, when would except occur? Try/except is used for the errors that could occur. Except would occur if there is an error that did not pass through the try block of code.
  • What code seems to be repeated in each of these examples to point, why is it repeated? Try/except is repeated. This is repeated because there are many different places where checking to ensure everything is inputted correctly is necessary.
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()

The row with user id max the password has been successfully updated

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? DELETE can be dangerous in the case that you delete all of your data and do not have it anywhere else.
  • In the print statemements, what is the "f" and what does {uid} do? f is used to embed expressions inside of string literals. The {uid} is used to input the user id that is being used in the code.
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()

The row with uid max was successfully deleted

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? Because of a recursion. As the function continues, it calls itself, thus the menu is always repeating
  • Could you refactor this menu? Make it work with a List? Yes because recursion leads to stackflow. 
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")

No uid max was not found in the table
A new user record nathan has been created

def menu():
    db_funcs = [ 
            ('c', create),
            ('r', read),
            ('u', update),
            ('d', delete),
            ('s', schema)
        ]
    
    while True:
        operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    
        if len(operation)==0: # Escape Key
            return
        
        found = False
        for func in db_funcs:
            if (func[0]) == operation.lower():
                found = True
                func[1]()
    
        if not found:
            print("Please enter c, r, u, or d") 
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")

A new user record bhargav has been created

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 CRUD concepts show this because the CRUD functions can simply be called.

  • In 2.4a or 2.4b lecture

    • Do you see data abstraction? Complement this with Debugging example. Yes, there is data abstraction in both examples of databases being created. There are classes and objects used to simplify how the data is stored in the database. There are also libraries used, which are imported in order to simplify the code and to allow complex operations to the used without the need for thousands of lines of code.

    • Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

Reference... sqlite documentation