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

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() ## Getting Schema from databse

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

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

Notes

  • Imperative programming is series of functions
    • Thinking about functionality first
  • OOP is when you have a class and work with objects in that class
    • Thinking about data first
  • Data can be APIs, databases, dataframes with pandas, etc.
  • SQL Alchemy is an abstraction
  • Abstraction is using less code for the same functionality
    • Helps use paradine
    • OOP can be an abstraction
    • Function is an abstraction
    • SQL ALchemy is abstraction
  • Primary key for database is the column
  • You can put a python dictionary directly into a databse through BLOB

  • Connect

  • Cursor

  • Schema
    • I changed files to instance
    • results = cursor.execute("PRAGMA table_info('users')").fetchall() is getting schema from database

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 connections object is an abstraction. It allows you to connect an open database to your computer. We don't know what all of the code doesbut we use it. We can find a database.
  • Same for cursor object?
    • A cursor object helps find things in the connection object.
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
    • Databases, function and data.
  • Is "results" an object? How do you know?
    • Yes because it has functions and 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 users').fetchall() ## Putting code into variable

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

Notes

  • Code blocks look very similar but command in the middle is key command
  • Password is incrypted
  • We are putting the code in

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

Notes

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?
    • The hacked part
  • 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()

Notes

-

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?
    • Yes ebcause you could accidently delete the wrong data forever
  • In the print statemements, what is the "f" and what does {uid} do?
    • The print statement pritns a statement and the {uid} adds the uid to the statement.
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()

Notes

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 was not defined to stop.
  • Could you refactor this menu? Make it work with a List?
    • You could make it work with a list because you could add the different CRUD functions to 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")

Notes

  • This menu is asking us for a prompt
  • When we ask for read it repeats because of a recursion. It keeps running
  • Inside of menu it called menu
    • Recursion
      • Big topic
      • Method calls itself
      • Base case or exit case that allows it to leave

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?
  • 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