Unit 2.4a Using Programs with Data, SQLAlchemy

• Database and SQLAlchemy
  • Imports and Flask Objects
  • Model Definition
  • Initial Data
  • Check for given Credentials in users table in sqlite.db
  • Create a new User in table in Sqlite.db
  • Reading users table in sqlite.db
• Hacks
  • My Own Databse

Database and SQLAlchemy

In this blog we will explore using programs with data, focused on Databases. We will use SQLite Database to learn more about using Programs with Data. Use Debugging through these examples to examine Objects created in Code.

• College Board talks about ideas like

  • Program Usage. "iterative and interactive way when processing information"
  • Managing Data. "classifying data are part of the process in using programs", "data files in a Table"
  • Insight "insight and knowledge can be obtained from ... digitally represented information"
  • Filter systems. 'tools for finding information and recognizing patterns"
  • Application. "the preserve has two databases", "an employee wants to count the number of book"

• PBL, Databases, Iterative/OOP

  • Iterative. Refers to a sequence of instructions or code being repeated until a specific end result is achieved
  • OOP. A computer programming model that organizes software design around data, or objects, rather than functions and logic
  • SQL. Structured Query Language, abbreviated as SQL, is a language used in programming, managing, and structuring data

Imports and Flask Objects

Defines and key object creations

• Comment on where you have observed these working? Provide a defintion of purpose.
  1. Flask app object:- app = Flask(name) creates the object - app is getting assigned the attributes (setters)
  2. SQLAlchemy db object:
     • setting the database object as the properties from SQLAlchemy

"""
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__) #  app = Flask(_name_) creates the object 
# 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 is getting assigned the attributes (setters)
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy() # setting the database object as the properties from SQLAlchemy



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

Model Definition

Define columns, initialization, and CRUD methods for users table in sqlite.db

• Comment on these items in the class, purpose and defintion.
  • class User:- defining the class template for "Users" - we will use this template to create objects for the class
  • db.Model inheritance:
    • (db.Model) is called inheritance -> inheriting the database properties into our class
    • allows us to use these methods to interact with our database, aka adding to our template
  • init method:
    • "constructor"
    • used to help us instantiate an object from our User class
    • makes an object using the Class name and attributes as parameters
  • @property, @<column>.setter:
    • setters: allow us to change the attributes
    • getters: enable us to retrieve data out of the objects
  • create, read, update, delete methods:
    • Can interact with the database using these methods
    • create: forms the data in the database
    • read: retrieves the data from the database
    • update: updates the data in the database
    • delete: deletes the data in the database

""" 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 User(db.Model): # defining the class template for "Users". We will use this template to create objects for the class
    __tablename__ = 'users'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _name = db.Column(db.String(255), unique=False, nullable=False)
    _uid = db.Column(db.String(255), unique=True, nullable=False)
    _password = db.Column(db.String(255), unique=False, nullable=False)
    _dob = db.Column(db.Date)

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, name, uid, password="123qwerty", dob=datetime.today()):
        self._name = name    # variables with self prefix become part of the object, 
        self._uid = uid
        self.set_password(password)
        if isinstance(dob, str):  # not a date type     
            dob = date=datetime.today()
        self._dob = dob

    # a name getter method, extracts name from object
    @property # getters: enable us to retrieve data out of the objects
    def name(self):
        return self._name
    
    # a setter function, allows name to be updated after initial object creation
    @name.setter # setters: allow us to change the attributes
    def name(self, name):
        self._name = name
    
    # a getter method, extracts uid from object
    @property
    def uid(self):
        return self._uid
    
    # a setter function, allows uid to be updated after initial object creation
    @uid.setter
    def uid(self, uid):
        self._uid = uid
        
    # check if uid parameter matches user id in object, return boolean
    def is_uid(self, uid):
        return self._uid == uid
    
    @property
    def password(self):
        return self._password[0:10] + "..." # because of security only show 1st characters

    # update password, this is conventional method used for setter
    def set_password(self, password):
        """Create a hashed password."""
        self._password = generate_password_hash(password, method='sha256')

    # check password parameter against stored/encrypted password
    def is_password(self, password):
        """Check against hashed password."""
        result = check_password_hash(self._password, password)
        return result
    
    # dob property is returned as string, a string represents date outside object
    @property
    def dob(self):
        dob_string = self._dob.strftime('%m-%d-%Y')
        return dob_string
    
    # dob setter, verifies date type before it is set or default to today
    @dob.setter
    def dob(self, dob):
        if isinstance(dob, str):  # not a date type     
            dob = date=datetime.today()
        self._dob = dob
    
    # age is calculated field, age is returned according to date of birth
    @property
    def age(self):
        today = datetime.today()
        return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day))
    
    # output content using str(object) is in human readable form
    # 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,
            "name": self.name,
            "uid": self.uid,
            "dob": self.dob,
            "age": self.age,
        }

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

    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
    
    # code is a definition (a template) but isn't instantiated yet so there is no output

