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:implements a WSGI application and acts as the central object. It is passed the name of the module or package of the application. Once it is created it will act as a central registry for the view functions, the URL rules, template configuration and much more. 2. SQLAlchemy db object: SQLAlchemy is a library that facilitates the communication between Python programs and 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:Classes provide a means of bundling data and functionality together. User has many attributes, such as uid, name... - db.Model inheritance: A model where an entity (often called the child entity) is derived (it inherits) from another entity (the parent entity). They get passed down.
    • init method: The init function is called every time an object is created from a class. The init method lets the class initialize the object's attributes and serves no other purpose.
    • @property, @<column>.setter: Pythonic way to use getters and setters
    • create, read, update, delete methods: Methods to manipulate the data in a given 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
""" database dependencies to support sqliteDB examples """

# Define the Score class to manage actions in the 'score' table
class Scores(db.Model):
    __tablename__ = 'scores1'

    # Define the User schema with "vars" from object
    id = db.Column(db.Integer, primary_key=True)
    _name = db.Column(db.String(255), unique=True, nullable=False)
    _score = 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, score, dob=datetime.today()):
        self._name = name    # variables with self prefix become part of the object,
        self._score = score 
        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
    
    @property
    def score(self):
        return self._score
    
    @score.setter
    def score(self, score):
        self._score = score
        
    def is_score(self, score):
        return self._score == score

    @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

    @property
    def age(self):
        today = datetime.today()
        return today.year - self._dob.year - ((today.month, today.day) < (self._dob.month, self._dob.day))
    
    @property
    def __str__(self):
        return json.dumps(self.read())

    def create(self):
        try:
            # creates a person object from Score(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,
            "score": self.score,
            "dob": self.dob,
            "age": self.age,
        }

    # CRUD update: updates user name, password, phone
    # returns self
    def update(self, name="", score=""):
        """only updates values with length"""
        if len(name) > 0:
            self.name = name
        if len(score) > 0:
            self.score = score
        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:If the try does not work, then the except will be executed. ORM=
"""Database Creation and Testing """

def initScores():
    with app.app_context():
        """Create database and tables"""
        # db.init_app(app)
        db.create_all()
        """Tester data for table"""
        u1 = Score(name='Shruthi', score='2',dob=datetime(2006, 4, 14))
        u2 = Score(name='Claire', score='3',dob=datetime(2005, 2, 11))
        u3 = Score(name='Grace', score='1',dob=datetime(2005, 9, 11))
        u4 = Score(name='Noor', score='5',dob=datetime(2004, 2, 11))
        u5 = Score(name='Jiya', score='6',dob=datetime(2004, 8, 9))
        u6 = Score(name='Kavya', score='1', dob=datetime(2009, 8, 30))

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

        """Builds sample user/note(s) data"""
        for user in users:
            try:                        #try to add new user
                '''add user to table'''
                object = user.create()
                print(f"Created new name {object.name}")
            except:  # error raised if it does not work        
                '''fails with bad or duplicate data'''
                print(f"Records exist name {user.name}, or error.")
    
initScores()     
Created new name Shruthi
Created new name Claire
Created new name Grace
Created new name Noor
Created new name Jiya
Created new name Kavya

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 User, by a specific attribute. 2. user.password: Sets user's pwd.
def find_by_name(name):
    with app.app_context():
        score = Score.query.filter_by(_name=name).first()
                    #
    return score # returns user object

# Check credentials by finding user and verify password
def check_credentials(name,dob):
    user = find_by_name(name)
    if user == None:
        return False
    if (user.is_dob(dob)):
        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:Find a specific entry by searching by uid 2. user = User(...): initializes/sets the User object
    2. user.dob and try/except: Creates the User attribute of dob. If DOB is set to be todays date, except will be run.
    3. user.create() and try/except: Creates new user. Error will be raised if the user was not build.
def create():
    # optimize user time to see if uid exists
    name = input("Enter your name:")
    user = find_by_name(name)
    try:
        print("Found\n", user.read())
        return
    except:
        pass # keep going

    
    # request value that ensure creating valid object
    score = input("Enter your score:")
    
    # Initialize User object before date
    user = Score(name=name, 
                score=score, 
                )
    
    # 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 score {score}")
create()

# tested "Shruthi"
Created
 {'id': 7, 'name': 'Sarah', 'score': '8', 'dob': '01-09-2006', 'age': 17}

Reading users table in sqlite.db

Uses SQLALchemy query.all method to read data

  • Comment on purpose of following
    1. User.query.all
      • searches through all the users in User
    2. json_ready assignment, google List Comprehension
      • List comprehensions provide a concise way to create list
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
    with app.app_context():
        table = Score.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': 'Shruthi', 'score': '2', 'dob': '04-14-2006', 'age': 16},
 {'id': 2, 'name': 'Claire', 'score': '3', 'dob': '02-11-2005', 'age': 18},
 {'id': 3, 'name': 'Grace', 'score': '1', 'dob': '09-11-2005', 'age': 17},
 {'id': 4, 'name': 'Noor', 'score': '5', 'dob': '02-11-2004', 'age': 19},
 {'id': 5, 'name': 'Jiya', 'score': '6', 'dob': '08-09-2004', 'age': 18},
 {'id': 6, 'name': 'Kavya', 'score': '1', 'dob': '08-30-2009', 'age': 13},
 {'id': 7, 'name': 'Sarah', 'score': '8', 'dob': '01-09-2006', 'age': 17}]
  • 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.