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

Uses PRAGMA statement to read schema.

Describe Schema, here is resource Resource- What is a database schema? A database schema is a blueprint that defines the structure and organization of a database. It includes the tables, columns, relationships, constraints, and other elements that make up the database.

What is the purpose of identity Column in SQL database? An identity column in SQL database is a special column that automatically generates a unique value for each row inserted into the table. It's commonly used as a primary key to identify and link related records.
What is the purpose of a primary key in SQL database? The primary key in SQL database is a column or set of columns that uniquely identifies each row in a table. It provides a means of linking related records through foreign key relationships.
What are the Data Types in SQL table? Data types in SQL tables are used to define the type of data that can be stored in each column of the table. Numeric, character, date/time, binary, and Boolean data types are the main types used in SQL. Each type has its own format and limitations on the data that can be stored in it.

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

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 object that represents a connection to a database. It provides methods for establishing and managing connections to the database, as well as for executing SQL statements and retrieving data from the database. Based on Google search results, a connection object is used to establish a connection to a database, and it provides methods for executing SQL commands on the database.

Same for cursor object?

A cursor object is an object that represents a database cursor. It provides methods for iterating over the results of a database query, as well as for fetching individual rows of data. Based on Google search results, a cursor object is used to interact with the database and execute SQL commands, and it provides methods for working with the result set returned by a query.

Look at conn object and cursor object in VSCode debugger. What attributes are in the object?

The attributes of a connection object and cursor object in the VSCode debugger may vary depending on the specific implementation and the type of database being used. However, some common attributes of a connection object include closed, database, host, port, and protocol version. Some common attributes of a cursor object include arraysize, description, rowcount, and lastrowid.

Is "results" an object? How do you know?

"Results" is not a generic object, but it could be an object depending on the context. It could refer to the result set returned by a query, which would be an object containing the rows of data returned by the query. Whether "results" is an object or not depends on the specific implementation and context in which it is used.

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

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?
- Better implementations are those that are more efficient, readable, and easier to maintain.
- Worse implementations are those that are less efficient, harder to read, and more difficult to maintain.
Explain purpose of SQL INSERT. Is this the same as User init?
- The purpose of SQL INSERT is to add new rows of data to a database table.
- SQL INSERT allows you to specify the values for each column in the new row or insert values derived from a subquery.
- SQL INSERT is not the same as User __init__ in Python classes, as SQL INSERT is used to modify the data stored in a database table while User __init__ is used to initialize the attributes of a Python object.

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

Explain try/except, when would except occur?

try/except is a control flow statement used in Python to handle exceptions that may occur during program execution. The try block contains the code that may raise an exception, while the except block contains the code that is executed if an exception is raised. The except block is only executed if an exception is raised in the try block. The purpose of using try/except is to gracefully handle exceptions and prevent the program from crashing.

What code seems to be repeated in each of these examples to point, why is it repeated?
Code that appears to be repeated in different examples includes:- Importing the sqlite3 library - Defining the conn and cursor objects
- Using the cursor.execute() method to run SQL commands
- Calling conn.commit() to commit changes to the database
- Closing the cursor and conn objects
This code is repeated because it is necessary to establish a connection to the database and perform SQL commands. cursor.execute() is used to run the desired SQL code, such as update, delete, or read operations. conn.commit() is used to commit changes to the database and make them permanent. Closing the cursor and conn objects is important to ensure that working connections and cursors are closed properly, especially when working with servers, and to prevent unnecessary resource usage.

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?

DELETE is a potentially dangerous operation because it has the ability to remove information from a database. If used incorrectly or in the wrong hands, it can accidentally delete important information. However, DELETE is also necessary for maintaining a clean and organized database with accurate records.

In the print statements, what is the "f" and what does {uid} do?

In the print statements, the "f" before the string allows for formatted string interpolation in Python. The curly braces containing the uid variable indicate that the value of uid should be inserted into the string at that location. This allows for the easy inclusion of variable values in string output without having to concatenate multiple strings.

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

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 there are multiple comparisons for the operations in CRUD. This could be replaced with a match statement or a list.

Could you refactor this menu? Make it work with a List? Yes, it could be refactored with a list for all the different operations

def menu():
    options = {'c': create, 'r': read, 'u': update, 'd': delete, 's': schema}
    operations = {'c': 'Create', 'r': 'Read', 'u': 'Update', 'd': 'Delete', 's': 'Schema'}
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    try:
        choice = options[operation.lower()]
        print(f"Selected option: {operations[operation.lower()]}")
        choice()
    except KeyError:
        if len(operation) == 0:
            return
        else:
            print("Please enter a valid option.")
    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, I implemented procedural abstraction because of the above code blocks. This is a particular example:

def menu():
    options = {'c': create, 'r': read, 'u': update, 'd': delete, 's': schema}
    operations = {'c': 'Create', 'r': 'Read', 'u': 'Update', 'd': 'Delete', 's': 'Schema'}
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")

This shows the use of a dictionary to parse the user input and map it to a specific operation.

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.

See BST Map Abstraction

Reference... sqlite documentation

Unit 2.4B