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 of the object (setter)
      • sets up template for database objects
    2. SQLAlchemy db object
      • db = SQLAlchemy()
      • sets the database object with 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__) # this creates the object and assigns app to the attributes
# 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() # sets the database object with properties from SQUAlchemy


# 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 = defines the template for users (class definition template used to create objects of type user)
      • a class is a template for a future object (attributes)
    • db.Model inheritance
      • inherit ability to make our class use database properties, methods, and attributes
    • init method
      • init method helps us instantiate (create) an object from our user class
      • instantiates a u1 object with attributes
    • @property, @<column>.setter
      • used to see what is inside an object and is used to change what is inside the object
      • setter= allows us to change attributes
      • getter (property)= shows us attributes (ex. value name= name is shown)
      • used to set and retrieve data

    • create, read, update, delete methods

      • methods inside of a class
      • interacts with the database
      • creates a new user with attributes
      • reads a user/s and their attributes
      • updates a user and their attributes
      • deletes a user and their attributes

    • when executing cell = no result because template is created but data is not instantiated

""" 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): # defines the template for users (class definition template used to create objects of type user)
    __tablename__ = 'users'  # table name is plural, class name is singular

    # Define the User schema with "vars" from object

    # able to use db methods, attributes, properties inherritted to add to database template
    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)
    # init method helps us instantiate (create) an object from our user class
    def __init__(self, name, uid, password="123qwerty", dob=datetime.today()): # instantiates a u1 object with attributes
        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  # property is a getter that shows us object attributes
    def name(self):
        return self._name
    
    # a setter function, allows name to be updated after initial object creation
    @name.setter # setter function can change the attributes of an object
    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): # creates a user in the databse 
        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): # reads the users in the database and their attributes
        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=""): # updates an existing user's attributes
        """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): # deletes a user and their attributes from the database entirely
        db.session.delete(self)
        db.session.commit()
        return None

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

      Using the SQUAlchemy library, the db.create_all() creates rows for all the objects (u1, u2, u3, u4, u5, and u6) and columns for their attributes.

    2. User Object Constructors

      The User object constructors are used to add the objects to the class with their corresponding attributes. The "User" before the user data is referring to the User class in which the objects are stored.

    3. Try / Except

      The try/except is used to check for bad data before a user is added to the class. If the user already exists or the user has bad data, the user will not be added to the class. This helps to prevent future issues when creating the database.

"""Database Creation and Testing """


# Builds working data for testing
def initUsers():
    with app.app_context():
        """Create database and tables"""
        db.create_all() # creates rows for all the objects (u1, u2, u3, u4, u5, and u6) and columns for their attributes. 
        """Tester data for table"""
        u1 = User(name='Thomas Edison', uid='toby', password='123toby', dob=datetime(1847, 2, 11))
        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: # checking for bad data and errors
            try:
                '''add user to table'''
                object = user.create()
                print(f"Created new uid {object.uid}")
            except:  # error raised if object not 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

      • Object Relational Model = ORM
      • identifies credentials
      • searches through the class to find what is defined in the parameter
      • this returns the first match in class (or if it exists)

    2. user.password

      • checks to see if passwords match
      • if not found = will return false
      • if found = will return true
def find_by_uid(uid):
    with app.app_context():
        user = User.query.filter_by(_uid=uid).first() # identifies credentials, will return true/false
    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)): # checks to see if passwords match
        return True # is yes= returns true
    return False # if no- returns 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 tests to see if a user already exists. If a user already exists, it will return the user's data, but won't create a new one. If the user does not already exist, then the program will continue.
    1. user = User(...)
    • user = User() defines user as a variable with the attributes from the class of user objects.
    1. user.dob and try/except
    • This line of code is to avoid garbage data with the date. If the user inputs in incorrect date formatting, the program will not be able to figure out the age and will need to return an error.
    1. user.create() and try/except
    • This line of code is to check if an object is successfully created or not. If a user is successfully created, it will return a success message. If not, the code will return an error message along with the corresponding uid.
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() # checks for correct formatting and calculates age
    except ValueError:
        user.dob = datetime.today()
        print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {user.dob}") # prints an error for incorrect format- this avoids garbage data with the date
           
    # write object to database
    with app.app_context():
        try:
            object = user.create()
            print("Created\n", object.read()) # the program will try to create a user and if it fails, it will move to except
        except:  # error raised if object not created
            print("Unknown error uid {uid}") # will print an error if error with user creation
        
