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
      • This has been used in previous tasks from trimesters 1 and 2. The app object's function is to symbolize your web application. It is used to set up and describe the behavior of your application and is an instance of the Flask class. The app object is used to register routes, specify extensions that your application might use, and set up configuration variables.
    2. SQLAlchemy db object
      • In my own CPT, I have seen this in action. I build and read databases using a sqlite database and the db object. The SQLAlchemy db object's function is to communicate with your application's database. It is an instance of the SQLAlchemy class and offers a high-level interface for Python code to communicate with databases.
"""
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)

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
      • creates variables for each of a user's characteristics and abstracts the user into a unique, named entity.
    • db.Model inheritance:- building block for user items. - init method
      • answer
    • @property, @<column>.setter
      • These are the corresponding getter and setter methods.
    • create, read, update, delete methods
      • The data can be read, created, built into the database, updated, and deleted, as their names imply.
""" 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):
    __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
    def name(self):
        return self._name
    
    # a setter function, allows name to be updated after initial object creation
    @name.setter
    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

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
      • utilizes a SQLALchemy database.initialize entries in sqlite.db using create_all()
    2. User Object Constructors
      • user objects are initialized using getter and setter functions.
    3. Try / Except
      • This tool removes duplicated or flawed data. It guarantees that no two users will have the same ID.
"""Database Creation and Testing """


# Builds working data for testing
def initUsers():
    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))
        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()

Records exist uid toby, or error.
Records exist uid niko, or error.
Records exist uid lex, or error.
Records exist uid whit, or error.
Records exist uid indi, or error.
Records exist uid raven, or error.

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
      • This locates the user whose uid fits the input by filtering the database by uid.
    2. user.password
      • When the uid is obtained, the function check-credentials compares the password entered with the user's password associated with the uid. This functions essentially as a security tool.
def find_by_uid(uid):
    with app.app_context():
        user = User.query.filter_by(_uid=uid).first()
    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")

True

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
      • The find_by_uid() function locates the user whose uid fits the input by filtering the database by uid. The customer is then returned. The try/except function eliminates duplicated or flawed data. It guarantees that no two users will have the same ID.
    2. user = User(...)
      • this creates the user instances before the birthdate.
    3. user.dob and try/except
      • The user's birthdate is indicated by the variable user.dob. It makes sure that users enter their birthdates in the proper format; if not, an error will appear, and the date of birth will be set to the default. The try/except function eliminates duplicated or flawed data. It guarantees that no two users will have the same ID.
    4. user.create() and try/except
      • In response to input from the user, user.create() generates a new user to add to the sqlite table. The try/except function eliminates duplicated or flawed data. It guarantees that no two users will have the same ID.
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()

Created
 {'id': 10, 'name': 'SreejaGGG', 'uid': 'sreeja2', 'dob': '10-10-2006', 'age': 16}

Reading users table in sqlite.db

Uses SQLALchemy query.all method to read data

  • Comment on purpose of following
    1. User.query.all
      • This depicts the entire table of User data, which is then specified as the local table variable and used to generate the JSON through an iterative process.
    2. json_ready assignment, google List Comprehension
      • The properly formatted JSON data created by the procedure next to it is represented by the json_ready variable. The code is then returned along with it.
# 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
    return json_ready

read()

[{'id': 1,
  'name': 'Thomas Edison',
  'uid': 'toby',
  'dob': '02-11-1847',
  'age': 176},
 {'id': 2,
  'name': 'Nikola Tesla',
  'uid': 'niko',
  'dob': '03-15-2023',
  'age': 0},
 {'id': 3,
  'name': 'Alexander Graham Bell',
  'uid': 'lex',
  'dob': '03-15-2023',
  'age': 0},
 {'id': 4,
  'name': 'Eli Whitney',
  'uid': 'whit',
  'dob': '03-15-2023',
  'age': 0},
 {'id': 5,
  'name': 'Indiana Jones',
  'uid': 'indi',
  'dob': '10-21-1920',
  'age': 102},
 {'id': 6,
  'name': 'Marion Ravenwood',
  'uid': 'raven',
  'dob': '10-21-1921',
  'age': 101},
 {'id': 7,
  'name': 'sreejagangapuram1',
  'uid': 'sreeja123',
  'dob': '10-29-2006',
  'age': 16}]

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.

College Board MC Quiz

image

Reflection

Question 1 - For a given row in the spreadsheet, suppose genre contains the genre as a string, num contains the number of copies in stock as a number, and cost contains the cost as a number. Which of the following expressions will evaluate to true if the book should be counted and evaluates to false otherwise?

  • I got this question wrong, I put B, (genre = "mystery") AND ((1 < num) AND (cost < 10.00)), when the correct answer was A, (genre = "mystery") AND ((1 ≤ num) AND (cost < 10.00)). A is correct because only mystery novels are counted, so the value of genre must be "mystery" for a book to be included. Only books with at least one copy on hand are tallied, so num must be greater than or equal to 1. Only books that cost less than $10 are tallied, so the value of cost must be less than $10.00. The AND operator is used between the three criteria because all three must be true.

