Database Programming is Program with Data

The Tri 2 Final Project is 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?

  • What is the purpose of identity Column in SQL database?
  • What is the purpose of a primary key in SQL database?
  • What are the Data Types in SQL 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?
  • Same for cursor object?
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
  • Is "results" an object? How do you know?
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$NHm2ZEtWToTVLSMI$260527ce3229fff0d8d2580bea6a302a46ed1512a921dd000e3a4eb44d789d52', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$w7pzaJw2DIEnzx9w$f0918afbaab60ee319ebbdc20379bf28e699c2ebe055e9281853e266bf05ed34', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$ZXYmRYccNg2DwioO$f85258931f5e25d051f66149e615c5ac63b40b16aebd263dfb96451951a429fb', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$K5bhBrjZbzc7OpCt$131df92b9b647ccad6d538c37fb9b326a1c2859a7d1e0b4eb75a7a8f317a703f', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$0xWOf4BeQow4di6m$83523e6cf236373e2ef8ac37a8fc55a7b7178f3cfda583aa41f4ccd7283d5a0b', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$2OyGoQSiGNRSRkM6$0ea237269955a10c5e5530a9feeaee8711c90f8a30ccce694dd645a756348e01', '1921-10-21')

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?
  • 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 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?
  • What is the "f" and {uid} do?
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?
  • Could you refactor this menu? Make it work with a List?
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")

(1, 'Thomas Edison', 'toby', 'sha256$NHm2ZEtWToTVLSMI$260527ce3229fff0d8d2580bea6a302a46ed1512a921dd000e3a4eb44d789d52', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$w7pzaJw2DIEnzx9w$f0918afbaab60ee319ebbdc20379bf28e699c2ebe055e9281853e266bf05ed34', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$ZXYmRYccNg2DwioO$f85258931f5e25d051f66149e615c5ac63b40b16aebd263dfb96451951a429fb', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$K5bhBrjZbzc7OpCt$131df92b9b647ccad6d538c37fb9b326a1c2859a7d1e0b4eb75a7a8f317a703f', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$0xWOf4BeQow4di6m$83523e6cf236373e2ef8ac37a8fc55a7b7178f3cfda583aa41f4ccd7283d5a0b', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$2OyGoQSiGNRSRkM6$0ea237269955a10c5e5530a9feeaee8711c90f8a30ccce694dd645a756348e01', '1921-10-21')

Hacks

  • Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
  • Create a new Table or do something new, sqlite documentation
  • In implementation in previous bullet, do you see procedural abstraction?