Backend Lesson Plan
""" database dependencies to support sqliteDB examples """
from random import randrange
from datetime import date
import os, base64
import json
from __init__ import app, db
from sqlalchemy.exc import IntegrityError
class Colleges(db.Model):
__tablename__ = 'Colleges'
# Define the colleges schema
id = db.Column(db.Integer, primary_key=True)
_college = db.Column(db.String(255), unique=True, nullable=False)
_city = db.Column(db.Text, unique=False, nullable=False)
_rate = db.Column(db.Text, unique=False, nullable=False)
_area = db.Column(db.Text, unique=False, nullable=False)
_stufac = db.Column(db.Text, unique=False, nullable=False)
_majors = db.Column(db.Text, unique=False, nullable=False)
def __init__(self, college, city, rate, area, stufac, majors):
self._college = college
self._city = city
self._rate = rate
self._area = area
self._stufac = stufac
self._majors = majors
@property
def college(self):
return self._college
# a setter function, allows exercise name to be updated after initial object creation
@college.setter
def college(self, college):
self._college = college
def is_college(self, college):
return self._college == college
@property
def city(self):
return self._city
@city.setter
def city(self, city):
self._city = city
@property
def rate(self):
return self._rate
@rate.setter
def rate(self, rate):
self._rate = rate
@property
def area(self):
return self._area
@area.setter
def area(self, area):
self._area = area
@property
def stufac(self):
return self._stufac
@stufac.setter
def stufac(self, stufac):
self._stufac = stufac
@property
def majors(self):
return self._majors
@majors.setter
def majors(self, majors):
self._majors = majors
@property
def __str__(self):
return json.dumps(self.read())
# CRUD create, adds a new record to the Colleges table
def create(self):
try:
# creates a workout object from College(db.Model)
db.session.add(self)
db.session.commit()
return self
except IntegrityError:
db.session.remove()
return None
def read(self):
return {
"id": self.id,
"college": self.college,
"city": self.city,
"rate": self.rate,
"area": self.area,
"stufac": self.stufac,
"majors": self.majors
}
def update(self,rate=""):
"""only updates values with length"""
if len(rate) > 0:
self.rate = rate
db.session.commit()
return self
def delete(self):
db.session.delete(self)
db.session.commit()
return None
def initColleges():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
i1 = Colleges(college='UCSD', city='San Diego', rate='34%', area='urban', stufac='19:1', majors='Biology, Cognitive Science, International studies')
i2 = Colleges(college='UCSC', city='Santa Cruz', rate='52%', area='suburban', stufac='18:1', majors='Computer Science, Psychology, Cell/Cellular and Molecular Biology')
i3 = Colleges(college='UCLA', city='Los Angeles', rate='12%', area='urban', stufac='17:1', majors='Sociology, Political Science and Government, and Econometrics and Quantitative Economics')
i4 = Colleges(college='UC Davis', city='LA', rate='46%', area='suburban', stufac='20:1', majors='Research and Experimental Psychology, Management Sciences and Information Systems, Neuroscience and Neurobiology')
i5 = Colleges(college='UC Berkeley',city='Berkeley', rate='14.4%', area='urban', stufac='18:1', majors='Cell/Cellular and Molecular Biology, Computer Science, and Econometrics and Quantitative Economics.')
i6 = Colleges(college='UC Riverside', city='Riverside', rate='69%', area='urban', stufac='22:1', majors='Business Administration and Management, Psychology, Biology/Biological Sciences')
i7 = Colleges(college='UC Merced', city='Merced', rate='89%', area='urban', stufac='20:1', majors='Biology/Biological Sciences, Psychology, Business Administration and Management')
i8 = Colleges(college='UCSB', city='Santa Barbara', rate='26%', area='college town', stufac='18:1', majors='Social Sciences, Biological and Biomedical Sciences, Mathematics and Statistics')
i9 = Colleges(college='UC Irvine', city='Irvine', rate='41%', area='suburban', stufac='18:1', majors='Health-Related Knowledge and Skills, Psychology, Business, Management, Marketing, and Related Support Services')
colleges = [i1, i2, i3, i4, i5, i6, i7, i8, i9]
# """Builds sample college/note(s) data"""
for college in colleges:
try:
'''add a few 1 to 4 notes per user'''
for num in range(randrange(1, 9)):
note = "#### " + college.college + " note " + str(num) + ". \n Generated by test data."
'''add college data to table'''
college.create()
except IntegrityError:
'''fails with bad or duplicate data'''
db.session.remove()
print(f"ERROR {Colleges.college}")
This is how we created the database for the colleges API. First, we define all the variables within the table and create setters and getters for each column. Then, we created the read, create, and delete commands which we can use to read the data in the database on our deployed backend page on a browser. Finally, at the end, we added tester data for each column into the database so we have information on the colleges to then pull to the frontend.
- In this case, we didn't have to use crud for users to edit the data because the information on the colleges is not meant to be edited.
- We also used this format of database creation for the reviews feature of our project
""" database dependencies to support sqliteDB examples """
from random import randrange
import os, base64
import json
from __init__ import app, db
from sqlalchemy.exc import IntegrityError
# Define the Review class to manage actions in the 'reviews' table
class Review(db.Model):
__tablename__ = 'reviews'
# Define the Review schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_school = db.Column(db.String(255), unique=False, nullable=False)
_review = db.Column(db.Text, unique=True, nullable=False)
# constructor of a Review object, initializes the instance variables within object (self)
def __init__(self, school, review):
self._school = school # variables with self prefix become part of the object,
self._review = review
# a name getter method, extracts name from object
@property
def school(self):
return self._school
# a setter function, allows name to be updated after initial object creation
@school.setter
def school(self, school):
self._school = school
@property
def review(self):
return self._review
@review.setter
def review(self, review):
self._review = review
def is_review(self, review):
return self._review == review
@property
def __str__(self):
return json.dumps(self.read())
def create(self):
try:
# creates a person object from Review(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to Reviews 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,
"school": self.school,
"review": self.review
}
# CRUD update: updates review name, password, phone
# returns self
def update(self, school="", review=""):
"""only updates values with length"""
if len(review) > 0:
self.review = review
if len(review) > 0:
self.review = review
if len(school) > 0:
self.school = school
if len(school) > 0:
self.school = school
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 """
def initReviews():
with app.app_context():
"""Create database and tables"""
# db.init_app(app)
db.create_all()
"""Tester data for table"""
u1 = Review(school='UCSD', review='Socially dead :(')
u2 = Review(school='UCSD', review='Great engineering program')
u3 = Review(school='UCI', review='Go anteaters!!!!!!!!!')
u4 = Review(school='UCM', review='Nothing fun to do at all around.')
u5 = Review(school='UCSB', review='Party School!!!!')
u6 = Review(school='UCSC', review='Great program, my son goes here')
reviews = [u1, u2, u3, u4, u5, u6]
"""Builds sample reviews/note(s) data"""
for review in reviews:
try:
'''add a few 1 to 4 notes per review'''
for num in range(randrange(1, 4)):
note = "#### " + review.school + " note " + str(num) + ". \n Generated by test data."
'''add review/post data to table'''
review.create()
except IntegrityError:
'''fails with bad or duplicate data'''
db.session.remove()
print(f"Records exist, duplicate email, or error: {review.review}")
import json
from flask import Blueprint, request, jsonify
from flask_restful import Api, Resource # used for REST API building
from datetime import datetime
from model.colleges import Colleges
from __init__ import db
college_api = Blueprint('college_api', __name__,
url_prefix='/api/colleges')
api = Api(college_api)
# API docs https://flask-restful.readthedocs.io/en/latest/api.html
class CollegesAPI:
class _Create(Resource):
def post(self):
''' Read data for json body '''
body = request.get_json()
''' Avoid garbage in, error checking '''
# validate college
college = body.get('college')
if college is None or len(college) < 2:
return {'message': f'Input College Name'}, 210
city = body.get('city')
if city is None or len(city) < 1:
return {'message': f'Input City'}, 213
area = body.get('area')
if area is None or len(area) < 2:
return {'message': f'Input Area'}, 210
rate = body.get('rate')
if rate is None or len(rate) < 0:
return {'message': f'Input number of repetitions (must be integer)'}, 214
stufac = body.get('stufac')
if stufac is None or len(stufac) < 2:
return {'message': f'Input Student-Faculty Ratio'}, 217
majors = body.get('majors')
if majors is None or len(majors) < 2:
return {'message': f'Input Majors'}, 217
from model.colleges import Colleges
io = Colleges(college=college,
city=city,
rate=rate,
area=area,
stufac=stufac,
majors=majors)
Colleges = io.create()
# success returns json of college
if Colleges:
return jsonify(Colleges.read())
# failure returns error
return {'message': f'Processed {college}, a format error or college {college} is duplicate'}, 215
class _Read(Resource):
def get(self):
colleges = Colleges.query.all() # read/extract all colleges from database
json_ready = [college.read() for college in colleges] # prepare output in json
return jsonify(json_ready) # jsonify creates Flask response object, more specific to APIs than json.dumps
class _Delete(Resource):
def delete(self):
db.session.query(Colleges).delete()
db.session.commit()
return {'message': 'All colleges have been deleted.'}
# building RESTapi endpoint
api.add_resource(_Create, '/create')
api.add_resource(_Read, '/')
api.add_resource(_Delete, '/delete')
This was the creation of our API in the backend for our colleges database. Here, we used the "from model.colleges import Colleges" command to pull the database we created into the api and create a link we could use to read the database on a browser page. After this, we created a garbage data check to make sure each column of the database table had information. This comes in handy when testing the api with applications like postman. Finally, at the end we used the read, create, and delete functions we made in the database file to build RESTapi endpoints.
- This code is also used for the review api file for the reviews function (Below)
import json
from flask import Blueprint, request, jsonify
from flask_restful import Api, Resource # used for REST API building
from model.reviews import Review
from __init__ import db
review_api = Blueprint('review_api', __name__,
url_prefix='/api/reviews')
# API docs https://flask-restful.readthedocs.io/en/latest/api.html
api = Api(review_api)
class ReviewPI:
class _Create(Resource):
def post(self):
''' Read data for json body '''
body = request.get_json()
''' Avoid garbage in, error checking '''
# validate school
school = body.get('school')
if school is None or len(school) < 2:
return {'message': f'School is missing, or is less than 2 characters'}, 400
# validate review
review = body.get('review')
if review is None or len(review) < 1:
return {'message': f'Review is missing'}, 400
''' #1: Key code block, setup REVIEW OBJECT '''
so = Review(school=school,
review=review)
''' #2: Key Code block to add REVIEW to database '''
# create review in database
review = so.create()
# success returns json of review
if review:
return jsonify(review.read())
# failure returns error
return {'message': f'Processed {school}, either a format error or rEVEIE {review} is duplicate'}, 400
class _Read(Resource):
def get(self):
reviews = Review.query.all() # read/extract all REVIEWS from database
json_ready = [review.read() for review in reviews] # prepare output in json
return jsonify(json_ready) # jsonify creates Flask response object, more specific to APIs than json.dumps
class _Delete(Resource):
def delete(self):
db.session.query(Review).delete()
db.session.commit()
return {'message': 'All reviews have been deleted.'}
# building RESTapi endpoint
api.add_resource(_Create, '/create')
api.add_resource(_Read, '/')
api.add_resource(_Delete, '/delete')
When the backend database and api is functional, we are able to pull information from the database into the frontend table via the API link we created.