"""
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:///sports.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)
""" 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 Sport(db.Model):
__tablename__ = 'sportss' # table name is plural, class name is singular
# Define the User schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_sport = db.Column(db.String(255), unique=False, nullable=False)
_sid = db.Column(db.String(255), unique=True, nullable=False)
_comp = db.Column(db.String(255), unique=False, nullable=False)
_outside = db.Column(db.String(255), unique=False, nullable=False)
_ball = db.Column(db.String(255), unique=False, nullable=False)
_contact = db.Column(db.String(255), unique=False, nullable=False)
_team = db.Column(db.String(255), unique=False, nullable=False)
_running = db.Column(db.Integer, unique=False, nullable=False)
#_population = db.Column(db.Integer, unique=False, nullable=False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, sport, sid, comp, outside, ball, contact, team, running):
self._sport = sport # variables with self prefix become part of the object,
self._sid = sid
self._comp = comp
self._outside = outside
self._ball = ball
self._contact = contact
self._team = team
self._running = running
#self._population = population
# a name getter method, extracts name from object
@property
def sport(self):
return self._sport
# a setter function, allows name to be updated after initial object creation
@sport.setter
def sport(self, sport):
self._sport = sport
# a getter method, extracts uid from object
@property
def sid(self):
return self._sid
# a setter function, allows uid to be updated after initial object creation
@sid.setter
def sid(self, sid):
self._sid = sid
# check if uid parameter matches user id in object, return boolean
def is_sid(self, sid):
return self._sid == sid
@property
def comp(self):
return self._comp
# a setter function, allows name to be updated after initial object creation
@comp.setter
def comp(self, comp):
self._comp = comp
@property
def outside(self):
return self._outside
# a setter function, allows name to be updated after initial object creation
@outside.setter
def outside(self, outside):
self._outside = outside
@property
def ball(self):
return self._ball
# a setter function, allows name to be updated after initial object creation
@ball.setter
def ball(self, ball):
self._ball = ball
@property
def contact(self):
return self._contact
# a setter function, allows name to be updated after initial object creation
@contact.setter
def contact(self, contact):
self._contact = contact
@property
def team(self):
return self._team
# a setter function, allows name to be updated after initial object creation
@team.setter
def contact(self, team):
self._team = team
@property
def running(self):
return self._running
# a setter function, allows name to be updated after initial object creation
@running.setter
def running(self, running):
self._running = running
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,
"sport": self.sport,
"sid": self.sid,
"comp": self.comp,
"outside": self.outside,
"ball": self.ball,
"contact": self.contact,
"team": self.team,
"running": self.running,
}
# CRUD update: updates user name, password, phone
# returns self
def update(self, sport="", comp="", outside="", ball="", contact="", team="", running=""):
"""only updates values with length"""
if len(sport) > 0:
self.sport = sport
if len(comp) > 0:
self.comp = comp
if len(outside) > 0:
self.outside = outside
if len(ball) > 0:
self.ball = ball
if len(contact) > 0:
self.contact = contact
if len(team) > 0:
self.team = team
if len(running) > 0:
self.running = running
db.session.merge(self)
db.session.commit()
return self
# CRUD delete: remove self
# None
def delete(self):
db.session.delete(self)
db.session.commit()
return None
"""Database Creation and Testing """
# Builds working data for testing
# SQLAlchemy extracts single user from database matching User ID
def find_by_sid(sid):
with app.app_context():
place = Sport.query.filter_by(_sid=sid).first()
return place # returns user object
# Check credentials by finding user and verify password
def check_credentials(sid, comp):
# query email and return user record
activity = find_by_sid(sid)
if activity == None:
return False
if (activity.is_comp(comp)):
return True
return False
def find_by_sport(sport):
with app.app_context():
place = Sport.query.filter_by(_sport=sport).first()
return place # returns user object
# Check credentials by finding user and verify password
def check_credentials(sport):
# query email and return user record
place = find_by_sid(sport)
if place == None:
return False
def initSports():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
u1 = Sport(sport='Basketball', sid='Bas', comp='Yes', outside='No', ball='Yes', contact='Yes', team='Yes', running='Yes')
u2 = Sport(sport='Football', sid='Foot', comp='Yes', outside='Yes', ball='Yes', contact='Yes', team='Yes', running='Yes')
sports = [u1, u2]
"""Builds sample user/note(s) data"""
for sport in sports:
try:
'''add user to table'''
object = sport.create()
print(f"Created new cid {object.sid}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist sid {sport.sid}, or error.")
initSports()
#check_credentials("indi", "123qwerty")
# Inputs, Try/Except, and SQLAlchemy work together to build a valid database object
def create():
# optimize user time to see if uid exists
cid = input("Enter your user cid:")
place = find_by_cid(cid)
try:
print("Found\n", place.read())
return
except:
pass # keep going
# request value that ensure creating valid object
country = input("Enter the country:")
continent = input("Enter the continent")
population = input("Enter the population'")
# Initialize User object before date
place = Country(country=country,
cid=cid,
continent=continent,
population=population
)
# create user.dob, fail with today as dob
# write object to database
with app.app_context():
try:
object = place.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error cid {cid}")
create()
def read():
with app.app_context():
table = Country.query.all()
json_ready = [place.read() for place in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
read()
def update():
# optimize user time to see if uid exists
cid = input("Enter your cid:")
place = find_by_cid(cid)
if place is None:
print(f"User {cid} is not found")
return
new_country = input("What is your new country: ")
new_continent = input("What is your new continent: ")
new_population = input("What is your new population: ")
with app.app_context():
try:
place.update(new_country, new_continent, new_population)
print(f"Cid, {cid}, has been updated with the country, {new_country}, with the continent {new_continent}, and the population, {new_population}")
except:
print(f"There was a problem in updating the country, {cid}")
update()
def delete():
# optimize user time to see if uid exists
cid = input("Enter your cid:")
place = find_by_cid(cid)
if place is None:
print(f"Country, {cid} is not found :(")
with app.app_context():
try:
place.delete()
print(f"Country, {cid} has been deleted.")
except:
print("Enter a country link that already exists")
delete()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif operation.lower() == 's':
schema()
elif len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")
2.4b Hacks
- Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
- In this implementation, do you see procedural abstraction?
In 2.4a or 2.4b lecture
Do you see data abstraction? Complement this with Debugging example.
- Yes I do see data abstraction because by usuing this form of databases, we are narrowing down the code we have to use. In fact, for the users technecally we don't have to use any code. We can let the website users add their names and accounts and create all the data for us. Also, we can choose one row or column from the table to only read or alter one piece of the data.
- This piece of code creates a new user and defines it properties. This user can be updated by the update command but if this same code is run again it will override the update command
- This puts the different types of data into the table
- This code creates the properties of the table. It has setters and getters and is very important because there would be no table rows or columns without this piece of code.
- This piece of code also helps create properties. It decides if a property is going to be unique or not. So a uid would be unique because it is like a username and every user has a different username.
- This piece of code actually creates the database file
- Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.
Reference... sqlite documentation