Unit 2.4a-b Hacks

Create the SQLITE Table/Database

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

Defining variables to match with CPT Project

""" 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


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

# Define the User class to manage actions in the 'users' 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.) User 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 Workout(db.Model):
    __tablename__ = 'workouts'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _workoutName = db.Column(db.String(255), unique=True, nullable=False)
    _sets = db.Column(db.Integer, unique=False, nullable=False)
    _reps = db.Column(db.Integer, unique=False, nullable=False)

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, workoutName, sets, reps):
        self._workoutName = workoutName    # variables with self prefix become part of the object, 
        self._sets = sets
        self._reps = reps
        ##if isinstance(dob, str):  # not a date type     
            #dob = date=datetime.today()
        #self._dob = dob

    # a name getter method, extracts workout name from object
    @property
    def workoutName(self):
        return self._workoutName
    
    # a setter function, allows the workout name to be updated after initial object creation
    
    @workoutName.setter
    def workoutName(self, workoutName):
        self._workoutName = workoutName
     # dob property is returned as string, to avoid unfriendly outcomes
        # a sets getter method, extracts sets from object
    @property
    def sets(self):
        return self._sets
    
    # a setter function, allows the sets to be updated after initial object creation
    @sets.setter
    def sets(self, sets):
        self._sets = sets

    # a repetitions getter method, extracts reps from object
    @property
    def reps(self):
        return self._reps
    
    # a setter function, allows the repetitions to be updated after initial object creation
    @reps.setter
    def reps(self, reps):
        self._reps = reps
 

     # a getter method, id for no duplicates
    #@property
    #def workoutName(self):
        #return self._workoutName
    
    # a setter function, allows name to be updated after initial object creation - setting the workout name as the uid
    #@workoutName.setter
    #def uid(self, workoutName):
        #self._workoutName = workoutName
        
    # check if uid parameter matches user id in object, return boolean NO UID
    #def is_workoutName(self, workoutName):
        #return self._workoutName == workoutName
    
    # 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,
            "workoutName": self.workoutName,
            "sets": self.sets,
            "reps": self.reps,
        }

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

    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
    

Tester Data

"""Database Creation and Testing """


# Builds working data for testing
def initWorkouts():
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        u1 = Workout(workoutName='Frenchies', sets=3, reps=3)
        u2 = Workout(workoutName='Slow Negatives', sets=3, reps=15)
        u3 = Workout(workoutName='90 Degree Lock-Off', sets=3, reps=5)
        u4 = Workout(workoutName='Pull-Ups', sets=3, reps=5)
        u5 = Workout(workoutName='Scapular Pull-Ups', sets=3, reps=5)
        u6 = Workout(workoutName='Symmetric Moves', sets=3, reps=8)


        workouts = [u1, u2, u3, u4, u5, u6]

        """Builds sample user/note(s) data"""
        for workout in workouts:
            try:
                '''add workout to table'''
                object = workout.create()
                print(f"Created new workout {object.workoutName}")
            except:  # error raised if object nit created
                '''fails with bad or duplicate data'''
                print(f"Records exist workout {workout.workoutName}, or error.")
                
initWorkouts()

Created new workout Frenchies
Created new workout Slow Negatives
Created new workout 90 Degree Lock-Off
Created new workout Pull-Ups
Created new workout Scapular Pull-Ups
Created new workout Symmetric Moves

Schema

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

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

    # Close the database connection
    conn.close()
    
schema()

(0, 'id', 'INTEGER', 1, None, 1)
(1, '_workoutName', 'VARCHAR(255)', 1, None, 0)
(2, '_sets', 'INTEGER', 1, None, 0)
(3, '_reps', 'INTEGER', 1, None, 0)

Read data

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 workouts').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, 'Frenchies', 3, 3)
(2, 'Slow Negatives', 3, 15)
(3, '90 Degree Lock-Off', 3, 5)
(4, 'Pull-Ups', 3, 5)
(5, 'Scapular Pull-Ups', 3, 5)
(6, 'Symmetric Moves', 3, 8)

Adding new workout to table and printing (CREATE)

import sqlite3

### CREATING A NEW WORKOUT IN DATABASE


def create():
    workoutName = input(str("Insert workout name"))
    sets = input(("insert number of sets"))
    reps = input(("Insert number of repetitions"))
    
    # 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 workouts (_workoutName, _sets, _reps) VALUES (?, ?, ?)", (workoutName, sets, reps))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new workout record {workoutName} 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()
read()

A new workout record Circuits has been created
(1, 'Frenchies', 3, 3)
(2, 'Slow Negatives', 3, 15)
(3, '90 Degree Lock-Off', 3, 5)
(4, 'Pull-Ups', 3, 5)
(5, 'Scapular Pull-Ups', 3, 5)
(6, 'Symmetric Moves', 3, 8)
(7, 'Circuits', 4, 4)

import sqlite3

def update():
    workoutName = input("Enter which workout you would like to update")
    sets = input("Enter updated number of sets")
    if len(sets) < 0:
        message = "none"
        sets = 1
    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 workouts SET _sets = ? WHERE _workoutName = ?", (sets, workoutName))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No workout named {workoutName} was not found in the table")
        else:
            print(f"The row with workout named {workoutName} the number of sets 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()
read()

No workout named Circuits was not found in the table
(1, 'Frenchies', 3, 3)
(2, 'Slow Negatives', 3, 15)
(3, '90 Degree Lock-Off', 3, 5)
(4, 'Pull-Ups', 3, 5)
(5, 'Scapular Pull-Ups', 3, 5)
(6, 'Symmetric Moves', 3, 8)

Deleting from Table and print (DELETE)

import sqlite3

def delete():
    workoutName = input("Enter workout 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 workouts WHERE _workoutName = ?", (workoutName,))
        if cursor.rowcount == 0:
            # The workout was not found in the table
            print(f"No workout named {workoutName} was not found in the table")
        else:
            # The workout was found in the table and the row was deleted
            print(f"The row with name {workoutName} 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()

read()

The row with name Circuits was successfully deleted
(1, 'Frenchies', 3, 3)
(2, 'Slow Negatives', 3, 15)
(3, '90 Degree Lock-Off', 3, 5)
(4, 'Pull-Ups', 3, 5)
(5, 'Scapular Pull-Ups', 3, 5)
(6, 'Symmetric Moves', 3, 8)

CRUD

## UPDATE IS NOT USED

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 - CAUSES IT TO KEEP RUNNING
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")

The row with name Burpees was successfully deleted
(1, 'Frenchies', 3, 3)
(2, 'Slow Negatives', 3, 15)
(3, '90 Degree Lock-Off', 3, 5)
(4, 'Pull-Ups', 3, 5)
(5, 'Scapular Pull-Ups', 3, 5)
(6, 'Symmetric Moves', 3, 8)

OOP -class Imperative - functions