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?

- The structure of the database with the data organized into a blueprint.

What is the purpose of identity Column in SQL database?
- Identity columns are used for generating key values, which enable the rows to be unique and identified.
What is the purpose of a primary key in SQL database?
- Gives a unique value to each row and allows them to be identified.
What are the Data Types in SQL table?
- String, text, images, basically any data type such as lists, dictionaries classes.

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('cars')").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, '_chargetime', 'VARCHAR(255)', 1, None, 0)

(4, '_car', 'VARCHAR(255)', 1, None, 0)

(5, '_dreamcar', 'INTEGER', 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 represents a unique session with a data source. Its purpose is to create an open connection to a data source.
Same for cursor object?
- It is used to make a connection for executing SQL queries.
Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
- They both have many special variables and function variables.
Is "results" an object? How do you know?
- "results" is an object because it contains data and many variables.

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 cars').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', 'Tesla Model Y', '7 Hours', 'Tesla Model X')

(2, 'Nikola Tesla', 'niko', 'Lucid Air','10 Hours', 'Tesla Model S')

(3, 'Alexander Graham Bell', 'lex', 'Tesla Model X', '7 Hours', 'Honda Civic 2020')

(4, 'Eli Whitney', 'whit', 'Tesla Model S', '7 Hours', 'Lucid Air')

(5, 'Indiana Jones', 'indi', 'Tesla Model 3', '7 Hours', 'Tesla Model S')

(6, 'Marion Ravenwood', 'raven', 'NIO ec6', '9 Hours', 'Tesla Model 3')

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?

This create is better and can do more functions but is relatively worse when the SQL database is small. In this case, the other method is better because it works better in less data.

Explain purpose of SQL INSERT. Is this the same as User _init__?

This inserts data into the table while the User init initializes user object with the data.

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 __ has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

What does the hacked part do?

The hacked part appears when the password is less than 2 characters. It sets the passwrod to a new one.

Explain try/except, when would except occur?

Try and except enable coders to test error code. Except occurs if there is an error with try.

What code seems to be repeated in each of these examples to point, why is it repeated?

Defining conn and cursor seem to repeat, and cursor uses SQL commands update, delete, insert, select

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

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 if done incorrectly, it can delete all the data and all the records from the database.

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

The use of f makes it an f-string, or string format. The {uid} is a placeholder and the variable will be used within the string.

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

The menu repeats because it is a source of recursion. It keeps going and does the crud operations until the user decides to stop.

Could you refactor this menu? Make it work with a List?

THis menu could be refracted using loops and it could possibly work with a list.

A new user record has been created

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, this implementation has a lot of procedural abstraction. There are functions created in order to be able to define what procedures we want to do in the code. For example, there are procedures defined for create, read, update, and delete. This allows us to have a broad function which can be used to add information to specific rows of the database.
In 2.4a or 2.4b lecture
- Do you see data abstraction? Complement this with Debugging example.
  - There is data abstraction in this code. There are many things which are defined when creating a user, from their name to password to date of birth. These all need to be stored together, and using the code, we create a specific uid for the user which allows the rest of the data to be called from. The uid is essential in functions like update and delete, where data is removed or changed based on the user-specific uid.
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

Reference... sqlite documentation

Completed Hacks

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

import sqlite3

conn = sqlite3.connect(database)
cursor = conn.cursor()
try:
    cursor.execute("CREATE TABLE charges AS SELECT _name, _uid, _chargetime, _car, _dream FROM cars;")
    conn.commit()
except sqlite3.Error as error:
    print("Error while executing the CREATE:", error)
        
# Close the cursor and connection objects
cursor.close()
conn.close()

This code imports the SQLite3 library, establishes a connection to a SQLite database, and then creates a cursor object. The cursor object is then used to execute an SQL statement that creates a new table called "charges". The new table is being created by selecting certain columns (_name, _uid, _chargetime, _car, and _dream) from an existing table called "cars". The try block is used to execute the SQL statement with the cursor object and commit the changes made to the database, while any errors that occur are caught and printed out. After the changes are committed and the cursor is closed, the connection to the database is ended.

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 charges').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()

('Thomas Edison', 'toby', 'Tesla Model Y', '7 Hours', 'Tesla Model X')

('Nikola Tesla', 'niko', 'Lucid Air','10 Hours', 'Tesla Model S')

('Alexander Graham Bell', 'lex', 'Tesla Model X', '7 Hours', 'Honda Civic 2020')

('Eli Whitney', 'whit', 'Tesla Model S', '7 Hours', 'Lucid Air')

('Indiana Jones', 'indi', 'Tesla Model 3', '7 Hours', 'Tesla Model S')

('Marion Ravenwood', 'raven', 'NIO ec6', '9 Hours', 'Tesla Model 3')

The code opens a connection to a SQLite database file specified by a variable called database. It creates a cursor object to execute SQLite queries on the database. Then, it executes a SELECT query to retrieve all the rows from a table called "charges". It stores the result in a variable called results. It then checks the length of the results list. If the list is empty, it prints a message to the console saying that the table is empty. Otherwise, it iterates over the results list and prints each row to the console. Finally, it closes the cursor and connection objects.

Unit 2.4b Using Programs with Data, SQL