Unit 2.4a Using Programs with Data, SQLAlchemy
Using Programs with Data is focused on SQL and database actions. Part A focuses on SQLAlchemy and an OOP programming style,
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.
- Flask app object
Occurs on the line 'app = Flask(name)', its purpose is to set up an Flask object
- SQLAlchemy db object
Takes place at the line 'db = SQLAlchemy()' and it serves 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 a new template for the object
- db.Model inheritance
SQLite table and database is created
- init method
Helps create the object
-
@property,@<column>.setter
@property creates the values and @< column >.setter changes those values
- create, read, update, delete methods
The methods used to alter and change an database, interacting with it.
""" 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 college data
def __init__(self, name, college, gpa, sat, act, gradDate=datetime.today()): # variables that record the users information
self._name = name
self._college = college
self._gpa = gpa
self._sat = sat
self._act = act
if isinstance(gradDate, str):
gradDate = date=datetime.today()
self._gradDate = gradDate
@property
def name(self):
return self._name
@name.setter
def name(self, name):
self._name = name
@property
def college(self):
return self._college
@college.setter
def college(self, college):
self._college = college
@property
def gpa(self):
return self._gpa
@gpa.setter
def gpa(self, gpa):
self._gpa = gpa
@property
def sat(self):
return self._sat
@sat.setter
def sat(self, sat):
self._sat = sat
@property
def act(self):
return self._act
@act.setter
def act(self, act):
self._act = act
@property
def gradDate(self):
gradDate_string = self._gradDate.strftime('%m-%d-%Y')
return gradDate_string
@gradDate.setter
def gradDate(self, gradDate):
if isinstance(gradDate, str): # not a date type
gradDate = date=datetime.today()
self._gradDate = gradDate
def __str__(self):
return json.dumps(self.make_dict())
# CRUD operations: create, read, update, delete
# CREATE: Creates new profile
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 name and college
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 profile
def delete(self):
db.session.delete(self)
db.session.commit()
return None
# READ: converts self to 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?
- Create All Tables from db Object
Works by running the code db.create_all()
- User Object Constructors
Is created from the User() and the right formatting
- Try / Except
"Try" attempts to create each object and "Except" removes the object and gives 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('Max', 'Purdue', '4.2', '1600','NA',datetime(2023,2,23))
p2 = Student('Ellen', 'Stanford', '4.9', '1600','36', datetime(2023,1,19))
p3 = Student('Joe', 'Palomar', '1.9', '1000', '24', datetime(2024,3,29))
p4 = Student('Gavin', 'MIT', '4.5','1600','33',datetime(2023,8,13))
p5 = Student('Joey', 'UCDavis', '2.0','1320','25',datetime(2024,6,13))
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()
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
- User.query.filter_by
Filters through the data by the requested parameter in this case uid
- user.password
Checks for the password of each of the users
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
- user.find_by_uid() and try/except
Try to see if there is a uid that matches the requested uid, and if its not found, it ignores that and keeps going on with the code
- user = User(...)
Creates an object for the user
- user.dob and try/except
user.dob is the birthday that the user inputs and try sets it into the format and except will set it to today and return an error msg
- user.create() and try/except
This creates the user object and try creates the user and except gives and error msg if no object is created
# Inputs, Try/Except, and SQLAlchemy work together to build a valid database object
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()
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
- Comment on purpose of following
- User.query.all
Creates and table of all the data in the database
- json_ready assignment, google List Comprehension
Its gives a much shorter syntax for the list and makes it easier to read and understand a List
# 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()
def update():
nameUp = input("Enter your name:")
if nameUp is None:
return {'message': f'Invalid name'}, 210
collegeUp = input("Enter your college:")
if collegeUp is None:
return {'message': f'Invalid college'}, 210
gpaUp = input("Enter your GPA:")
if gpaUp is None:
return {'message': f'Invalid GPA'}, 210
satUp = input("Enter your SAT score:")
if satUp is None:
return {'message': f'Invalid SAT score'}, 210
actUp = input("Enter your ACT score:")
if actUp is None:
return {'message': f'Invalid ACT score'}, 210
with app.app_context():
# Gets the user through the username
updateProf = Student.query.filter_by(_name = nameUp).first()
if updateProf:
# Updates the score for the user
updateProf.update(college = collegeUp)
updateProf.update(gpa = gpaUp)
updateProf.update(act = actUp)
updateProf.update(sat = satUp)
# Returns a dictionary to confirm that the score was updated
print("Updated:", updateProf.make_dict())
else:
# Error message if update fails
print(f"{nameUp} not found")
update()
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()