Initial Data

Uses SQLALchemy db.create_all() to initialize rows into sqlite.db

• Comment on how these work?
  1. Create All Tables from db Object:- the db object uses the properties from the SQLAlchemy library to build the rows and collumns in the tables with the user list that has the attributes as data 2. User Object Constructors:
     • all the tester users have have "User" before the parameters, which constructs the data into the User class.
  2. Try / Except:
     • the program will attempt the code that is under "try" and then if it fails, it will run the "except" code
     • in this case, it will try to add the data to the database, however, if it already exists it will print "Records exist uid {user.uid}, or error"

"""Database Creation and Testing """


# Builds working data for testing
def initUsers(): # sets the properties of the object as we create it
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        u1 = User(name='Thomas Edison', uid='toby', password='123toby', dob=datetime(1847, 2, 11)) #makes an object using the Class name and attributes as parameters
        u2 = User(name='Nikola Tesla', uid='niko', password='123niko')
        u3 = User(name='Alexander Graham Bell', uid='lex', password='123lex')
        u4 = User(name='Eli Whitney', uid='whit', password='123whit')
        u5 = User(name='Indiana Jones', uid='indi', dob=datetime(1920, 10, 21))
        u6 = User(name='Marion Ravenwood', uid='raven', dob=datetime(1921, 10, 21))


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

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

Check for given Credentials in users table in sqlite.db

Use of ORM Query object and custom methods to identify user to credentials uid and password

• Comment on purpose of following
  1. User.query.filter_by:- will result in true or false - identifies credentials
  2. user.password: -will check if the passoword exists in the databse
     • if you uncomment out check credentials and change the password it will display false

def find_by_uid(uid):
    with app.app_context():
        user = User.query.filter_by(_uid=uid).first() # can identify user crdentials because of ORM Query object
    return user # returns user object

# Check credentials by finding user and verify password
def check_credentials(uid, password):
    # query email and return user record
    user = find_by_uid(uid)
    if user == None:
        return False
    if (user.is_password(password)):
        return True
    return False
        
# check_credentials("indi", "123qwerty")

Create a new User in table in Sqlite.db

Uses SQLALchemy and custom user.create() method to add row.

• Comment on purpose of following
  1. user.find_by_uid() and try/except:- this step is to make sure the user doesn't already exist in the databse - if the user id already exists, it will print it
     • if it doesn't, it will continue onto the rest of the code (adding to databse)
  2. user = User(...):
     • this piece defines "user" as a variable that contains all the attributes of the person from the User class
  3. user.dob and try/except:
     • this piece of code it checking for garbage data
     • it will try to define the user's date of birth with their input
     • if it isn't in the correct format it will set the dob to the defualt (current date) and print that the input was invalid
  4. user.create() and try/except
     • it will try user.create()
     • user.create() is defined earlier and has it's own tey/except, that will filter out repeats or garbage data
     • in this code cell, the try will run that function but if it fails it will print an error

def create():
    # optimize user time to see if uid exists
    uid = input("Enter your user id:")
    user = find_by_uid(uid)
    try:
        print("Found\n", user.read())
        return
    except:
        pass # keep going
    
    # request value that ensure creating valid object
    name = input("Enter your name:")
    password = input("Enter your password")
    
    # Initialize User object before date
    user = User(name=name, 
                uid=uid, 
                password=password
                )
    
    # create user.dob, fail with today as dob
    dob = input("Enter your date of birth 'YYYY-MM-DD'")
    try:
        user.dob = datetime.strptime(dob, '%Y-%m-%d').date()
    except ValueError:
        user.dob = datetime.today()
        print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {user.dob}")
           
    # write object to database
    with app.app_context():
        try:
            object = user.create()
            print("Created\n", object.read())
        except:  # error raised if object not created
            print("Unknown error uid {uid}")
        
