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
    2. SQLAlchemy db object
"""
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 databasee
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy() # SQUALchemy object object called here


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

Model Definition

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

  • Comment on these items in the class
    • class User purpose:defining the template for user and use this template to create objects of type users - db.Model inheritance: inherit the capability to make the class use database properties, methods, and attributes. Adding to template database information
    • init method: constructor used to instantiate (make, create, derive) an object from the user class object
    • @property, @.setter: see what's inside and change what's inside (setters and getters). setter is used to change and getter is used to see whats inside</li>
    • additional methods: create read update and delete methods
    • </ul> </li> </ul> </div> </div> </div>
      """ 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 Period(db.Model):
          __tablename__ = 'periods'  # 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)
          _ovulation = 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, ovulation, dob=datetime.today()):
              self._name = name    # variables with self prefix become part of the object, 
              self._uid = uid
              self._ovulation = ovulation
              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 ovulation(self):
              return self._ovulation
      
          # a setter function, allows name to be updated after initial object creation
          @ovulation.setter
          def ovulation(self, ovulation):
              self._ovulation = ovulation
          
          # 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,
                  "ovulation": self.ovulation,
                  "dob": self.dob,
                  "age": self.age,
              }
      
          # CRUD update: updates user name, ovulation, phone
          # returns self
          def update(self, name="", uid="", ovulation=""):
              """only updates values with length"""
              if len(name) > 0:
                  self.name = name
              if len(uid) > 0:
                  self.uid = uid
              if len(ovulation) > 0:
                  self.ovulation = ovulation
              db.session.add(self)
              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
        2. User Object Constructors
        3. Try / Except
      """Database Creation and Testing """
      
      
      # Builds working data for testing
      def initPeriods():
          with app.app_context():
              """Create database and tables"""
              db.create_all() # creates all lines of data in the database
              """Tester data for table"""
              u1 = Period(name='Thomas Edison', uid='toby', ovulation='2023-03-12', dob=datetime(1847, 2, 11))
              u2 = Period(name='Nikola Tesla', uid='niko', ovulation='2023-02-12')
              u3 = Period(name='Alexander Graham Bell', uid='lex', ovulation='2023-03-09')
              u4 = Period(name='Eli Whitney', uid='whit', ovulation='2023-02-07')
              # all the users that are in the database
      
      
              periods = [u1, u2, u3, u4]
      
              """Builds sample user/note(s) data"""
              for periods in periods:
                  try:
                      '''add user to table'''
                      object = periods.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 {periods.uid}, or error.")
              # try is exxecuted if successful and except is used if the rcord already exists or if there is an error
                      
      initPeriods()
      
      Records exist uid toby, or error.
      Records exist uid niko, or error.
      Records exist uid lex, or error.
      Records exist uid whit, 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
        2. user.password
      def find_by_uid(uid):
          with app.app_context():
              period = Period.query.filter_by(_uid=uid).first() # makes query that is able to retrieve all of the data that is in the database
          return period # returns user object
      
      # Check credentials by finding user and verify password
      def check_credentials(uid, password): # verifies that password matches with user id
          # query email and return user record
          period = find_by_uid(uid)
          if period == None:
              return False
          if (period.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
        2. user = User(...)
        3. user.dob and try/except
        4. user.create() and try/except
      def create():
          # optimize user time to see if uid exists
          uid = input("Enter your user id:")
          period = find_by_uid(uid) # search to find the user
          try:
              print("Found\n", period.read())
              return
          except:
              pass # keep going
          # try is if the user is found and except is used when the user is not found with the uid
          # request value that ensure creating valid object
          name = input("Enter your name:")
          ovulation = input("Enter your last ovulation date 'YYYY-MM-DD':")
          
          # Initialize User object before date
          period = Period(name=name, 
                      uid=uid, 
                      ovulation=ovulation
                      )
          
          # create user.dob, fail with today as dob
          dob = input("Enter your date of birth 'YYYY-MM-DD'")
          try:
              period.dob = datetime.strptime(dob, '%Y-%m-%d').date()
          except ValueError:
              period.dob = datetime.today()
              print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {period.dob}")
          # if the input is correct then try is used and birthday is added to the database, if it is invalid then except block is used       
          
          # write object to database
          with app.app_context():
              try:
                  object = period.create()
                  print("Created\n", object.read())
              except:  # error raised if object not created
                  print("Unknown error uid {uid}")
              # user created successfully will print the successful user, if not then error will be printed
              
      create()
      
      Created
       {'id': 8, 'name': 'Emma Shen', 'uid': 'emmas', 'ovulation': '2023-03-10', 'dob': '07-29-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
        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 = Period.query.all() #retrieves the data and then constructs it into a query
          json_ready = [period.read() for period in table] # each user add user.read() to list
          return json_ready # print all user in json
      
      read()
      
      [{'id': 3,
        'name': 'Thomas Edison',
        'uid': 'toby',
        'ovulation': '2023-03-12',
        'dob': '02-11-1847',
        'age': 176},
       {'id': 4,
        'name': 'Nikola Tesla',
        'uid': 'niko',
        'ovulation': '2023-02-12',
        'dob': '03-19-2023',
        'age': 0},
       {'id': 5,
        'name': 'Alexander Graham Bell',
        'uid': 'lex',
        'ovulation': '2023-03-09',
        'dob': '03-19-2023',
        'age': 0},
       {'id': 6,
        'name': 'Eli Whitney',
        'uid': 'whit',
        'ovulation': '2023-02-07',
        'dob': '03-19-2023',
        'age': 0},
       {'id': 7,
        'name': 'Annika Liao',
        'uid': 'annikal',
        'ovulation': '2023-02-08',
        'dob': '09-23-2007',
        'age': 15},
       {'id': 8,
        'name': 'Emma Shen',
        'uid': 'emmas',
        'ovulation': '2023-03-10',
        'dob': '07-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.

      Update Function

      def update():
          uid = input("Enter your user id:")
          period = find_by_uid(uid)
      
          if period is None:
              print(f"Period {uid} is not found")
              return
          print(period)
      
          new_name = input("What is your new name?")
          new_uid = input("What is your new uid?")
          new_ovulation = input("What is your new ovulation date?")
      
          with app.app_context():
              try:
                  period.update(new_name, new_uid, new_ovulation)
                  print(f"Period {uid} has now been updated.")
              except:
                  print(f"Updating period {uid} was unsuccessful.")
      update()
      
      {"id": 2, "name": "claire zhao", "uid": "clairez", "ovulation": "2023-03-20", "dob": "02-01-2007", "age": 16}
      Period clairez has now been updated.
      

      Delete Function

      def delete():
          uid = input("Enter your user id:")
          period = find_by_uid(uid)
          if period is None:
              print(f"No period found with uid {uid}")
              return
          with app.app_context():
              try:
                  period.delete()
                  print(f"Period {uid} deleted")
              except:
                  print("Unknown error occured during deletion")
      delete()
      
      Period gracew deleted
      
      </div>