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
"""
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
- Defines the schema and methods accociated with users in the table (how you manipulate and get data)
- db.Model inheritance
- Lets the User class use the methods and structure of db.Model
-
init method
- Is the constructor for class User, let you set the instance variables such as id and username
-
@property,@<column>.setter- Defines getter and setter methods for class User (encapsulation)
- create, read, update, delete methods
- These are the methods for reading and manipulating the database and the data in that database.
- class User
""" 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 ChessUsers(db.Model):
__tablename__ = 'chess_users'
# Define the Users schema
uid = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), unique=True, nullable=False)
password = db.Column(db.String(255), unique=False, nullable=False)
dob = db.Column(db.String(255), unique = False, nullable=False)
games = db.Column(db.String(255), unique = False, nullable=False)
# Defines a relationship between User record and Notes table, one-to-many (one user to many notes)
# notes = db.relationship("Notes", cascade='all, delete', backref='users', lazy=True)
# constructor of a User object, initializes of instance variables within object
def __init__(self, name='', uid="0", password="null", dob="11-11-1111", games=""):
self.uid = make_id()
self.name = name
self.dob = dob
self.games = ""
self.set_password(password)
# returns a string representation of object, similar to java toString()
def __repr__(self):
return "Users(" + str(self.uid) + "," + self.name + "," + str(self.dob) + str(self.games) + ")"
# CRUD create/add a new record to the table
# returns self or None on error
def create(self):
try:
# creates a person object from Users(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 {
"uid": self.uid,
"name": self.name,
"password": self.password,
"dob": self.dob,
"games": self.games
}
def read2(self):
return {
"uid": self.uid,
"name": self.name,
"dob": self.dob,
}
# CRUD update: updates users name, password, phone
# returns self
def update(self, name="", uid="", password="", dob=""):
"""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 len(dob) > 0:
self.dob = dob
db.session.commit()
return self
# CRUD delete: remove self
# None
def delete(self):
user = self.read()
db.session.delete(self)
db.session.commit()
return f"deleted {user}"
def deleteGame(self, date):
games = self.games.split('#')
games.pop(0)
for game in games:
gameStr = game.replace("\'", "\"")
thing = json.loads(gameStr)
if thing['date'] == date:
games.remove(game)
gameString = ""
for el in games:
gameString += "#" + str(el)
self.games = gameString
db.session.commit()
return gameString
# set password method is used to create encrypted password
def set_password(self, password):
"""Create hashed password."""
self.password = generate_password_hash(password, method='sha256')
db.session.commit()
# check password to check versus encrypted password
def is_password_match(self, password):
"""Check hashed password."""
result = check_password_hash(self.password, password)
return result
# required for login_user, overrides id (login_user default) to implemented userID
def get_id(self):
return self.uid
def get_name(self):
return self.name
def get_games(self):
return self.games
def update_games(self, game):
self.games += "#" + str(game)
try:
db.session.commit()
return self
except IntegrityError:
db.session.remove()
return None
def getUser(uid):
with app.app_context():
return ChessUsers.query.filter_by(uid=uid).first()
# users = ChessUsers.query.all()
# for user in users:
# if(user.get_id() == uid):
# return user
def getName(name):
return ChessUsers.query.filter_by(name=name).first()
users = ChessUsers.query.all()
for user in users:
if(user.get_name() == name):
return user
def make_id():
users = ChessUsers.query.all()
uid = 0
for user in users:
if(user.get_id() > uid):
uid = user.get_id()
if (uid < 100):
return 100
return uid + 1
def getGame(uid, date):
user = getUser(uid)
games = user.games.split('#')
for game in games:
if game.date == date:
return game
Initial Data
Uses SQLALchemy db.create_all() to initialize rows into sqlite.db
- Comment on how these work?
- Create All Tables from db Object
- Uses the schema of Users to build a table with the necessary data
- User Object Constructors
- Each u1, u2, etc are objects of class user which are created when they are initialized
- Try / Except
- The Try/Except tries to add each object created above to the database and if it fails, doesn't add the object.
- Create All Tables from db Object
"""Database Creation and Testing """
# Builds working data for testing
def createTestingData():
with app.app_context():
db.create_all()
u1 = ChessUsers(name='Toby', password="lmaobad", uid="12")
u2 = ChessUsers(name='Gene', password="WRizz", uid="123")
try:
'''add user/note data to table'''
u1.create()
u2.create()
#u5.create()
#u6.create()
except IntegrityError:
'''fails with bad or duplicate data'''
db.session.remove()
print(f"Records exist, duplicate email, or error: {u1.uid}")
createTestingData()
Check for given Credentials in users table in sqlite.db
Use of ORM (object relational model) Query object and custom methods to identify user to credentials uid and password
- Comment on purpose of following
- User.query.filter_by
- returns a user object from db based on id
- user.password
- password attribute of objeect user
- User.query.filter_by
def find_by_uid(uid):
with app.app_context():
user = ChessUsers.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")
# print(find_by_uid("indi"))
def changePassword(name, password, newPassword):
with app.app_context():
user = getName(name)
if user == None:
return
if (user.is_password_match(password)):
user.set_password(newPassword)
print(user.read())
return user.is_password_match(newPassword)
return "failed"
changePassword(name="dash", password="password", newPassword="password")
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
- returns a user object from the db based on given id
- user = User(...)
- Creates a new user object using class User
- user.dob and try/except
- user.dob is the dob attribute of the created user object
- user.create() and try/except
- user.create() runs the create function on the user object, adding the data in the object to the database
- user.find_by_uid() and try/except
def create():
# optimize user time to see if uid exists
uid = input("Enter your user id:")
user = getUser(uid)
try:
print("Found\n", user.read())
return
except:
pass # keep going
# request value that ensure creating valid object
name = input("Enter your name:")
password = input("Enter your password")
dob = input("Enter your dob")
# Initialize User object before date
# write object to database
with app.app_context():
user = ChessUsers(name=name,
password=password,
dob=dob
)
try:
object = user.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
create()
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
with app.app_context():
table = ChessUsers.query.all()
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
read() #read db before delete
def deleteUser(uid):
with app.app_context():
user = getUser(uid)
return user.delete()
# deleteUser(103)
read() #read db after delete