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

Explore SQLite Connect object to establish database connection- Explore SQLite Cursor Object to fetch data from a table within a database

Schema of Users table in Sqlite.db

Describe Schema, here is resource Resource

What is a database schema?

A database schema defines how data is organized within a relational database
What is the purpose of identity Column in SQL database?

Identity columns can be used for generating key values. The identity property on a column guarantees the following:Each new value is generated based on the current seed & increment.
What is the purpose of a primary key in SQL database?

A primary key is the column or columns that contain values that uniquely identify each row in a table. A primary key is needed:To extract or archive data from a table that is visited more than once during a process
What are the Data Types in SQL table?

String, boolean, integer, float

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 instance of a class in a programming language that represents a connection to a database or a network resource. It is typically used to execute queries, transactions, and other operations on the database or resource.
Same for cursor object?

A cursor object is an instance of a class in a programming language that is used to traverse and manipulate the records returned from a database query. In other words, it is a mechanism for iterating over the result set of a database query and processing the individual rows returned.
Look at conn object and cursor object in VSCode debugger. What attributes are in the object?

The attributes in the conn and cursor objects in a VSCode debugger session depend on the specific type of object being used, as well as the database driver being used to connect to the database. The conn object has a database, host, port, user, password, autocommit, isolation_level, closed. The cursor object
Is "results" an object? How do you know?

"Results" can be an object in certain contexts, but it depends on the specific context in which the term is being used. In general, an object is a data structure that stores data and provides methods for accessing and manipulating that data. Depending on the context, "results" could refer to a specific data structure that stores the outcome of a computation or experiment, such as the results of a scientific study or the results of a search query on a website.

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$WOTYUzh25UQ3KLyO$2261f088c6f40028429bceec524bdea26b1146eaa8213364ac5e44fbb14bc429', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$cBqqDPsIoAk80rOv$f5bd4c4a6c8317dad8273140a14638d44e4fe8d3201d8a7fce800a327af63874', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$zjCcOdS8mDSbVxVG$42130b9517c0424a1d65cd0a2608ee404f308a5c62d5d226294c2807a7cbc557', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$DQwoCCCv4qqyGUMS$009c668366039f21d8d05944c0020a5b417f4587172a96201593886adad67e1f', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$6t67LoVg2Qy2hLNT$edb03fa214deb5544181cfb29e930f02f5828eb370ba3bedd41ef1037d38b134', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'gothackednewpassword123', '1921-10-21')
(8, 'Bob Ross', 'bob', '14444444444444', '1946-10-29')
(9, 'John Yu', 'john', 'sha256$WFIk5zyz9KWZYuIy$fe11f1c15fae7ef5f47453d32abd34663edd14e2b5485e760a642b85ddc6d881', '2008-09-12')

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

A new user record will has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

What does the hacked part do?

It checks if how long the password is and if it is shorter than two digits that means it is easier to hack so that is why for security to see if your password can get hacked.
Explain try/except, when would except occur?

try and except are keywords used in Python programming language to handle exceptions, which are unexpected errors that occur during the execution of a program. The try block contains the code that may raise an exception, and the except block contains the code that handles the exception if it is raised.
What code seems to be repeated in each of these examples to point, why is it repeated?

It is repeated because users are recursive.

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 will the password has been hacked

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?

It is a dangerous operation because the try block contains the code that may raise an exception, and the except block contains the code that handles the exception if it is raised.
In the print statemements, what is the "f" and what does {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()

The row with uid 134 was successfully deleted

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

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

In this implementation, do you see procedural abstraction?

Yes, this implementation uses procedural abstraction by encapsulating each database operation into separate functions (create, read, update, delete, schema). This allows for each function to be reused and modified independently of the others, making the code more modular and easier to maintain. The menu function also provides a simple interface to access each of these functions, further abstracting away the implementation details.

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

Yes, I can see that the algorithm uses data abstraction. For instance, the user is not shown the implementation specifics of the database actions in the create(), read(), update(), and delete() methods. As opposed to this, the user engages with the functions at a higher level of abstraction, sending inputs and getting results without having to be aware of the precise mechanics of how the database is being accessed or changed.

In terms of debugging, a straightforward illustration would be to add print lines at different locations throughout the code to assist in locating potential error locations. For instance, we could add print statements to the update() method to verify that the user-inputted numbers for uid and password are being read properly. It shows in the following above image how the program goes through each of the lines and makes sure to print all the rows.

Object Orientation vs Imperative

Object-oriented programming (OOP) and imperative programming are both popular programming paradigms, each with its own advantages and disadvantages. Here are some key differences between the two:

Focus: Imperative programming focuses on how to achieve a specific task, while OOP focuses on the creation and manipulation of objects.
Data Structures: In imperative programming, data structures are often simple arrays or structures, while in OOP, they are typically more complex objects with their own attributes and methods.
Inheritance: In OOP, objects can inherit properties and methods from other objects, allowing for code reuse and increased modularity. In imperative programming, code is often duplicated or reused by copying and pasting.

Overall, OOP is often seen as more organized, modular, and reusable than imperative programming. However, imperative programming can be more efficient for certain tasks, particularly those that involve a lot of low-level manipulation of data. Ultimately, the choice between the two will depend on the specific needs of the project and the preferences of the programmer.

My CPT Addition

Therefore, I prefer OOP much more and decided to use it to input more features into my data table.

Unit 2.4b Using Programs with Data, SQL