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?

- database schema is like a blueprint
  • What is the purpose of identity Column in SQL database?
    • Identity column defines the variable
    • In this case it was UID
  • What is the purpose of a primary key in SQL database?
    • Identifies the user
"""
These imports define the key objects
"""

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

"""
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)
""" database dependencies to support sqliteDB examples """
from random import randrange
import os, base64
import json

from sqlalchemy.exc import IntegrityError


''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into Python shell and follow along '''


# Define the Recipe class to manage actions in the 'recipes' table
# -- Object Relational Mapping (ORM) is the key concept of SQLAlchemy
# -- a.) db.Model is like an inner layer of the onion in ORM
# -- b.) Recipe represents data we want to store, something that is built on db.Model
# -- c.) SQLAlchemy ORM is layer on top of SQLAlchemy Core, then SQLAlchemy engine, SQL
class Recipe(db.Model):
    __tablename__ = 'recipes'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, unique=True, primary_key=True)
    _recipename = db.Column(db.String(255), unique=False, nullable=False)
    _recipelink = db.Column(db.String(255), unique=False, nullable=False)
    _recipetype = db.Column(db.String(255), unique=False, nullable=False)
    _recipecuisine = db.Column(db.String(255), unique=False, nullable=False)

    # Defines a relationship between Recipe record and Notes table, one-to-many (one recipe to many notes)

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, recipename, recipelink, recipetype, recipecuisine):
        self._recipename = recipename    # variables with self prefix become part of the object, 
        self._recipelink = recipelink
        self._recipetype = recipetype
        self._recipecuisine = recipecuisine

    # a name getter method, extracts name from object
    @property
    def recipename(self):
        return self._recipename
    # a setter function, allows name to be updated after initial object creation
    @recipename.setter
    def recipename(self, recipename):
        self._recipename = recipename
        
    # a getter method, extracts link from object
    @property
    def recipelink(self):
        return self._recipelink
    # a setter function, allows link to be updated after initial object creation
    @recipelink.setter
    def recipelink(self, recipelink):
        self._recipelink = recipelink
        
    # a getter method, extracts link from object
    @property
    def recipetype(self):
        return self._recipetype
    # a setter function, allows link to be updated after initial object creation
    @recipetype.setter
    def recipetype(self, recipetype):
        self._recipetype = recipetype

    # a getter method, extracts link from object
    @property
    def recipecuisine(self):
        return self._recipecuisine
    # a setter function, allows link to be updated after initial object creation
    @recipecuisine.setter
    def recipecuisine(self, recipecuisine):
        self._recipecuisine = recipecuisine
        
    
    @property
    # output content using str(object) in human readable form, uses getter
    # output content using json dumps, this is ready for API response
    def __str__(self):
        return json.dumps(self.read())

    # CRUD create/add a new record to the table
    # returns self or None on error
    def create(self):
        try:
            # creates a person object from User(db.Model) class, passes initializers
            db.session.add(self)  # add prepares to persist person object to Users table
            db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
            return self
        except IntegrityError:
            db.session.remove()
            return None

    # CRUD read converts self to dictionary
    # returns dictionary
    def read(self):
        return {
            "id": self.id,
            "recipename" : self.recipename,
            "recipelink" : self.recipelink,
            "recipetype" : self.recipetype,
            "recipecuisine" : self.recipecuisine,
        }

    # CRUD update: updates user name, password, phone
    # returns self
    def update(self, recipename="", recipelink="", recipetype="", recipecuisine=""):
        """only updates values with length"""
        if len(recipename) > 0:
            self.recipename = recipename
        if len(recipelink) > 0:
            self.recipelink = recipelink
        if len(recipetype) > 0:
            self.recipetype = recipetype
        if len(recipecuisine) > 0:
            self.recipecuisine = recipecuisine
        db.session.commit()
        return self

    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
"""Database Creation and Testing """


# Builds working data for testing
def initRecipes():
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        r1 = Recipe(recipename='Avocado Toast', recipelink='link1', recipetype='Breakfast', recipecuisine='American')
        r2 = Recipe(recipename='Scrambled Eggs', recipelink='link2', recipetype='Breakfast', recipecuisine='American')
        r3 = Recipe(recipename='Pancake', recipelink='link3', recipetype='Breakfast', recipecuisine='American')
        r4 = Recipe(recipename='Mac and Cheese', recipelink='link4', recipetype='Lunch', recipecuisine='American')
        r5 = Recipe(recipename='Panini Sandwich', recipelink='link5', recipetype='Lunch', recipecuisine='French')
        r6 = Recipe(recipename='Salad', recipelink='link6', recipetype='Lunch', recipecuisine='Mediterranean')
        r7 = Recipe(recipename='Minestrone Soup', recipelink='link7', recipetype='Dinner', recipecuisine='Italian')
        r8 = Recipe(recipename='Lasagna', recipelink='link8', recipetype='Dinner', recipecuisine='Italian')
        r9 = Recipe(recipename='Pasta', recipelink='link9', recipetype='Dinner', recipecuisine='Italian')
        r10 = Recipe(recipename='Brownies', recipelink='link10', recipetype='Dessert', recipecuisine='German')
        r11 = Recipe(recipename='Chocolate Chip Cookies', recipelink='link11', recipetype='Dessert', recipecuisine='American')
        r12 = Recipe(recipename='Custard Pudding', recipelink='link12', recipetype='Dessert', recipecuisine='German')
    
        recipes = [r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12]

        """Builds sample user/note(s) data"""
        for recipe in recipes:
            try:
                recipe.create()
            except IntegrityError:
                '''fails with bad or duplicate data'''
                db.session.remove()
                print(f"Records exist, duplicate email, or error: {recipe.model}")
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('recipes')").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?
    • should connect to a database
  • Same for cursor object?
    • makes the connection for executing SQL
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
    • special variables
    • database
    • file
    • sqlite3
  • Is "results" an object? How do you know?
    • results is not an object
    • does not show up as an attribute in debugger
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 recipes').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()
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb Cell 9 in <cell line: 24>()
     <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=20'>21</a>     cursor.close()
     <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=21'>22</a>     conn.close()
---> <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=23'>24</a> read()

/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb Cell 9 in read()
      <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=7'>8</a> cursor = conn.cursor()
     <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=9'>10</a> # Execute a SELECT statement to retrieve data from a table
---> <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=10'>11</a> results = cursor.execute('SELECT * FROM recipes').fetchall()
     <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=12'>13</a> # Print the results
     <a href='vscode-notebook-cell://wsl%2Bubuntu/home/nope1013/vscode/firstrepo/_notebooks/2023-03-16-AP-unit2-4bhacks.ipynb#W5sdnNjb2RlLXJlbW90ZQ%3D%3D?line=13'>14</a> if len(results) == 0:

OperationalError: no such table: recipes

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():
    recipename = input("Enter recipe name:")
    recipelink = input("Enter recipe link:")
    recipetype = input("Enter recipe type:")
    recipecuisine = input("Enter recipe cuisine:")
    
    # 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 recipes (_recipename, _recipelink, _recipetype, _recipecuisine) VALUES (?, ?, ?, ?)", (recipename, recipelink, recipetype, recipecuisine))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new recipe record {recipename} 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()
Error while executing the INSERT: no such table: recipes

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?
    • prevents passwords that don't meet criteria
  • Explain try/except, when would except occur?
    • except occurs when the user input is an exception to the set rules
  • What code seems to be repeated in each of these examples to point, why is it repeated?
    • The repeated code seems to be input and close
    • done to close the cursor and connection objects
import sqlite3

def update():
    recipename = input("Enter recipe name to update")
    #recipelink = input("Enter recipe link to update")
    recipetype = input("Enter recipe type to update")
    #recipecuisine = input("Enter recipe cuisine to update")
    
    # 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 recipes SET _recipetype = ? WHERE _recipename = ?", (recipename, recipetype))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {recipename} was not found in the table")
        else:
            print(f"The row with recipe name {recipename} the recipe type has updated successfully")
            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()
Error while executing the UPDATE: no such table: recipes

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?
    • data cannot be recovered
import sqlite3

def delete():
    recipename = input("Enter recipe name 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 recipes WHERE _recipename = ?", (recipename,))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No recipe {recipename} 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 {recipename} 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()
Error while executing the DELETE: no such table: recipes

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?
    • the call function is a recursion to repeat the menu
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")