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?

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

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$lwwd50sSzrKx9gjf$af5090b53c79c5e6932d21c7a335dc5cac028ccb890064a265577ed6c2958bd1', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$QQxmqaRl2itWgXgZ$39c688aa8e693d863680b375ea26630d02413607c388c82d6761e0c2b7399197', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$p6XRxKVlwXfZ8SR3$05c965f4c75d5e6aa1197d564614f1eda8d162cf52e5f3c8a756028632b16d83', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$PklD73XKVg0Veade$66c0da0d5b67586c09fdc35c638aef9d4a8a392d5f50c5c677ec265d09b5b495', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$nxiPTkfR9yzTsgr5$1ab00b8853103b9557e4fc802590deafd076be8587d44ecad1c88243cd2ca1ad', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$OAIQ2877WHWDAbZQ$2a048f7d7205965f2869c2af1f7aa3a733a04ec64418b3a83d77be7324fc32d7', '1921-10-21')
(7, 'Thomas Edison', '', 'sha256$ARUnNf1sR4J6t55P$776ead6e32709ac10fbb6dc40d604388680f36b21ea516f9452bfe50f5ec606f', '1847-02-11')
(8, 'Ishi Singh', 'ishi', 'sha256$Q3Jm2D66Ppo8obBw$14e20f6f5717f3f367a220fae40885f1f4387581f8050233ff8fd9a2cf30459e', '2006-04-27')
(9, 'Jane Doe', 'jane', 'sha256$K0xksGk33HmIbbVN$6b337b8a04c862067980abcb8d98cdc68b1c9344c5a42368a9f6a106d42d318e', '2023-03-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()

Error while executing the INSERT: UNIQUE constraint failed: users._uid

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

The row with uid ishi was successfully deleted

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?