Unit 2.4a Using Programs with Data, SQLAlchemy

• Database and SQLAlchemy
  • Imports and Flask Objects
  • Model Definition
  • Initial Data
  • Extra Notes
  • 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
• Update Functionality
• Delete Functionality

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
  • Flask app object occurs on the line 'app = Flask(name)'. The purpose is to set up a Flask object.
  1. SQLAlchemy db object
  • Occurs on the line 'db = SQLAlchemy()'. The purpose is to create properties for db.

"""
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 template for making object
  • db.Model inheritance
  • Creates SQLite table and database.
  • init method
  • Constructor of the object and helps create it.
  • @property, @<column>.setter
  • @property establishes the values and @<column>.setter alters the values inputted.
  • create, read, update, delete methods
  • Common methods for a database. Adds database abilities to help us interact with the data and perform CRUD.

""" database dependencies to support sqliteDB examples """
from random import randrange
import datetime
from datetime import datetime
import os, base64
import json

# from __init__ import app, db
from sqlalchemy.exc import IntegrityError


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

# Create class Score to add a scores table to database
class Student(db.Model):
    # Table name
    __tablename__ = 'students'

    # Define the columns for the table
    id = db.Column(db.Integer, primary_key=True)
    _name = db.Column(db.String(255), unique=False, nullable=False)
    _college = db.Column(db.String(255), unique=False, nullable=False)
    _gpa= db.Column(db.String(255), unique=False, nullable=False)
    _sat = db.Column(db.String(255), unique=False, nullable=False)
    _act = db.Column(db.String(255), unique=False, nullable=False)
    _gradDate = db.Column(db.Date)

    # Construct the profile for Score
    def __init__(self, name, college, gpa, sat, act, gradDate=datetime.today()): # variables that record the username, score, and date of score(dos)
        self._name = name
        self._college = college
        self._gpa = gpa
        self._sat = sat
        self._act = act
        if isinstance(gradDate, str):  # not a date type     
            gradDate = date=datetime.today()
        self._gradDate = gradDate
    
    # Getter method
    @property
    def name(self):
        return self._name
    
    # Setter function
    @name.setter
    def name(self, name):
        self._name = name
    
    # Getter method
    @property
    def college(self):
        return self._college
    
    # Setter function
    @college.setter
    def college(self, college):
        self._college = college

    # Getter method
    @property
    def gpa(self):
        return self._gpa
    
    # Setter function
    @gpa.setter
    def gpa(self, gpa):
        self._gpa = gpa

    # Getter method
    @property
    def sat(self):
        return self._sat
    
    # Setter function
    @sat.setter
    def sat(self, sat):
        self._sat = sat

    # Getter method
    @property
    def act(self):
        return self._act
    
    # Setter function
    @act.setter
    def act(self, act):
        self._act = act

    # Getter method
    @property
    def gradDate(self):
        gradDate_string = self._gradDate.strftime('%m-%d-%Y')
        return gradDate_string
    
    # Setter function
    @gradDate.setter
    def gradDate(self, gradDate):
        if isinstance(gradDate, str):  # not a date type     
            gradDate = date=datetime.today()
        self._gradDate = gradDate

    
    
    # # Convert dos to a string
    # @property
    # def dos(self):
    #     dos_string = self._dos.strftime('%m-%d-%Y')
    #     return dos_string
    
    # # Setter function
    # @dos.setter
    # def dos(self, dos):
    #     self._dos = dos

    # Output content using json dumps
    def __str__(self):
        return json.dumps(self.make_dict())

    # CRUD operations: create, read, update, delete
    # CREATE: returns self and returns None if there is error
    def create(self):
        try:
            # creates a user object from Score(db.Model) class
            db.session.add(self)  # add persists user object onto table
            db.session.commit()  # SqlAlchemy "unit of work pattern" requires a manual commit
            return self
        except IntegrityError:
            db.session.remove()
            return None
    
    # UPDATE: updates username and score
    def update(self, name="", college="", gpa="", sat="", act=""):
        """only updates values with length"""
        if len(name) > 0:
            self.name = name
        if len(college) > 0:
            self.college = college
        if len(gpa) > 0:
            self.gpa = gpa
        if len(sat) > 0:
            self.sat = sat
        if len(act) > 0:
            self.act = act
        db.session.add(self)
        db.session.commit()
        return self

    # DELETE: removes a user
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None

    # READ: converts self to dictionary
    # returns a dictionary
    def make_dict(self):
        return {
            "id": self.id,
            "name": self.name,
            "college": self.college,
            "GPA": self.gpa,
            "SAT": self.sat,
            "ACT": self.act,
            "gradDate": self.gradDate
        }

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
  • Works through db.create_all()
  1. User Object Constructors
  • Works through the Student() with the proper parameters(shown in p1 to p5)
  1. Try / Except
  • Try works by attempting to create each object. If it doesn't work, except takes charge, removes the faulty object, and displays an error message.

"""Database Creation and Testing """


# Builds working data for testing
def initStudents():
    with app.app_context():
        """Create database and tables"""
        db.create_all()
        """Tester data for table"""
        p1 = Student('Eric', 'Yale', '4.2', '1600','NA',datetime(2023,1,11))
        p2 = Student('Jeff', 'Harvard', '3.2', '1540','30', datetime(2023,1,22))
        p3 = Student('Bob', 'Palomar', '4.9', '1600', '36', datetime(2024,6,9))
        p4 = Student('???', 'Stanford', '4.5','1400','29',datetime(2024,6,9))
        p5 = Student('Bobby', 'UCSD', '2.0','1320','25',datetime(2025,6,10))

        profiles = [p1, p2, p3, p4, p5]

        """Builds sample user/note(s) data"""
        for profile in profiles:
            try:
                profile.create()
            except IntegrityError:
                '''fails with bad or duplicate data'''
                db.session.remove()
                print(f"Error in creating object: {profile.name}")
                
initStudents()

Extra Notes

• ORM

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
  • Filters through the data to find and match the uid in the function's parameters.
  1. user.password
  • Checks to see if there is a password existing for the user.

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

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
  • Finds to see if there is already an existing uid that is the same as the inputted uid. Try stops the code if the same uid already exists. If there is no currently existing inputted uid, except makes the code continue running.
  1. user = User(...)
  • Creates the User object.
  1. user.dob and try/except
  • user.dob is the inputted dob. Try sets the dob in the following format. Except sets it to today and returns and error message if the date isn't inputted correctly.
  1. user.create() and try/except
  • Creates the User object. Try creates the user. Except returns an error message if no object is created.

def create():
    # request value that ensure creating valid object
    name = input("Enter your name:")
    if name is None:
                return {'message': f'Invalid name'}, 210
    
    college = input("Enter your college:")
    if college is None:
                return {'message': f'Invalid college'}, 210
    
    gpa = input("Enter your GPA:")
    if gpa is None:
                return {'message': f'Invalid GPA'}, 210
    
    sat = input("Enter your SAT score:")
    if sat is None:
                return {'message': f'Invalid SAT score'}, 210

    act = input("Enter your ACT score:")
    if act is None:
                return {'message': f'Invalid ACT score'}, 210

    # Initialize User object before date
    students = Student(name=name, 
                college=college,
                gpa=gpa,
                sat=sat,
                act=act
                )
    # create user.dob, fail with today as dob
    gradDate = input("Enter your date of graduation 'MM-DD-YYYY'")
    try:
        students.gradDate = datetime.strptime(gradDate, '%m-%d-%Y').date()
    except ValueError:
        students.gradDate = datetime.today()
        print(f"Invalid date {gradDate} require MM-DD-YYYY, date defaulted to {students.gradDate}")

    # write object to database
    with app.app_context():
        try:
            object = students.create()
            print("Created\n", object.make_dict())
        except:  # error raised if object not created
            print(f"Unknown error name {name}")
        
create()

Created
 {'id': 6, 'name': 'John Mortensen', 'college': 'Stanford', 'GPA': '4.0', 'SAT': '1600', 'ACT': '36', 'gradDate': '06-09-2023'}

Reading users table in sqlite.db

Uses SQLALchemy query.all method to read data

• Comment on purpose of following
  1. User.query.all
  2. json_ready assignment, google List Comprehension

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

read()

[{'id': 1,
  'name': 'Eric',
  'college': 'Yale',
  'GPA': '4.2',
  'SAT': '1600',
  'ACT': 'NA',
  'gradDate': '01-11-2023'},
 {'id': 2,
  'name': 'Jeff',
  'college': 'Harvard',
  'GPA': '3.2',
  'SAT': '1540',
  'ACT': '30',
  'gradDate': '01-22-2023'},
 {'id': 3,
  'name': 'Bob',
  'college': 'Palomar',
  'GPA': '4.9',
  'SAT': '1600',
  'ACT': '36',
  'gradDate': '06-09-2024'},
 {'id': 4,
  'name': '???',
  'college': 'Stanford',
  'GPA': '4.5',
  'SAT': '1400',
  'ACT': '29',
  'gradDate': '06-09-2024'},
 {'id': 5,
  'name': 'Bobby',
  'college': 'UCSD',
  'GPA': '2.0',
  'SAT': '1320',
  'ACT': '25',
  'gradDate': '06-10-2025'},
 {'id': 6,
  'name': 'John Mortensen',
  'college': 'Oregon',
  'GPA': '4.0',
  'SAT': '1600',
  'ACT': '36',
  'gradDate': '06-09-2023'}]

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.

Update Functionality

def update():

    nameD = input("Enter your name:")
    if nameD is None:
                return {'message': f'Invalid name'}, 210
    
    collegeD = input("Enter your college:")
    if collegeD is None:
                return {'message': f'Invalid college'}, 210
    
    gpaD = input("Enter your GPA:")
    if gpaD is None:
                return {'message': f'Invalid GPA'}, 210
    
    satD = input("Enter your SAT score:")
    if satD is None:
                return {'message': f'Invalid SAT score'}, 210

    actD = input("Enter your ACT score:")
    if actD is None:
                return {'message': f'Invalid ACT score'}, 210
    
    with app.app_context():
        # Gets the user through the username
        profileUpdate = Student.query.filter_by(_name = nameD).first()
        if profileUpdate:
            # Updates the score for the user
            profileUpdate.update(college = collegeD)
            profileUpdate.update(gpa = gpaD)
            profileUpdate.update(act = actD)
            profileUpdate.update(sat = satD)
            # Returns a dictionary to confirm that the score was updated
            print("Updated:", profileUpdate.make_dict())
        else:
            # Error message if update fails
            print(f"{nameD} not found")

update()

Updated: {'id': 6, 'name': 'John Mortensen', 'college': 'Oregon', 'GPA': '4.0', 'SAT': '1600', 'ACT': '36', 'gradDate': '06-09-2023'}

Delete Functionality

def delete():

    # Gets the ID
    getID = int(input("Enter the ID of the profile:"))
    with app.app_context():
        # Gets the user through the ID
        profileDelete = Student.query.get(getID)
        if profileDelete:
            # Deletes the user according to its ID number
            profileDelete.delete()
            print(f"Profile #{getID} deleted")
        else:
            # Error message if delete fails
            print(f"Profile #{getID} not found")
    
delete()

Profile #1 deleted

/tmp/ipykernel_15236/78855563.py:7: LegacyAPIWarning: The Query.get() method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The method is now available as Session.get() (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  profileDelete = Student.query.get(getID)