Unit 2.4a Hacks
Hacks for 2.3
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 saw the Flask app object in our CPT project, where it managed many if not all operations that we wanted to perform with flask
- SQLAlchemy db object The db object was also created in our CPT project, where it was used to establish a connection and perform database operations and manipulations on our 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 User abstraction encapsulates all the user attributes, such as uid and name, along with various methods like create, add, delete, and update. This abstraction is useful as it centralizes all the necessary information and operations related to users under a single name, making it convenient to modify user details and update their entry in the program at a later stage.
- db.Model inheritance
By inheriting all the methods and attributes from the
db.Modelclass, we can utilize data types for our database schema, as well as other CRUD (Create, Read, Update, Delete) operations. - init method This serves as our class constructor, responsible for initializing the user attributes when the object is created, so that we can utilize this data in our subsequent operations.
-
@property,@<column>.setterThese decorators are employed to define the getters and setters within a class or object, which generates references that can be utilized to access the class methods and attributes from outside the class. - create, read, update, delete methods Methods are used to help to solve problems with a class or to change things within the table.
""" database dependencies to support sqlite examples """
import datetime
from datetime import datetime
import json
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
class Pong(db.Model):
__tablename__ = 'pongs'
# Define the Pong schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_user1 = db.Column(db.String(255), unique=False, nullable=False)
_user2 = db.Column(db.String(255), unique=False, nullable=False)
_score1 = db.Column(db.String(255), unique=False, nullable=False)
_score2 = db.Column(db.String(255), unique=False, nullable=False)
_result1 = db.Column(db.String(255), unique=False, nullable=False)
_result2 = db.Column(db.String(255), unique=False, nullable=False)
_gameDatetime = db.Column(db.DateTime)
# constructor of a Pong object, initializes the instance variables within object (self)
def __init__(self, user1="none", user2="none", score1='0', score2='0', result1="none", result2="none", gameDatetime=datetime.now()): # variables with self prefix become part of the object,
self._user1 = user1
self._user2 = user2
self._score1 = score1
self._score2 = score2
self._result1 = result1
self._result2 = result2
self._gameDatetime = gameDatetime
# a getter method, extracts email from object
@property
def user1(self):
return self._user1
# a setter function, allows user1 to be updated after initial object creation. This one for player 1
@user1.setter
def user1(self, user1):
self._user1 = user1
@property
def user2(self):
return self._user2
# a setter function for player 2
@user2.setter
def user2(self, user2):
self._user2 = user2
@property
def score1(self):
return self._score1
# a setter function for score for player 1
@score1.setter
def score1(self, score1):
self._score1 = score1
@property
def score2(self):
return self._score2
# a setter function for score for player 2
@score2.setter
def score2(self, score2):
self._score2 = score2
@property
def result1(self):
return self._result1
# a setter function for the result of player 1
@result1.setter
def result1(self, result1):
self._result1 = result1
@property
def result2(self):
return self._result2
# a setter function for the result of player 1
@result2.setter
def result2(self, result2):
self._result2 = result2
# gameDatetime property is returned as string
@property
def gameDatetime(self):
gameDatetime_string = self._gameDatetime.strftime('%m-%d-%Y %H:%M:%S')
return gameDatetime_string
# gameDatetime should be have verification for type date
@gameDatetime.setter
def gameDatetime(self, gameDatetime):
self._gameDatetime = gameDatetime
# 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.make_dict())
# CRUD create/add a new record to the table
# returns self or None on error
def create(self):
try:
# creates a person object from Pong(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to Pongs table
db.session.commit() # SqlAlchemy "unit of work pattern" requires a manual commit
return self
except IntegrityError:
db.session.remove()
return None
# CRUD update: updates user1, user2, score1, score2, result1, result2
# returns self
def update(self, user1="", user2="", score1="", score2="", result1="", result2=""):
"""only updates values with length"""
if len(user1) == 3:
self.user1 = user1
if len(user2) == 3:
self.user2 = user2
"""only updates values with scores greater than 0"""
if int(score1) >= 0:
self.score1 = score1
if int(score2) >= 0:
self.score2 = score2
"""only updates when the results are either won or loss"""
if result1 in ("Win", "Loss"):
self.result1 = result1
if result2 in ("Win", "Loss"):
self.result2 = result2
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?
-
Create All Tables from db Object calls the
create_all()method from the db object created in the model class. I assume that takes the Object properties and generates the table with the object entries. -
User Object Constructors defined with init(), takes in the inputted data and assigns it to class variables for future operations such as adding to database.
-
Try / Except The try method runs the code within it first and runs the create function from the above code, creating the data for each of the user within
usersthen prints the uid of the object created. If the try method does no work, then it goes to the code within except, which is the print
-
"""Database Creation and Testing """
def initPong():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
game1 = Pong('AAA', 'BBB', '1', '5', 'Loss', 'Win', datetime(2023, 1, 22, 15, 30, 0))
game2 = Pong('AAB', 'ABC', '2', '5', 'Loss', 'Win', datetime(2023, 1, 21, 14, 15, 0))
game3 = Pong('AAC', 'GHI', '5', '4', 'Win', 'Loss', datetime(2023, 1, 20, 13, 0, 0))
game4 = Pong('AAD', 'FGH', '5', '1', 'Win', 'Loss', datetime(2023, 1, 19, 12, 45, 0))
game5 = Pong('AAE', 'TYU', '3', '5', 'Loss', 'Win', datetime(2023, 1, 22, 11, 30, 0))
games = [game1, game2, game3, game4, game5]
"""Builds sample game data"""
for game in games:
try:
game.create()
except IntegrityError:
'''fails with bad data'''
db.session.remove()
print(f"Error in {game.user1} and/or {game.user2}")
initPong()
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 Locates the first entry in the database where the value of the _uid coloumn is equal to a specific UID. Used to search for the object of a user to update.
- user.password accesses the password of the user.
def find_by_uid(user1):
with app.app_context():
user = Pong.query.filter_by(_user1=user1).first()
return user # returns user object
# Check credentials by finding user and verify password
def check_result(user1, result1):
# query email and return user record
user = find_by_uid(user1)
if user == None:
return False
return False
check_result("AAA", "Loss")
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 Locates and stores the object of the user in question. Try printing the user information to check if the user has been created, if NotFound error, we continue with the rest of the function
- user = User(...) Creates the user object with the inputted parameters
- user.dob and try/except Set the date of birth of our user. Try to format the date into a standard format, if an invalid input is detected, autofill with today's date
- user.create() and try/except
creates the user object instantiated in the
create()function with it's method in the Model class. This inserts our object into the database.
def create():
# optimize user time to see if uid exists
user1 = input("Enter your user id:")
game = find_by_uid(user1)
try:
print("Found\n", game.read())
return
except:
pass # keep going
# request value that ensure creating valid object
user1 = input("Enter first username:")
user2 = input("Enter second username:")
score1 = input("Enter first score:")
score2 = input("Enter second score:")
result1 = input("Enter player 1's result:")
result2 = input("Enter player 2's result:")
# Initialize User object before date
game = Pong(user1=user1,
user2=user2,
score1=score1,
score2=score2,
result1=result1,
result2=result2)
# create user.dob, fail with today as dob
gameDatetime = input("Enter your date of birth 'YYYY-MM-DD'")
try:
game.dog = datetime.strptime(gameDatetime, '%Y-%m-%d %H:%M:%S').date()
except ValueError:
game.dog = datetime.today()
print(f"Invalid date {gameDatetime} require YYYY-mm-dd, date defaulted to {game.gameDatetime}")
# write object to database
with app.app_context():
try:
object = game.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
create()
Reading users table in sqlite.db
Uses SQLALchemy query.all method to read data
- Comment on purpose of following
- User.query.all Grabs a list/iterable of all user entries in the database provided
- json_ready assignment, google List Comprehensio Json formatted data. Uses list comprehension which takes all data from a prexisting list and moifies it to a copy that we later use.
# SQLAlchemy extracts all users from database, turns each user into JSON
def read():
with app.app_context():
table = Pong.query.all()
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
def update():
uid = input("What user ID would you like to update?")
user = find_by_uid(uid) # "List Comprehensions", for each user add user.read() to list
name = input("Enter desired new name: ")
uid = input("Enter desired new id: ")
new_password = input("Enter desired new password: ")
with app.app_context():
try:
object = user.update(name, uid, new_password)
print("Updated\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
return
update()
def delete():
uid = input("What user ID would you like to delete?")
user = find_by_uid(uid)
with app.app_context():
try:
object = user.delete()
print("Deleted\n")
except: # error raised if object not created
print("Unknown error uid {uid}")
return
db.session.delete(self)
db.session.commit()
return None
delete()