def post(self):
"""Insert new building."""
query = "INSERT INTO building VALUES (%s, %s)"
json_data = request.get_json()
parameters = (json_data['building_id'], json_data['building_name'])
execute_query(query, parameters)
return jsonify(msg="Insert successful.")
def patch(self, event_id):
"""Edit an event info."""
json_data = request.get_json()
parameters = []
set_query = []
event_attributes = [
"class_title", "subject", "course_num", "start_time", "end_time",
"days", "space_id", "instructor_first", "instructor_last",
"semester_id", "crn"
]
for attr in event_attributes:
if attr in json_data:
set_query.append(f"{attr} = %s")
parameters.append(json_data[attr])
set_query = ", ".join(set_query)
parameters.append(f"{json_data['crn']}{json_data['semester_id']}")
parameters = tuple(parameters)
query = f"UPDATE class SET {set_query} WHERE CONCAT(crn, semester_id) = %s"
print(query)
print(parameters)
execute_query(query, parameters)
return jsonify(msg="Edit successful.")
def patch(self, user_id):
""" Replaces information of corresponding user_id with request body """
query = f"""update users set user_id = %s """
query += f"""where user_id = %s"""
json_data = request.get_json()
parameters = (json_data['user_id'], user_id)
database_utilities.execute_query(query, parameters)
def post(self):
""" Insert data for a new admin """
query = f"""insert into admins values (%s);"""
json_data = request.get_json()
parameters = (json_data['email'], )
database_utilities.execute_query(query, parameters)
return jsonify(msg="Insert successful.")
def post(self):
""" Insert data for new space """
query = f"""insert into spaces values (%s, %s, %s, %s, %s);"""
json_data = request.get_json()
parameters = (json_data['space_id'], json_data['building_id'],
json_data['name'], json_data['capacity'],
json_data['features'])
execute_query(query, parameters)
return jsonify(msg="Insert successful.")
def patch(self, building_id):
"""Edit a building info."""
query = """
UPDATE building
SET building_name = %s, building_id = %s
WHERE building_id = %s
"""
json_data = request.get_json()
parameters = (json_data['new_name'], json_data['new_id'], building_id)
execute_query(query, parameters)
return jsonify(msg="Edit successful.")
def get(self):
""" Fetch data for all spaces """
# Parse request for parameters
parser = reqparse.RequestParser()
parser.add_argument('building_id')
parser.add_argument('datetime')
args = parser.parse_args()
# Build query strings
where_query = "WHERE building_id = %s" if args['building_id'] else ''
query = f"SELECT * FROM spaces {where_query}"
parameters = (args['building_id'],)
spaces_query_results = execute_query(query, parameters)
# If provided a datetime, get availability data
if args['datetime'] and args['building_id']:
# Get events of queried spaces
space_ids = [row["space_id"] for row in spaces_query_results]
events_query_results = execute_query(
"SELECT * FROM class WHERE space_id IN %s", (tuple(space_ids),))
datetime_obj = parse_datetime(args["datetime"])
day_of_the_week = get_day_of_the_week_from_datetime(datetime_obj)
time_obj = datetime_obj.time()
current_semester = get_semester_from_datetime(datetime_obj)
for space in spaces_query_results:
space["is_available"] = "Available"
for event in events_query_results:
# Check matching space_id and semester_id
if (space["space_id"] == event["space_id"]
and event["semester_id"] == current_semester):
# Check day
day_check = day_of_the_week in list(event["days"])
# Check time
time_check = (event["start_time"] < time_obj
and time_obj < event["end_time"])
if day_check and time_check:
space["is_available"] = event["class_title"]
break
return spaces_query_results
def get_semester_from_datetime(datetime_obj):
"""Find semester record that intersects with given datetime object."""
return execute_query(
"""
SELECT *
FROM semester
WHERE start_date <= %s AND end_date > %s""",
(datetime_obj, datetime_obj))[0]["semester_id"]
def post(self):
""" Returns JWT upon login verification """
json_data = request.get_json()
if 'email' not in json_data or not json_data['email']:
return make_response(jsonify(msg="Missing email"), 401)
data = database_utilities.execute_query(
"SELECT * FROM admins WHERE email = %s", (json_data['email'], ))
return jsonify({"is_admin": len(data) > 0})
def post(self):
"""Insert new event."""
query = """
INSERT INTO class
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
json_data = request.get_json()
parameters = (
json_data['class_title'],
json_data['subject'],
json_data['course_num'],
json_data['start_time'],
json_data['end_time'],
json_data['days'],
json_data['space_id'],
json_data['instructor_first'],
json_data['instructor_last'],
json_data['semester_id'],
json_data['crn'],
)
execute_query(query, parameters)
return jsonify(msg="Insert successful.")
def patch(self, space_id):
"""Updates a space record."""
json_data = request.get_json()
parameters = []
set_query = []
space_attributes = ["space_id", "building_id",
"name", "capacity", "features"]
for attr in space_attributes:
if attr in json_data:
set_query.append(f"{attr} = %s")
parameters.append(json_data[attr])
set_query = ", ".join(set_query)
parameters.append(space_id)
parameters = tuple(parameters)
query = f"UPDATE spaces SET {set_query} WHERE space_id = %s"
print(query)
print(parameters)
execute_query(query, parameters)
return jsonify(msg="Edit successful.")
def get(self):
"""Fetch data for all events."""
# Parse request for parameters
parser = reqparse.RequestParser()
parser.add_argument('space_id')
args = parser.parse_args()
# Build query strings
where_query = "WHERE space_id = %s" if args['space_id'] else ''
query = f"SELECT * FROM class {where_query}"
parameters = (args['space_id'], )
results = execute_query(query, parameters)
convert_datetimes_in_query_results(results)
print(results)
return results
def delete(self, email):
""" Deletes admin with the corresponding email """
database_utilities.execute_query(
f"""delete from admins where email = %s""", (email, ))
return jsonify(msg="Delete successful.")
def delete(self, building_id):
"""Delete a building"""
query = "DELETE FROM building WHERE building_id = %s"
parameters = (building_id, )
execute_query(query, parameters)
return jsonify(msg="Delete successful.")
from utilities import database_utilities
import os
os.chdir(os.path.dirname(__file__))
path = os.path.join(os.getcwd(), "words.db")
all_rows = database_utilities.execute_query("select * from words order by usage_count desc", database_path=path)
for row in all_rows:
# row[0] returns the first column in the query (name), row[1] returns email column.
print('{0} : {1}'.format(row[0], row[1]))
def delete(self, user_id):
""" Deletes user with the corresponding user_id """
return database_utilities.execute_query(
f"""delete from users where user_id = %s""", (user_id, ))
def get(self, user_id):
""" Fetch data for user with corresponding user_id """
return database_utilities.execute_query(
f"""select * from users where user_id = %s""", (user_id, ))
def get(self, space_id):
""" Fetch data for space with the corresponding space_id """
return execute_query(
"SELECT * FROM spaces WHERE space_id = %s", (space_id, ))
def get(self, email):
""" Fetch data for admin with the corresponding email """
return database_utilities.execute_query(
f"""select * from admins where email = %s""", (email, ))
def delete(self, event_id):
"""Delete an event"""
query = "DELETE FROM class WHERE CONCAT(crn, semester_id) = %s"
parameters = (event_id, )
execute_query(query, parameters)
return jsonify(msg="Delete successful.")
def delete(self, space_id):
""" Deletes space with the corresponding space_id """
execute_query(
"DELETE FROM spaces WHERE space_id = %s", (space_id, ))
return jsonify(msg="Delete successful.")
def get(self):
""" Fetch data for all users """
return database_utilities.execute_query("select * from users")
def post(self):
""" Insert data for new users """
query = f"""insert into users values (%s);"""
json_data = request.get_json()
parameters = (json_data['user_id'], )
database_utilities.execute_query(query, parameters)
def get(self):
"""Fetch data for all buidlings."""
return execute_query("SELECT * FROM building")