Database Programming is Program with Data

The Tri 2 Final Project is 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 schema, or layout, that gives a DB its structure. It allows for the layout of relationships between the elements of tables that make up a DB as a whole.

What is the purpose of identity Column in SQL database?: The identity column allows for a unique identifier for each table row.
What is the purpose of a primary key in SQL database?: The primary key in an SQL DB is used as a column to identify each table row as a unique entry.
What are the Data Types in SQL table?: Integers, strings, and boolean are some of the data types in an SQL table.

import sqlite3

database = 'files/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 used to connect to the DB. This allows for a user to read data from the DB live.- Same for cursor object?: A cursor object allows for the retrieval of data from a table one row at a time.
Look at conn object and cursor object in VSCode debugger. What attributes are in the object?: My debugger doesn't work, but from looking at other people's debuggers, some of the attributes in the object include special variables, function variables, and class variables.
Is "results" an object? How do you know?: Yes, it is an object.

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

Compore create() in both SQL lessons. What is better or worse in the two implementations?:In my opinion, the 2.4a is much more simple to use for the create() function, so it is clear that the create method below in 2.4b can be used for more complex DBs.- Explain purpose of SQL INSERT. Is this the same as User init?: They are similar, as SQL INSERT is utilized to create new records into the DB table, which the user init also sort of does by creating a new user record.

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

What does the hacked part do?:If the length of the updated password is less than 2 characters, it sends you a message that you have been hacked and sets the password to be a more secure password.- Explain try/except, when would except occur?: The except would occur when there is an error in updating data in the DB table.

What code seems to be repeated in each of these examples to point, why is it repeated?: The try/except in each of these examples is repeated, as there are many portions where an incorrectly formatted input can occur, which is why error handling needs to be in place throughout the program.

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?:If your data is not backed up, all of the records from a table can get deleted and set you back.- What is the "f" and {uid} do?: It gives you a confirmation that the uid was found and the record was successfully deleted. "f" in general is used to put an expression inside of a literal string, while the {uid} embeds the user id from the code block into 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()

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 at the end, you can see that it is a recursive function, so it continues to call itself.- Could you refactor this menu? Make it work with a List?: Yes, it can be made into 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.
Create a new Table or do something new, sqlite documentation
In implementation in previous bullet, do you see procedural abstraction?: Yes, procedural abstraction is scattered throughout the implementation. Each of the functions is created and can then be called later on throughout the program for a code block.

In 2.4a or 2.4b lecture:

Do you see data abstraction? Complement this with Debugging example.: Yes, data abstraction can be seen, as each CRUD methodology is working with the given data. My debug isn't working because of my program not working, but if there were to be a create function, data abstraction could be seen by Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

database = 'sqlite:///files/sqlite.db'  # path and filename of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()


db.init_app(app)
""" database dependencies to support sqlite examples """
import datetime
from datetime import datetime
import json

from sqlalchemy.exc import IntegrityError
from werkzeug.security import generate_password_hash, check_password_hash

class FactofDay(db.Model):
    __tablename__ = 'FactDay'  
    
    '''
    The below sets all of the keys that are going to be looked at. The id key is special, as it is the primary key. This is what any sort of PUT and DELETE requests will be passed through if operable.
    '''
    id = db.Column(db.Integer, primary_key=True)
    _fact = db.Column(db.String(255), nullable=False)
    _date = db.Column(db.String(255), nullable=False)
    _year = db.Column(db.Integer, nullable=False )
    
    '''
    This is constructing the fact object and the "_init_" portion is initializing the variables within that fact object. 
    In this case, this is the fact, date, and year variables that are within this object.
    '''
    def __init__(self, fact, date, year):
        self._fact = fact
        self._date = date
        self._year = year
    
    '''
    the following lines 44-75 contain the setter and getter methods. each of the three above variables (fact, date, year)
    are being extracted from the object and then updated after the object is created. 
    '''
    @property
    def fact(self):
        return self._fact
    
    # setting fact variable in object

    @fact.setter
    def fact(self, fact):
       self._fact = fact
    
    # extracting date from object
    @property
    def date(self):
        return self._date
    
    # setting date variable in object
    
    @date.setter
    def date(self, date):
       self._date = date
    
    # extracting year from object
    
    @property
    def year(self):
        return self._year
    
    # setting year variable in object
    
    @year.setter
    def year(self, year):
       self._year = year
    
    '''
    The content is being outputted using "str(self)". It is being returned in JSON format, which is a readable format. This is a getter function.
    '''
    def __str__(self):
        return json.dumps(self.read())
    
    
    '''
    defining the create method. self allows us to access all of the attributes 
    of the current object. after the create method is defined, the data is queried from the DB.
    in this case, since it is the create method, the data is being ADDED, and then db.session.commit() is used
    to commit the DB transaction and apply the change to the DB.
    '''
    
    '''
    here, there is an integrity error "except" statement. db.session would be autocommitted 
    without the db.session.remove() line, and that's something we don't want for the purpose of the project.
    '''
    def create(self):
        try:
            db.session.add(self)  
            db.session.commit() 
            return self
        except IntegrityError:
            db.session.remove()
            return None
    
    '''
    the delete method is defined with the "self" parameter. this method is mainly for certain instances in the DB being 
    garbage collected, and the object kills itself.
    '''

    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
    
    '''
    read method with the self parameter, reading the object with all of the 
    properties: fact, date, and year are being returned.
    '''
    def read(self):
        return {
            "fact" : self.fact,
            "date" : self.date,
            "year" : self.year,
        }

    def update(self, fact="", date="", year=""):
        """only updates values with length"""
        if len(fact) > 0:
            self.fact = fact
        if len(date) > 0:
            self.date = date
        if len(year) > 0:
            self.year = year
        db.session.add(self)
        db.session.commit()
        return self

Unit 2.4b Using Programs with Data, SQL