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?

The schema represents the overall structure of the database and how information is organized, stored, and accessed. It provides a blueprint for how the database is designed and organized. Additionally, it defines security rules for accessing and modifying data.

  • What is the purpose of identity Column in SQL database? An identity column is a column that is defined as an automatically incrementing numeric value. The purpose of an identity column is to provide a unique value for each row in a table without the need for manual intervention or input.
  • What is the purpose of a primary key in SQL database? It helps ensure the uniqueness and integrity of the data in the table and it serves as a reference point for other tables that may need to access data in the table.
  • What are the Data Types in SQL table?
  • numeric (int, float, decimal)
  • Characters or strings
  • Date/time
  • Boolean
  • Binary
  • Json and variations
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?
  • Same for cursor object?
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
  • Is "results" an object? How do you know?
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$CDGgRfBeut2uqvEA$f1e92b9fa491fb16d50e148492e38dbd56332215b4039d0f732ddb7a5fc1a26c', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$rT3htR5xILKIGrlD$9ec288fce5d4e74e91a3e332ea4bbf14f126785c4889792a727b6e2de4d79a22', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$Rm0tIs355hyKsUE4$ded47f88d390b5666792a8fe6c2630c0924bd7be0db01e83cd3fad8b39067612', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$ZM292SWazGbDRza4$45bdc855aba573f8c8297f077b67a04d489dcfbed55d185e25c97f91ae6c8156', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$XxGSMzlKittPa9QG$30116de927d4710d5240f9e63f611bfbba75f346631bc47b9b791477382b4837', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$AlPpBn4T2b6ma2hT$910d19c0f14fb4e8571918669594bde6e33efc52646c7323fa06ba72529577cb', '1921-10-21')
(7, 'Azeem Khan', 'azeemk', 'sha256$XpYYrarV0BPEX2Od$12780281a91c041e02bff60fbc88771dfb6eb4745dff9cdda41e6ede450f361f', '2005-09-18')

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

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?
  • Explain try/except, when would except occur?
  • What code seems to be repeated in each of these examples to point, why is it repeated?
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 tester the password has been successfully updated

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?
  • 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 tester was successfully deleted

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? To keep the operation selection process until the user chooses to exit the program.
  • Could you refactor this menu? Make it work with a List? Yes. See the second code block below
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")
(1, 'Thomas Edison', 'toby', 'sha256$CDGgRfBeut2uqvEA$f1e92b9fa491fb16d50e148492e38dbd56332215b4039d0f732ddb7a5fc1a26c', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$rT3htR5xILKIGrlD$9ec288fce5d4e74e91a3e332ea4bbf14f126785c4889792a727b6e2de4d79a22', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$Rm0tIs355hyKsUE4$ded47f88d390b5666792a8fe6c2630c0924bd7be0db01e83cd3fad8b39067612', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$ZM292SWazGbDRza4$45bdc855aba573f8c8297f077b67a04d489dcfbed55d185e25c97f91ae6c8156', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$XxGSMzlKittPa9QG$30116de927d4710d5240f9e63f611bfbba75f346631bc47b9b791477382b4837', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$AlPpBn4T2b6ma2hT$910d19c0f14fb4e8571918669594bde6e33efc52646c7323fa06ba72529577cb', '1921-10-21')
(7, 'Azeem Khan', 'azeemk', 'sha256$XpYYrarV0BPEX2Od$12780281a91c041e02bff60fbc88771dfb6eb4745dff9cdda41e6ede450f361f', '2005-09-18')

Refactored Menu Interface with List

options = [
    ('c', 'Create'),
    ('r', 'Read'),
    ('u', 'Update'),
    ('d', 'Delete'),
    ('s', 'Schema')
]

# Define the menu function
def menu():
    # Display the menu options
    print('Select an option:')
    for option in options:
        print(f'({option[0]}) {option[1]}')

    # Get the user's choice
    operation = input("See the output below and choose an option, or press 'enter' to exit.").lower()

    # Find the selected option in the options list
    selected_option = None
    for option in options:
        if operation == option[0]:
            selected_option = option
            break

    # Call the corresponding function for the selected option
    if selected_option:
        if selected_option[0] == 'c':
            create()
        elif selected_option[0] == 'r':
            read()
        elif selected_option[0] == 'u':
            update()
        elif selected_option[0] == 'd':
            delete()
        elif selected_option[0] == 's':
            schema()
    elif operation == '':
        return
    else:
        print('Invalid option')

    # Repeat the menu
    menu()


menu()
Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete
(s) Schema
(1, 'Thomas Edison', 'toby', 'sha256$CDGgRfBeut2uqvEA$f1e92b9fa491fb16d50e148492e38dbd56332215b4039d0f732ddb7a5fc1a26c', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$rT3htR5xILKIGrlD$9ec288fce5d4e74e91a3e332ea4bbf14f126785c4889792a727b6e2de4d79a22', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$Rm0tIs355hyKsUE4$ded47f88d390b5666792a8fe6c2630c0924bd7be0db01e83cd3fad8b39067612', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$ZM292SWazGbDRza4$45bdc855aba573f8c8297f077b67a04d489dcfbed55d185e25c97f91ae6c8156', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$XxGSMzlKittPa9QG$30116de927d4710d5240f9e63f611bfbba75f346631bc47b9b791477382b4837', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$AlPpBn4T2b6ma2hT$910d19c0f14fb4e8571918669594bde6e33efc52646c7323fa06ba72529577cb', '1921-10-21')
Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete
(s) Schema

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. In the code above, the schema, read, create, update, and delete methods are defined and used later in the program for the menu. This is an example of procedural abstraction because it provides a more modular approach to programming the menu, as we can call the defined methods based on what option the user chooses instead of defining them separately each time the user selects an option.

  • In 2.4a or 2.4b lecture
    • Do you see data abstraction? Complement this with Debugging example. imageimage image image image image
    • Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

      Below is a table that stores data about employees such as employee id number, name, hours worked, and salary using OOP.
      I added a menu interface at the bottom, which allows the employee to execute methods defined in the code below.

Reference... sqlite documentation

Creating Employee Class

"""
These imports define the key objects
"""

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

""" database dependencies to support sqlite examples """
import json

from sqlalchemy.exc import IntegrityError

"""
These object and definitions are used throughout the Jupyter Notebook.
"""

# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
database = 'sqlite:///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()


# This belongs in place where it runs once per project
db.init_app(app)

# Employee table
class Employee(db.Model):
    __tablename__ = 'employees'

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _eid = db.Column(db.String(100), unique=False, nullable=False)
    _name = db.Column(db.String(255), unique=False, nullable=False)
    _hours = db.Column(db.Integer, unique=False, nullable=False)
    _salary = db.Column(db.Integer, unique=False, nullable=False)

    def __init__(self, eid, name, hours, salary):
        self._eid = eid
        self._name = name
        self._hours = hours
        self._salary = salary
        
    @property
    def name(self):
        return self._name
    
    @name.setter
    def name(self, name):
        self._name = name
    
    @property
    def eid(self):
        return self._eid
    
    @eid.setter
    def points(self, eid):
        self._eid = eid
    
    @property
    def hours(self):
        return self._hours
    
    @hours.setter
    def hours(self, hours):
        self._hours = hours

    @property
    def salary(self):
        return self._salary
    
    @salary.setter
    def salary(self, salary):
        self._salary = salary
    
    # output content using json dumps, this is ready for API response
    def __str__(self):
        return json.dumps(self.read())
    
    def create(self):
        try:
            db.session.add(self)
            db.session.commit()
            return self
        except IntegrityError:
            db.session.remove()
            return None

Initializing data

"""Database Creation and Testing """

# Builds working data for testing
def initEmp():
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        # Input data for objects which are made from the template defined by 'User'
        e1 = Employee(eid = 'akhan', name='Azeem Khan', hours='35', salary='60000')
        e2 = Employee(eid = 'mahmoudi', name='Arteen Mahmoudi', hours='40', salary='8000000')
        e3 = Employee(eid = 'cmills', name='Colin Mills', hours='35', salary='70000')
        e4 = Employee(eid = 'ekamk', name='Ekam Kaire', hours='38', salary='85000')


        employees = [e1, e2, e3, e4]

        """Builds sample player/note(s) data"""
        for e in employees:
            try:
                '''add user to table'''
                object = e.create()
                print(f"Created new employee {object.name}")
            except:  # error raised if object not created
                '''fails with bad or duplicate data'''
                print(f"Records exist name {e.name}, or error.")
                
initEmp()
Created new employee Azeem Khan
Created new employee Arteen Mahmoudi
Created new employee Colin Mills
Created new employee Ekam Kaire

Defining SQL CRUD

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('employees')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()


def create():
    eid = input("Enter your employee id")
    name = input("Enter your name")
    hours = input('How many hours do you work per week?')
    salary = input("What is your annual salary?")
    
    # 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 employees (_eid, _name, _hours, _salary) VALUES (?, ?, ?, ?)", (eid, name, hours, salary))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new user record {eid} 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()


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


def update():
    eid = input("Enter employee id to update")
    hours = input("Enter updated hours per week")
    salary = input("Enter updated annual salary")
    # 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 employees SET _hours = ?, _salary = ? WHERE _eid = ?", (hours, salary, eid))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No eid {eid} was not found in the table")
        else:
            print(f"The row with employee id {eid} the hours/salary has been successfully updated")
            conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the UPDATE:", error)
        
    
    # Close the cursor and connection objects
    cursor.close()
    conn.close()

def delete():
    eid = input("Enter employee 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 employees WHERE _eid = ?", (eid,))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No eid {eid} was not found in the table")
        else:
            # The uid was found in the table and the row was deleted
            print(f"The row with eid {eid} 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()

Creating a Menu Interface

options = [
    ('c', 'Create'),
    ('r', 'Read'),
    ('u', 'Update'),
    ('d', 'Delete'),
    ('s', 'Schema')
]

# Define the menu function
def menu():
    # Display the menu options
    print('Select an option:')
    for option in options:
        print(f'({option[0]}) {option[1]}')

    # Get the user's choice
    operation = input("See the output below and choose an option, or press 'enter' to exit.").lower()

    # Find the selected option in the options list
    selected_option = None
    for option in options:
        if operation == option[0]:
            selected_option = option
            break

    # Call the corresponding function for the selected option
    if selected_option:
        if selected_option[0] == 'c':
            create()
        elif selected_option[0] == 'r':
            read()
        elif selected_option[0] == 'u':
            update()
        elif selected_option[0] == 'd':
            delete()
        elif selected_option[0] == 's':
            schema()
    elif operation == '':
        return
    else:
        print('Invalid option')

    # Repeat the menu
    menu()


menu()
Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete
(s) Schema
(1, 'akhan', 'Azeem Khan', 35, 60000)
(2, 'mahmoudi', 'Arteen Mahmoudi', 40, 8000000)
(3, 'cmills', 'Colin Mills', 35, 70000)
(4, 'ekamk', 'Ekam Kaire', 38, 85000)
(5, 'mugsybogues', 'Mugsy Bogues', 40, 200000)
Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete
(s) Schema