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:I have seen the flask app object being used when we created our databases previously on our flask servers. 2. SQLAlchemy db object: This is also related to the database creations in our flask, talked about above. It is used to work with others data and create our own data.
"""
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:The purpose of the class is to define the attributes and behaviors of a user object. For example in this case, class User includes attrubutes that are associated with personal information of a user, like name, user id, password, date of birth, and age. The class also includes methods for creating, updating, and deleting user accounts (will talk more about below) and authentication systems, for example to check if the password is valid.
-
db.Model inheritance: Often used as base class for creating models that represent data tables, provides various methods and attributes for defining a databse schema and querying data. Inhertance from db.Model allows creation of new models that can inherit or take all the properties and behavors of db.Model class, and allows defining additional attributes and methods specific to the new model. The db.Model in this case is the parameter of class User.
-
init method: init is method in Python classes that is uesd to initilize an object, also called the constructor object. It is called when an object is created from a class. This method sets up the inital state of an object.
-
@property,@<column>.setterWith the getter, we get and we are reading it, it is the read function. With the setter, we can create name or user ID or update a previous name or user ID. These property and setter functions allow us to create, read, update, easier because we have a function we can use to get their name and user ID and update it. -
create, read, update, delete methods CRUD are the four basic functions of storage to manage data stored in databases. Create method is used to add new data records to a database. Read method is used to retrieve data records from database. Update is to modify exisitng data in a database. Delete is to remove data records from a 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
class Leader(db.Model):
__tablename__ = 'leaders' # 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)
_score = db.Column(db.String(255), unique=False, nullable=False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, name, uid, password="123qwerty", dob=datetime.today(), score=6):
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
self._score = score
# 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 email from object
@property
def uid(self):
return self._uid
# a setter function, allows name 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 setter
def set_password(self, password):
"""Create a hashed password."""
self._password = generate_password_hash(password, method='sha256')
# check password parameter versus 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, to avoid unfriendly outcomes
@property
def dob(self):
dob_string = self._dob.strftime('%m-%d-%Y')
return dob_string
# dob should be have verification for type date
@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))
# a name getter method, extracts name from object
@property
def score(self):
return self._score
# a setter function, allows name to be updated after initial object creation
@score.setter
def score(self, score):
self._score = score
# output content using str(object) in human readable form, uses getter
# 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,
"score": self.score,
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, name="", uid="", password="", dob="", score=""):
"""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)
if isinstance(dob, str): # not a date type
dob = date=datetime.today()
else:
self.dob = dob
if len(score) > 0 and score == int(score) and score > 0 and score <= 6:
self.score = score
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
Putting a breakpoint at u6 and initUsers(), debug, then press continue button
- Comment on how these work?
-
Create All Tables from db Object create_all() function creates the SQLite table so the user objects can be added into it
-
User Object Constructors The user object takes one or more parameters that specifies the user's properties defined by the arugments in the User class.
-
Try / Except Try and except are used for exception handling when an error occurs, can be used as a validation process. Try lets you test a code block for errors, and the except block lets you handle the error if there is an error.
"""Database Creation and Testing """
# Builds working data for testing
def initLeaders():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
u1 = Leader(name='Thomas Edison', uid='toby', password='123toby', score=5)
u2 = Leader(name='Nikola Tesla', uid='niko', password='123niko', score=3)
u3 = Leader(name='Alexander Graham Bell', uid='lex', password='123lex', score=4)
u4 = Leader(name='Eli Whitney', uid='whit', password='123whit', score = 1)
u5 = Leader(name='Indiana Jones', uid='indi', score = 2, dob=datetime(1920, 10, 21))
u6 = Leader(name='Marion Ravenwood', uid='raven', score = 4, dob=datetime(1921, 10, 21))
leaders = [u1, u2, u3, u4, u5, u6]
"""Builds sample user/note(s) data"""
for leader in leaders:
try:
'''add user to table'''
object = leader.create()
print(f"Created new uid {object.uid}")
except: # error raised if object not created
'''fails with bad or duplicate data'''
print(f"Records exist uid {leader.uid}, or error.")
initLeaders()
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 Query the user and find it by the given UID and see if it matches using the password as a checker.
-
user.password Checks if the password input matches based on the UID, so now there are two layers of verification (UID and password)
def find_by_uid(uid):
with app.app_context():
leader = Leader.query.filter_by(_uid=uid).first()
return leader # returns user object
# Check credentials by finding user and verify password
def check_credentials(uid, password):
# query email and return user record
leader = find_by_uid(uid)
if leader == None:
return False
if (leader.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 It assigns the user function to the user associated with the specified UID variable. If a user with the inputted UID does not exist in the database, the find_by_uid procedure will not work. So, the print function inside the try block will encounter an error while attempting to access the user variable, which has not been defined. This triggers the except block,and the pass statement will be executed to proceed with the user creation process.
-
user = User(...) The User object, with the name, user ID, and password inputted earlier, is assigned to the user variable. This variable is then used in user.dob, user.create, etc.
-
user.dob and try/except The program endeavors to generate the user's date of birth by adding the attribute directly using user.dob, based on the year, month, and day input. However, if the date of birth is incorrectly inputted, the .strptime function will not succeed and the try block will encounter an error. This leads to the execution of the except block. In this case, a default date of birth, set as the current date, will be assigned to the user.
-
user.create() and try/except This is for creating a new user by adding a new user an their information to the database. This is within a try except condtion because it must validate the information, for example, to check if the user inputted UID is a unique UID. The try sees if it fails, then it will trigger the except block to cancel the process and state that an error occured.
def create():
# optimize user time to see if uid exists
uid = input("Enter your user id:")
leader = find_by_uid(uid)
try:
print("Found\n", leader.read())
return
except:
pass # keep going
# request value that ensure creating valid object
name = input("Enter your name:")
password = input("Enter your password")
score = input("Enter your score")
# Initialize User object before date
leader = Leader(name=name,
uid=uid,
password=password,
score=score
)
# create leader.dob, fail with today as dob
dob = input("Enter your date of birth 'YYYY-MM-DD'")
try:
leader.dob = datetime.strptime(dob, '%Y-%m-%d').date()
except ValueError:
leader.dob = datetime.today()
print(f"Invalid date {dob} require YYYY-mm-dd, date defaulted to {leader.dob}")
# write object to database
with app.app_context():
try:
object = leader.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
create()
Above, I created a leader "Aliya" to be on the leaderboard table
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
- Comment on purpose of following
-
User.query.all Searches for all the users in the sqlite file and change each of the users to json. We should convert it to JSON because it is applicable in most code lanauge, so it is easy for systems to read. It is very universal and can covert to other languages as well back and forth.
-
json_ready assignment, google List Comprehension json_ready variable is the converted, correctly fomaratted JSON User data. It is returned inside the read function so then the data can be read and formatted nicely.
# SQLAlchemy extracts all leaders from database, turns each leader into JSON
def read():
with app.app_context():
table = Leader.query.all()
json_ready = [leader.read() for leader in table] # "List Comprehensions", for each leader add leader.read() to list
return json_ready
read()
You can see the leader "Aliya" read and displayed, at the bottom of the table
def update():
# first require uid input
uid = input("Enter your user id:")
leader = find_by_uid(uid)
if leader is None:
print("Leader with user ID " + uid + " not found.")
return
print(leader)
password = input("Enter password of leader to update information:")
# Check credentials by finding user and verify password
if check_credentials(uid, password) == False:
print("The password you entered is incorrect.")
return
# create new User attributes
new_name = input("Update your name:")
new_uid = input("Update your User ID:")
checkuid = find_by_uid(new_uid)
if (checkuid is not None) and (new_uid != uid):
print(f"The user ID {new_uid} is a duplicate.")
return
new_password = input("Update your password:")
new_dob = input("Update your dob:")
try:
dobformat = datetime.strptime(new_dob, '%Y-%m-%d').date()
except ValueError:
dobformat = datetime.today()
print(f"Date {new_dob} incorrect format. Date set to {dobformat}")
temp_score = input("Update your score:")
if int(temp_score)<1 or int(temp_score)>6:
new_score = 6
print(f"Score {temp_score} invalid. Score set to {new_score}")
else:
new_score = temp_score
# update the leader in the database
with app.app_context():
try:
updated = leader.update(name=new_name, uid=new_uid, password=new_password, dob=dobformat, score=new_score)
print("Updated leader:\n", updated.read())
except: # error raised if object not created
print("Updating leader {uid} unsucessful.")
update()
Now, I updated the leader "Aliya" to be changed to my sister and her information "Alice"
def delete():
# first require uid input
uid = input("Enter the user id to delete:")
leader = find_by_uid(uid)
if leader is None:
print("Leader with user ID " + uid + " not found.")
return
print(leader)
password = input("Enter password of leader to delete:")
if check_credentials(uid, password):
pass
else:
print("Incorrect password")
return
with app.app_context():
try:
leader.delete()
print("User has been deleted successfully.")
except:
print("There was an unknown error deleting the given user.")
delete()
Lastly, I deleted this "Alice" leader