Question 2 - Using only the data collected during the 7-day period, which of the following statements is true?

  • I got this question right and put D, the total number of items purchased on a given date can be determined by searching the data for all transactions that occurred on the given date and then adding the number of items purchased for each matching transaction. D is correct because the data for each transaction includes the transaction date and the total number of products bought during the transaction. The total number of items bought on a given date can be calculated by searching the data for all transactions that took place on the specified date, adding the number of items purchased in each transaction, and then taking that total.

Question 3 - A new rechargeable battery pack is available for products that use AA batteries. Which of the following best explains how the data files in the table can be used to send a targeted e-mail to only those customers who have purchased products that use AA batteries to let them know about the new accessory?

  • I got this question right and put D, Use the products file to generate a list of product IDs that use AA batteries, then use the list of product IDs to search the purchases file to generate a list of customer IDs, then use the list of customer IDs to search the customers file to generate a list of e-mail addresses. D is correct because the data in the products list can be used to compile an inventory of all the AA battery-using products' product IDs. The list of product IDs can be used in the purchases list to generate a list of customer IDs for all the customers who purchased products that use AA batteries because the product ID information shared by the products list and the purchases list. Finally, because the customer ID is shared by the customers list and the purchases list, it is possible to create a list of e-mail addresses for the customers who bought AA battery-powered items by using the list of customer IDs.

Question 4 - Assume that applying either of the filters will not change the relative order of the rows remaining in the spreadsheet. Which of the following sequences of steps can be used to identify the desired entry?

  • I got this question right. I put B and D, Filter by photographer, then filter by year, then sort by year and Sort by year, then filter by year, then filter by photographer. These are correct because any submissions with unknown photographers will be removed by the photographer filter. Any items with ambiguous years will be removed by the year filter. In column C, the remaining entries can be sorted by year, which will place the image with the lowest year number in the first row of the spreadsheet. The spreadsheet's column C will be sorted from least to largest if you choose to sort by year. Any items with ambiguous years will be removed by the year filter. Any submissions with unknown photographers will be removed by the photographer filter. The photo with the lowest year number will be in the first row of the spreadsheet because the filters have no effect on the order of the entries.

Question 5 - For a given row in the spreadsheet, suppose genre contains the genre as a string and day contains the day as a string. Which of the following expressions will evaluate to true if the show should be counted and evaluates to false otherwise?

  • I got this question right. I put B, (genre = "talk") AND ((day = "Saturday") OR (day = "Sunday")). B is correct because talk is the required genre and Saturday or Sunday is the required day in order for a program to be counted.

Question 6 - The preserve has two databases of information available to use for the exhibit. The first database contains information for each animal’s name, classification, skin type, and thermoregulation. The second database contains information for each animal’s name, lifestyle, average life span, and top speed. Which of the following explains how the two databases can be used to develop the interactive exhibit?

  • I got this question right. I put C, Both databases are needed. Each database can be searched by animal name to find all information to be displayed. C is correct because both databases will be used to show the data. The first directory can be searched using the animal's name to learn more about its classification, skin type, and thermoregulation. The second database can be searched using the animal's name to learn more about its lifestyle, average lifespan, and maximum speed.

Update 

def update():
    # input - uid of the user which we want to update
    uid = input("Enter user id")
    user = find_by_uid(uid)

    if user == None:
        print("User does not exist.")
        return
    else:
        print('User Found With user id '+uid)
        

    # User validation 
    password = input("Enter your password for validation")
    if not check_credentials(uid, password):
        print("The password you have entered is invalid")
        return
    else:
        print("Your password is validated")
        

    newName = input("Enter your name")
    newPassword = input("Enter new password")
         
    # Update the user details
    with app.app_context():
        try:
            object = user.update(name=newName, uid=uid, password=newPassword)
            print("User is sucessfully updated: ", object.read())
        except:
            print("Error Occurred: User not updated")


update()

User Found With user id sreeja1
Your password is validated
User is sucessfully updated:  {'id': 9, 'name': 'SreejaGan', 'uid': 'sreeja1', 'dob': '10-30-2006', 'age': 16}

Delete 

def delete():
    # input - uid of the user which we want to delete
    uid = input("Enter user id")
    user = find_by_uid(uid)

    if user == None:
        print("User does not exist.")
        return
    else:
        print('User Found With user id '+uid)
        

    # User validation 
    password = input("Enter your password for validation")
    if not check_credentials(uid, password):
        print("The password you have entered is invalid")
        return
    else:
        print("Your password is validated")
        
         
    # delete user
    with app.app_context():
        try:
            result = user.delete()
            print("User deleted successfully")
        except:
            print("Error occurred ")

delete()

User Found With user id sreeja2
Your password is validated
User deleted successfully

Proof of Deletion

Before

text

After

text