create()

Reading users table in sqlite.db

Uses SQLALchemy query.all method to read data

• Comment on purpose of following
  1. User.query.all:- this function is requesting all the data/objects from the User class/database 2. json_ready assignment, google List Comprehension:
     • list comprehension makes it so you can create a new list that you would originally need 2+ seperate steps for (loop and then new list) in 1
     • the json_ready assignment performs a for loop that reads the user data in the table and automatically appends the the users in the json_ready list
     • this assignment would originally need to be in more steps, with defining the list first, and then the loop that appends to the json_ready list

# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
    with app.app_context():
        table = User.query.all()
    json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list, shorter syntax
    return json_ready

read()

Hacks

• Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
• Change blog to your own database.
• Add additional CRUD
  • Add Update functionality to this blog.
  • Add Delete functionality to this blog.

My Own Databse

Incorporating CRUD into my own database

""" 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 Shop(db.Model): # defining the class template for "Users". We will use this template to create objects for the class
    __tablename__ = 'shops'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _item = db.Column(db.String(255), unique=False, nullable=False)
    _storename = db.Column(db.String(255), unique=False, nullable=False)
    _quantity = db.Column(db.String, unique=False, nullable=False)
    _price = db.Column(db.Integer, unique=False, nullable=False)

    # constructor of a User object, initializes the instance variables within object (self)
    def __init__(self, item, storename, quantity, price):
        self._item = item    # variables with self prefix become part of the object, 
        self._storename = storename
        self._quantity = quantity
        self._price = price

    # a name getter method, extracts name from object
    @property # getters: enable us to retrieve data out of the objects
    def item(self):
        return self._item
    
    # a setter function, allows name to be updated after initial object creation
    @item.setter # setters: allow us to change the attributes
    def item(self, item):
        self._item = item
    
    # a getter method, extracts uid from object
    @property
    def storename(self):
        return self._storename
    
    # a setter function, allows uid to be updated after initial object creation
    @storename.setter
    def storename(self, storename):
        self._uid = storename

      # a name getter method, extracts name from object
    @property # getters: enable us to retrieve data out of the objects
    def quantity(self):
        return self._quantity
    
    # a setter function, allows name to be updated after initial object creation
    @quantity.setter # setters: allow us to change the attributes
    def quantity(self, quantity):
        self._quantity = quantity

    # a name getter method, extracts name from object
    @property # getters: enable us to retrieve data out of the objects
    def price(self):
        return self._price
    
    # a setter function, allows name to be updated after initial object creation
    @price.setter # setters: allow us to change the attributes
    def price(self, price):
        self._price = price
    
    
    # output content using str(object) is in human readable form
    # 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,
            "item": self.item,
            "storename": self.storename,
            "quantity": self.quantity,
            "price": self.price,
        }

    # CRUD update: updates item, storename, quantity, price
    # returns self
    def update(self, item="", storename="", quantity="", price=""):
        """only updates values with length"""
        if len(item) > 0:
            self.item = item
        if len(storename) > 0:
            self.storename = storename
        if len(quantity) > 0:
            self.quantity = quantity
        if len(price) > 0:
            self.price = price
        db.session.commit()
        return self

    # CRUD delete: remove self
    # None
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
    
    # code is a definition (a template) but isn't instantiated yet so there is no output

"""Database Creation and Testing """


# Builds working data for testing
def initShops(): # sets the properties of the object as we create it
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        i1 = Shop(item='Milk', storename='Vons', quantity='1 gallon', price='4.38') #makes an object using the Class name and attributes as parameters
        i2 = Shop(item='Hamburger Buns', storename='Costco', quantity='3 bags', price='14.34')
        i3 = Shop(item='Juice', storename='Albertsons', quantity='1 jug', price='3.23')
        i4 = Shop(item='Soup', storename='Vons', quantity='4 cans', price='15.26')
        i5 = Shop(item='Crackers', storename='Albertsons', quantity='1 box', price='5.68')
        i6 = Shop(item='Bananas', storename='Sprouts', quantity='1 bunch', price='8.09')


        items = [i1, i2, i3, i4, i5, i6]

        """Builds sample item/note(s) data"""
        for item in items:
            try:
                '''add item to table'''
                object = item.create()
                print(f"Created new item {object.item}")
            except:  # error raised if object nit created
                '''fails with bad'''
                print(f"error, bad data")
                
initShops()

Created new item Milk
Created new item Hamburger Buns
Created new item Juice
Created new item Soup
Created new item Crackers
Created new item Bananas

def createitem():
    item = input("Enter the item you want to purchase:")
    storename = input("Enter the store you will get the item from:")
    quantity = input("Enter the quantity")
    price = input("Enter the price")
    
    # Initialize User object before date
    item = Shop(item=item, 
                storename=storename, 
                quantity=quantity,
                price=price
                )

    # write object to database
    with app.app_context():
        try:
            object = item.create()
            print("Created\n", object.read())
        except:  # error raised if object not created
            print("Unknown error")
        
createitem()

Created
 {'id': 7, 'item': 'Cookies', 'storename': 'Costco', 'quantity': '3 boxes', 'price': 14.56}

# SQLAlchemy extracts all users from database, turns each user into JSON
def readlist():
    with app.app_context():
        table = Shop.query.all()
    json_ready = [item.read() for item in table] # "List Comprehensions", for each user add user.read() to list, shorter syntax
    return json_ready

readlist()

[{'id': 1,
  'item': 'Milk',
  'storename': 'Vons',
  'quantity': '1 gallon',
  'price': 4.38},
 {'id': 2,
  'item': 'Hamburger Buns',
  'storename': 'Costco',
  'quantity': '3 bags',
  'price': 14.34},
 {'id': 3,
  'item': 'Juice',
  'storename': 'Albertsons',
  'quantity': '1 jug',
  'price': 3.23},
 {'id': 4,
  'item': 'Soup',
  'storename': 'Vons',
  'quantity': '4 cans',
  'price': 15.26},
 {'id': 5,
  'item': 'Crackers',
  'storename': 'Albertsons',
  'quantity': '1 box',
  'price': 5.68},
 {'id': 6,
  'item': 'Bananas',
  'storename': 'Sprouts',
  'quantity': '1 bunch',
  'price': 8.09},
 {'id': 7,
  'item': 'Cookies',
  'storename': 'Costco',
  'quantity': '3 boxes',
  'price': 14.56}]

def deleteitem():
    item = input("Enter the item you want to delete:")
    with app.app_context():
        result = Shop.query.filter_by(_item=item).first()
        if result is None:
            print("Item not found")
        else:
            result.delete()
            print("Deleted item:", result._item)

deleteitem()

Deleted item: Soup

def updateitem():
    useritem = input("Enter the item you want to update: ")
    with app.app_context():
        item = Shop.query.filter_by(_item=useritem).first()
        if item is None:
            print("Item not found")
        else:
            new_item = input("Enter new item name (press enter to keep current value): ")
            new_storename = input("Enter new store name (press enter to keep current value): ")
            new_quantity = input("Enter new quantity (press enter to keep current value): ")
            new_price = input("Enter new price (press enter to keep current value): ")
            
            if new_item:
                item.item = new_item
            if new_storename:
                item.storename = new_storename
            if new_quantity:
                item.quantity = new_quantity
            if new_price:
                item.price = new_price

            db.session.commit()
            print("Updated item:", item)


updateitem()

Updated item: {"id": 7, "item": "Oatmeal Cookies", "storename": "Costco", "quantity": "3 boxes", "price": 14.56}

import sqlite3

database = 'instance/sqlite.db' # this is location of database

def schemaitem():
    
    # 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('shops')").fetchall()

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

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

(0, 'id', 'INTEGER', 1, None, 1)
(1, '_item', 'VARCHAR(255)', 1, None, 0)
(2, '_storename', 'VARCHAR(255)', 1, None, 0)
(3, '_quantity', 'VARCHAR', 1, None, 0)
(4, '_price', 'INTEGER', 1, None, 0)

def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        createitem()
    elif operation.lower() == 'r':
        Shoppinglist = readlist()
        print(Shoppinglist)
    elif operation.lower() == 'u':
        updateitem()
    elif operation.lower() == 'd':
        deleteitem()
    elif operation.lower() == 's':
        schemaitem()
    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")

Deleted item: Grapes