My Homework DB
I made a database for the purpose of teachers outputing there homework
"""
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:///hw.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 """
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 User(db.Model):
__tablename__ = 'homework' # table teacher is plural, class teacher is singular
# Define the User schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_teacher = 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)
_hw = db.Column(db.String(255), unique=False, nullable=False)
# constructor of a User object, initializes the instance variables within object (self)
def __init__(self, teacher, uid, hw, password="123qwerty"):
self._teacher = teacher
self._uid = uid
self.hw = hw
self.set_password(password)
# a teacher getter method, extracts teacher from object
@property
def teacher(self):
return self._teacher
# a setter function, allows teacher to be updated after initial object creation
@teacher.setter
def teacher(self, teacher):
self._teacher = teacher
# a getter method, extracts uid from object
@property
def uid(self):
return self._uid
# a setter function, allows uid 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 hw(self):
return self._hw
# a setter function, allows teacher to be updated after initial object creation
@hw.setter
def hw(self, hw):
self._hw = hw
@property
def password(self):
return self._password[0:10] + "..." # because of security only show 1st characters
# update password, this is conventional method used for setter
def set_password(self, password):
"""Create a hashed password."""
self._password = generate_password_hash(password, method='sha256')
# check password parameter against stored/encrypted password
def is_password(self, password):
"""Check against hashed password."""
result = check_password_hash(self._password, password)
return result
# output content using str(object) is in human readable form
# 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,
"teacher": self.teacher,
"uid": self.uid,
"hw": self.hw,
}
# CRUD update: updates user teacher, password, phone
# returns self
def update(self, teacher="", uid="", hw="", password=""):
"""only updates values with length"""
if len(teacher) > 0:
self.teacher = teacher
if len(uid) > 0:
self.uid = uid
if len(hw) > 0:
self.hw = hw
if len(password) > 0:
self.set_password(password)
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
def initUsers():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
u1 = User(teacher='Mr. Mortensen', uid='bigmort123', hw='Hacks for 2.4a and b', password='123toby')
u2 = User(teacher='Ms. Calicot', uid='ilovechem', hw='Compound Worksheet', password='123niko')
u3 = User(teacher='Mr. Curry', uid='CurrysWorld', hw='Russia Worksheet', password='123lex')
u4 = User(teacher='Ms. Boehm', uid='englishrocks', hw='Logical Fallicies Worksheet', password='123lex')
u5 = User(teacher='Ms. Huang', uid='mathontop', hw='Problems 7-45 to 7-50', password='123lex')
users = [u1, u2, u3, u4, u5]
"""Builds sample user/note(s) data"""
for user in users:
try:
'''add user to table'''
object = user.create()
print(f"Created new uid {object.uid}")
except: # error raised if object nit created
'''fails with bad or duplicate data'''
print(f"Records exist uid {user.uid}, or error.")
initUsers()
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")
def hw_create():
# optimize user time to see if uid exists
uid = input("Enter your user id:")
user = find_by_uid(uid)
try:
print("Found\n", user.read())
return
except:
pass # keep going
# request value that ensure creating valid object
teacher = input("Enter your teaching name:")
password = input("Enter your password")
hw = input("Enter the homework that is due")
# Initialize User object before date
user = User(teacher=teacher,
uid=uid,
hw=hw,
password=password,
)
# write object to database
with app.app_context():
try:
object = user.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
# hw_create()
def hw_read():
with app.app_context():
table = User.query.all()
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
hw_read()
def hw_delete():
# Checking for user before delete
teach = input("Input your uid: ")
passs = input("Input your password: ")
if check_credentials(teach, passs) == False:
return
else:
pass
with app.app_context():
# find the user matching the input uid
user = User.query.filter_by(_uid=teach).first()
if user.hw == "No Homework":
return "Homework is already none"
else:
user.hw = "No Homework"
db.session.commit()
return "Homework deleted, hopefully no more :)."
# hw_delete()
def hw_update():
# Checking for user before delete
teach = input("Input your uid: ")
passs = input("Input your password: ")
if check_credentials(teach, passs) == False:
return
else:
pass
newhw = input("What HW would you like to add")
with app.app_context():
# find the user matching the input uid
user = User.query.filter_by(_uid=teach).first()
user.hw = ""
user.hw = newhw
db.session.commit()
return user.hw
# hw_update()
import sqlite3
database = 'instance/hw.db' # this is location of database
def hw_schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('homework')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
return conn
# hw_schema()
def hw_update():
# Checking for user before delete
teach = input("Input your uid: ")
passs = input("Input your password: ")
if check_credentials(teach, passs) == False:
return
else:
pass
newhw = input("What HW would you like to add")
with app.app_context():
# find the user matching the input uid
user = User.query.filter_by(_uid=teach).first()
if user.hw == "No Homework":
user.hw = ""
user.hw = newhw
print("Read")
print(user)
else:
user.hw = user.hw + ", " + newhw
print("Read")
print(user)
db.session.commit()
return
# Delete method
def hw_delete():
# Checking for user before delete
teach = input("Input your uid: ")
passs = input("Input your password: ")
if check_credentials(teach, passs) == False:
return
else:
pass
with app.app_context():
# find the user matching the input uid
user = User.query.filter_by(_uid=teach).first()
if user.hw == "No Homework":
return "Homework is already none"
else:
user.hw = "No Homework"
print("Homework Gone")
print(user)
db.session.commit()
return
# SQLAlchemy extracts all users from database, turns each user into JSON
def hw_read():
with app.app_context():
table = User.query.all()
json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
return json_ready
# Inputs, Try/Except, and SQLAlchemy work together to build a valid database object
def hw_create():
# optimize user time to see if uid exists
admin = input("Enter the Admin password")
if admin == "123poway":
pass
else:
print("Wrong Password")
return
uid = input("Enter your user id:")
user = find_by_uid(uid)
try:
print("Found\n", user.read())
return
except:
pass # keep going
# request value that ensure creating valid object
teacher = input("Enter your teaching name:")
password = input("Enter your password")
hw = input("Enter the homework that is due")
if hw == "":
hw = "No Homework"
# Initialize User object before date
user = User(teacher=teacher,
uid=uid,
hw=hw,
password=password,
)
# write object to database
with app.app_context():
try:
object = user.create()
print("Created\n", object.read())
except: # error raised if object not created
print("Unknown error uid {uid}")
def hw_schema():
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Fetch results of Schema
results = cursor.execute("PRAGMA table_info('homework')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
return conn
# hw_schema()
print("-----------------------------------------")
print("Select an option:")
print("1. Create Account")
print("2. Read homework")
print("3. Update homework")
print("4. Delete Account")
print("5. Schema")
print("0. Exit")
print("-----------------------------------------")
def menu():
choice = input("Enter your choice: ")
if choice == "1":
hw_create()
print("-----------------------------------------")
elif choice == "2":
read = hw_read()
print("Read")
for i in read:
print(i)
print("-----------------------------------------")
elif choice == "3":
hw_update()
print("-----------------------------------------")
elif choice == "4":
hw_delete()
print("-----------------------------------------")
elif choice == "5":
hw_schema()
print("-----------------------------------------")
elif choice == "0":
return
else:
print("Invalid choice. Please try again.")
menu() # recursion to keep the menu going
# Start the menu
menu()