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")