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 the organization of data as a blueprint of how the database is constructed.

  • What is the purpose of identity Column in SQL database?: The columns in a database contain the attributes of the class that all the entries in the database have.
  • What is the purpose of a primary key in SQL database?: A primary key is something that can be used to identify and object or entry in the database. In our case, the uid is unique and can therefore be used to identify a specific user in the database.
  • What are the Data Types in SQL table?: Some of the possible data types in an SQL table include strings, integers, floats, and images.
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 allows the programmer to connect with the database and interact with it. This way the programmer can search for things, display things, and do so much more with the database.
  • Same for cursor object?:After connecting to the sqlite database, the cursor object allows us to search through the database and fetch for different parts of it.- Look at conn object and cursor object in VSCode debugger. What attributes are in the object?: After using the VSCode debugger, I found that the attributes in the conn object are special variables, function variables, and class variables. Some of the attributes of the cursor object are special variables, function variables, connection, and description.
  • Is "results" an object? How do you know?: Yes, results is an object listed under the cursor object. It's an object because it contains data and attributes (like the conn and cursor objects) of each user.
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$7jEE87mtQgvSbeuM$abb57b85c00ce0521cfdc5eb608c5fb81261870d13c385aef7ec6568e4448c84', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$4e7B1Lpff76p9BRQ$f1818bd9f94b62d7c6d6e2f3426a0234cd4f6e8b27b0ee8a86d8edcac6cf1ece', '2023-03-17')
(3, 'Alexander Graham Bell', 'lex', 'sha256$V9ZT426BhF6ghSUQ$c538c8d51513e41b1f1ede1d99d922004d7e56a77695a0f7c9cce9ec2ada0efb', '2023-03-17')
(4, 'Eli Whitney', 'whit', 'sha256$On8knJWX1jTqCukb$4efbba10d347afb5498a1b747280c3e6a5bdeec98171b460441389deb48399aa', '2023-03-17')
(5, 'prasad', 'sachitcool1', 'sha256$0uPDaRZ6tYyJHQeS$4a4e2939f2d7c20c6385fec2805b4891cf0b8d5764add9612240fd9e766eb698', '1920-10-21')

Create a new User in table in Sqlite.db

Uses SQL INSERT to add row

  • Compare create() in both SQL lessons. What is better or worse in the two implementations? In both versions, the create function would gather inputs for each attribute of the object from the user and add it to the database a new entry. However, the method in which the program does this is different in the 2 lessons. In the 2.4a lesson, the program used user.create() to create a user, while the 2.4b lesson used cursor.execute and conn.commit() to create a user. The code in 2.4b looks simpler and more compact than the code in 2.4a.
  • Explain purpose of SQL INSERT. Is this the same as User init?:SQL INSERT takes the values inputted by the user for name, uid, password, and dob and inserts them into a list which represents an entry in the database. It is used to insert these attributes into the database as a new entry.
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 sachitprasad123 has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?:If the password inputted by the user is shorter than 2 characters, the password is changed to "gothackednewpassword123" as it's a longer and therefore more secure password. The message hacked will also displayed showing that the user selected by the user wasn't secure, so they got hacked.- Explain try/except, when would except occur?: Try except blocks are generally used to check for errors. If the try block doesn't work as intended, the except block runs which usually states that an error occurred with the process being run in the try block.
  • What code seems to be repeated in each of these examples to point, why is it repeated?: In all of these examples, the connect and cursor objects are used to allow us to connect and interact with the database. Without these, we wouldn't be able to access and use use the CRUD operations on the database to edit it. This is why this process is repeated whenever a change (create, update, delete) is needed to be made to the database.
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()

No uid  was not found in the table

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 is a dangerous operation because once an entry is deleted, it can't be brought back and is lost. This is why it's important to make sure you are deleting the correct entry whenever you use this CRUD operation.- In the print statements, what is the "f" and what does {uid} do?: The "f" is used so we can include variables that aren't strings like "{uid}" so we can print them along side strings. The {uid} displays the uid for an entry in the table. Curly brackets are used to show the value of uid rather than the word itself.
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()

No uid  was not found in the table

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 to allow the user to select a CRUD operation as many times as they wish until they feel they are satisfied.- Could you refactor this menu? Make it work with a List?: This menu could be changed to work with a list by storing the different crud operations in a list rather than as in if and elif statements.
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")

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, lots of procedural abstraction can be seen in this implementation. The CRUD functions (create, read, update, and delete) divide the program into parts and categorize it by what each part does. Additionally, the menu function basically combines all the functions into one function allowing you to do any CRUD function just by running one code segment. These elements also make it easier to read the program as all the code is in a specific function which is labeled with its purpose rather than a huge block of code where nothing is organized.

  • In 2.4a or 2.4b lecture

    • Do you see data abstraction? Complement this with Debugging example.

      Yes, data abstraction is present in both 2.4a and 2.4b as the code is condensed and its internal functions are hidden from the user. When the user wants to create, read, update, or delete from the database, the user simply needs to input a few things at most to get the desired result. In this way, the internal functions are hidden as the user doesn't know what is happening behind the scenes in the program. Also, the code is condensed as everything has a purpose and all the code is grouped well and put where it should be located.

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

Reference... sqlite documentation

Debugging Screenshots Showing Understanding of Key Objects

Conn Object

Conn Object

Cursor Object

Cursor Object