create()

Reading users table in sqlite.db

Uses SQLALchemy query.all method to read data

  • Comment on purpose of following

    1. User.query.all
    • User.query.all requests all of the user objects from the user class and database. This line is defining "table" as all of this data.
    1. json_ready assignment, google List Comprehension
    • List comprehension = a shorter syntax to edit existing lists (one line versus three or four)
    • the json_ready line uses list comprehension to create a new list of all the user data in the database in json format.
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
    with app.app_context():
        table = User.query.all() # defines table as request of all of the user objects from the user class
    json_ready = [user.read() for user in table] # "List Comprehension", for each user add user.read() to list
    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 Database with CRUD

Below is a database in which a user can make a shopping list. The user is able to add items to their list, update them, and delete them. Run the menu below to see and edit your shopping list!

Defining the Class, setters, getters 

class shopping_list(db.Model): # defines the template for shopping list 
    __tablename__ = 'Shopping_List'

    # Define the shopping schema with "vars" from object

    # able to use db methods, attributes, properties inherritted to add to database template
    id = db.Column(db.Integer, primary_key=True) 
    _item = db.Column(db.String(255), unique=True, nullable=False)
    _storename = db.Column(db.String(255), unique=False, nullable=False)
    _price = db.Column(db.Integer, unique=False, nullable=False)
    _quantity = db.Column(db.Integer, unique=False, nullable=False)


    # constructor of an item object, initializes the instance variables within object (self)
    # init method helps us instantiate (create) an object from class
    def __init__(self, item, storename, price, quantity): # instantiates an item
        self._item = item    # variables with self prefix become part of the object, 
        self._storename = storename
        self._price = price
        self._quantity = quantity

    # an item getter method, extracts item from object
    @property  # property is a getter that shows us object attributes
    def item(self):
        return self._item
    
    # a setter function, allows item to be updated after initial object creation
    @item.setter # setter function can change the attributes of an object
    def item(self, item):
        self._item = item
    
    # a getter method, extracts storename from object
    @property
    def storename(self):
        return self._storename
    
    # a setter function, allows storename to be updated after initial object creation
    @storename.setter
    def storename(self, storename):
        self._storename = storename
    
    # a getter method, extracts uid from object
    @property
    def price(self):
        return self._price
    
    # a setter function, allows uid to be updated after initial object creation
    @price.setter
    def price(self, price):
        self._price = price

    # a getter method, extracts uid from object
    @property
    def quantity(self):
        return self._quantity
    
    # a setter function, allows uid to be updated after initial object creation
    @quantity.setter
    def quantity(self, quantity):
        self._quantity = quantity
    
    # 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): # creates an item in the database 
        try:
            # creates an item object from shopping_list(db.Model) class, passes initializers
            db.session.add(self)  # add prepares to persist item object to Shopping_list 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): # reads the users in the database and their attributes
        return {
            "id": self.id,
            "item": self.item,
            "storename": self.storename,
            "price": self.price,
            "quantity": self.quantity
        }

    # CRUD update: updates item, storename, price, quantity
    # returns self
    def update(self, item="N/A", storename="N/A", price="N/A", quantity="N/A"): # updates an existing item's attributes
        """only updates values with length"""
        if len(item) > 0:
            self.name = item
        if len(storename) > 0:
            self.uid = storename
        if len(price) > 0:
            self.price = price
        if len(quantity) > 0:
            self.quantity = quantity
        db.session.commit()
        return self

    # CRUD delete: remove self
    # None
    def delete(self): # deletes an item and their attributes from the database entirely
        db.session.delete(self)
        db.session.commit()
        return None

Creating the Database 

"""Database Creation and Testing """


# Builds working data for testing
def initShopping_list():
    with app.app_context():
        """Create database and tables"""
        db.create_all() # creates rows for all the objects (u1, u2, u3, u4, u5, and u6) and columns for their attributes. 
        """Tester data for table"""
        i1 = shopping_list(item='Gummies', storename='Sprouts', price='3', quantity='4')
        i2 = shopping_list(item='Milk', storename='Vons', price='10', quantity='2')
        i3 = shopping_list(item='Oreos', storename='Costco', price='5', quantity='1')
        i4 = shopping_list(item='Ham', storename='Sprouts', price='15', quantity='1')
        i5 = shopping_list(item='Gatorade', storename='Vons', price='12', quantity='3')
        i6 = shopping_list(item='Cheez-its', storename='Vons', price='6', quantity='10')


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

        """Builds sample item data"""
        for item in items: # checking for bad data and errors
            try:
                '''add item to table'''
                object = item.create()
                print(f"Created new item: {object.item}")
            except:  # error raised if object not created
                '''fails with bad data'''
                print("error, bad data")
                
initShopping_list()

Created new item: Gummies
Created new item: Milk
Created new item: Oreos
Created new item: Ham
Created new item: Gatorade
Created new item: Cheez-its

Read 

def read_list():
    with app.app_context():
        shoppingtable = shopping_list.query.all() # defines table as request of all of the user objects from the user class
    json_ready = [item.read() for item in shoppingtable] # "List Comprehension", for each user add user.read() to list
    return json_ready

read_list()

[{'id': 1,
  'item': 'Gummies',
  'storename': 'Sprouts',
  'price': 3,
  'quantity': 4},
 {'id': 2, 'item': 'Milk', 'storename': 'Vons', 'price': 10, 'quantity': 2},
 {'id': 3, 'item': 'Oreos', 'storename': 'Costco', 'price': 5, 'quantity': 1},
 {'id': 4, 'item': 'Ham', 'storename': 'Sprouts', 'price': 15, 'quantity': 1},
 {'id': 5,
  'item': 'Gatorade',
  'storename': 'Vons',
  'price': 12,
  'quantity': 3},
 {'id': 6,
  'item': 'Cheez-its',
  'storename': 'Vons',
  'price': 6,
  'quantity': 10}]

Create 

def create_item():
    # request value that ensure creating valid object
    item = input("Enter item:")
    storename = input("Enter the storename:")
    price = input("Enter the price:")
    quantity = input("Enter the quantity:")
    
    # Initialize item object before date
    item = shopping_list(item=item, 
                storename=storename, 
                price=price,
                quantity=quantity
                )

    # write object to database
    with app.app_context():
        try:
            object = item.create()
            print("Created\n", object.read()) # the program will try to create an item and if it fails, it will move to except
        except:  # error raised if object not created
            print("Unknown error item {item}") # will print an error if error with user creation
        
create_item()

Update 

def update_item():
    updateditem = input("What item would you like to update?")
    with app.app_context():
        item = shopping_list.query.filter_by(_item=updateditem).first()
        if item is None:
            print("Item not found")
        else:
            new_item = input("Enter new item name (press enter to cancel): ")
            new_storename = input("Enter new store name (press enter to cancel): ")
            new_price = input("Enter new price (press enter to cancel): ")
            new_quantity = input("Enter new quantity (press enter to cancel): ")
           
            if new_item:
                item.item = new_item
            if new_storename:
                item.storename = new_storename
            if new_price:
                item.price = new_price
            if new_quantity:
                item.quantity = new_quantity
            db.session.commit()
            print("Updated item:", item)
update_item()

Delete 

def delete_item():
    item = input("What item would you like to delete?")
    with app.app_context():
        result = shopping_list.query.filter_by(_item=item).first()
        if result is None:
            print("Item not found")
        else:
            result.delete()
            print("Deleted item:", result._item)

delete_item()

Schema 

import sqlite3
database = 'instance/sqlite.db' # this is location of database
def schema_item():
    # 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('Shopping_list')").fetchall()
    # Print the results
    for row in results:
        print(row)
    # Close the database connection
    conn.close()
schema_item()

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

Shopping List Menu 

def item_menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        create_item()
    elif operation.lower() == 'r':
        shoppinglist = read_list()
        print(shoppinglist)
    elif operation.lower() == 'u':
        update_item()
    elif operation.lower() == 'd':
        delete_item()
    elif operation.lower() == 's':
        schema_item()
    elif len(operation)==0: # Escape Key
        return
    else:
        print("Please enter c, r, u, or d")
    item_menu() # recursion, repeat menu
try:
    item_menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")

Deleted item: Broccoli