def get_user_profile() -> ApiResponse: """ Takes GET request with user_id as a parameter :return: """ user_id = request.args.get('user_id') try: int(user_id) except ValueError as e: return RateMyDormApiResponse(None, 400, f"User id was not a valid integer {e}").response connection = get_connection() cursor = connection.cursor(buffered=True, named_tuple=True) params = {'user_id': user_id} query = """SELECT username, first_name, last_name, email, profile_image, status, profile_bio, user_role FROM users WHERE user_id = %(user_id)s LIMIT 1""" cursor.execute(query, params) user = cursor.fetchone() logger.debug(user) payload = {} if user: reviews, images = get_user_history(user_id, cursor) user_dict = convert_single_row_to_dict(user) payload['user'] = user_dict payload['reviews'] = reviews payload['images'] = images logger.debug(payload) connection.close() response = RateMyDormApiResponse(payload, 200).response return response
def get_url(params, image_type, id): logger.debug( f'Retrieve image url, params: {params}, image_type: {image_type}, id: {id}' ) if image_type == 'dorm': query = """SELECT url FROM dorm_image where dorm_id = %s""" elif image_type == 'profile': query = """SELECT profile_image FROM users WHERE user_id = %s""" else: return RateMyDormMessageResponse( 400, f"invalid image type: {image_type}, options are ['profile', 'dorm']" ).response connection = get_connection() cursor = connection.cursor() cursor.execute(query, (id, )) results = [row[0] for row in cursor] payload = {'urls': results} response = RateMyDormApiResponse(payload, 200).response connection.close() logger.debug('retrieve finished') return response
def store_url(params, image_type, id): logger.debug( f'Store image url, params: {params}, image_type: {image_type}, id: {id}' ) url = params.get('url') if image_type == 'dorm': query_params = { 'user_id': id['user_id'], 'dorm_id': id['dorm_id'], 'url': url } query = """INSERT INTO dorm_image VALUES (%(dorm_id)s, %(user_id)s, %(url)s)""" elif image_type == 'profile': query_params = {'url': url, 'id': id} query = """UPDATE users SET profile_image = %(url)s WHERE user_id = %(id)s""" else: return RateMyDormMessageResponse( 400, f"invalid image type: {image_type}, options are ['profile', 'dorm']" ).response connection = get_connection() cursor = connection.cursor() cursor.execute(query, query_params) connection.commit() logger.debug('store finished') return RateMyDormMessageResponse(200, "Stored image url").response
def get_last_id(): connection = get_connection() cursor = connection.cursor() cursor.execute("""SELECT LAST_INSERT_ID()""") id = cursor.fetchone() return id[0]
def database_status(): try: connection = get_connection() return "Database is up", 200 except (OperationalError, InterfaceError, ConnectionRefusedError) as e: print(e) return f"Database is not connected\nError: {e}", 503
def load_cards(): logging.debug(request.json) data_response = {'success': False} connection = get_connection() if request.method == 'POST': latitude = request.json['latitude'] longitude = request.json['longitude'] error = None params = { 'latitude': latitude, 'longitude': longitude } print(params) cursor = connection.cursor(buffered=True) cursor.execute( #formula taken from gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql courtesy of users: Mapperz and sachleen 'SELECT dorm_id, latitude, longitude, room_num, floor, building, quad, address, ' '(3959 * acos(' 'cos(radians(%(latitude)s)) ' '* cos(radians(latitude)) ' '* cos(radians(longitude) - radians(%(longitude)s)) ' '+ sin(radians(%(latitude)s)) ' '* sin(radians(latitude))' ')' ') AS distance ' 'FROM Dorm ' 'HAVING distance < 1 ' 'ORDER BY distance', params ) dormRows = cursor.fetchall() if dormRows is None: error = 'No dorms match your query' if error is None: dorm_dict = [] for i in range(len(dormRows)): dorm_dict.append([ str(dormRows[i][0]), #dorm_id str(dormRows[i][1]), #latitude str(dormRows[i][2]), #longitude str(dormRows[i][3]), #room_num str(dormRows[i][4]), #floor str(dormRows[i][5]), #building str(dormRows[i][6]), #quad str(dormRows[i][7]), #address ]) print(dorm_dict) return {'data' : dorm_dict}, 200 data_response['message'] = error return data_response, 401
def get_feature_ids(featureList: List) -> List[int]: connection = get_connection() cursor = connection.cursor(buffered=True, named_tuple=True) ids = [] for val in featureList: query = """SELECT feature_id FROM features WHERE feature = %s LIMIT 1""" cursor.execute(query, (val, )) result = cursor.fetchone() if result: ids.append(result.feature_id) # connection.close() return ids
def login(): """ Logs in a user and gives them a session cookie Accepts POST request: { 'email': value, 'password': value, } :return: 200 status if successfully logged in 401 if login failed """ logger.debug(request.json) data_response = {'success': False} connection = get_connection() cursor = connection.cursor(named_tuple=True) if request.method == 'POST': email = request.json['email'] password = request.json['password'] params = {'password': password, 'email': email} error = None cursor.execute( 'SELECT user_id, password, username FROM users WHERE email = %(email)s', params) user = cursor.fetchone() if user is None: error = 'Incorrect email.' elif not check_password_hash(user.password, password): error = 'Incorrect password.' if error is None: session.clear() session['user_id'] = user.user_id session['username'] = user.username data_response['success'] = True redirect_response = RateMyDormMessageResponse( 200, data_response).response return redirect_response data_response['message'] = error return data_response, 401 connection.commit()
def add_review(): json_data = request.json params = { 'user_id': json_data.get('user_id'), 'dorm_id': json_data.get('dorm_id'), 'timestamp': datetime.datetime.now(), 'rating': json_data.get('rating'), 'review_text': json_data.get('review_text'), } if None in params.values(): return RateMyDormApiResponse( payload="", code=400, message='Not all fields are filled out').response connection = get_connection() cursor = connection.cursor() response = "" try: insert = """INSERT INTO review (user_id, dorm_id, timestamp, rating, review_text) VALUES (%(user_id)s, %(dorm_id)s, %(timestamp)s, %(rating)s, %(review_text)s) """ cursor.execute(insert, params) response = 't' except IntegrityError as e: response = str(e), 400 logger.error(f'Integrity error during database insertion {e}') connection.rollback() except Exception as e: response = str(e), 400 logger.error(f'Unexpected error during review INSERT: {e}') connection.rollback() else: response = 'success', 200 logger.debug(f'Insert success {params}') connection.commit() finally: connection.close() return { 'message': response[0], 'timestamp': params.get('timestamp') }, response[1]
def example_query(): """ Example endpoint utilizing the mysql server :return: """ # Get a cursor from the database connection, this must be imported. See imports at top of file connector = get_connection() cursor = connector.cursor() # Results are stored in the cursor object # https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html operation = 'SELECT * FROM example_table ORDER BY date DESC' # multi=True returns an iterator so we can loop over the results for result in cursor.execute(operation, multi=True): if result.with_rows: print("Rows produced by statement '{}':".format(result.statement)) print(result.fetchall()) else: print("Number of rows affected by statement '{}': {}".format( result.statement, result.rowcount)) # Example of the proper way to parameterize an insert Template 'INSERT INTO example_table (column1, # column2) VALUES (%(column1_dictionary_key)s, %(column2_dictionary_key)s) Note the values format is %(keyname)s # This way we can pass a dictionary as a paramter to the query values = { 'data_being_inserted': 'Some data being inserted', 'date_being_inserted': datetime.now() } insert = "INSERT INTO example_table (data, date) VALUES (%(data_being_inserted)s, %(date_being_inserted)s)" # Execute query and pass values dictionary cursor.execute(insert, values) # Must commit to confirm changes connector.commit() # It's important to always return something from these endpoints even if it's just an empty dictionary. # Don't return None return {}
def load_logged_in_user(): """ Runs before other endpoints in this module. Loads the user's information into a variable for the duration of the request :return: """ logger.debug(request.json) user_id = session.get('user_id') if user_id is None: g.user = None else: try: connection = get_connection() except InterfaceError as e: return e.msg cursor = connection.cursor(buffered=True) cursor.execute( 'SELECT user_id, username FROM users WHERE user_id = %(user_id)s', {'user_id': user_id}) g.user = cursor.fetchone()
def example_rollback(): connector = get_connection() cursor = connector.cursor() stmt = 'INSERT INTO example_table (data) VALUES (%(data)s)' params = {'data': 'This will not be inserted'} try: cursor.execute(stmt, params) except Error as e: logging.error(f"Shouldn't see this {e}") return e # Imagine we've already run this insert, and now we decide we shouldn't have performed the insert. Since we are # required to commit before changes are published in the database we can rollback the changes we've staged. connector.rollback() connector.commit( ) # This commit does nothing after we've already rolled back # Try to select the insert we rolled back stmt = "SELECT * FROM example_table WHERE data='This will not be inserted'" cursor.execute(stmt) logging.info('The following line should show result=None') logging.info(f'Result of cursor.fetchone(): {cursor.fetchone()}') # Be careful to verify the cursor has returned anything from the query before performing operations on the data try: expected_string = cursor.fetchone() substring = expected_string[:5] except TypeError as e: logging.error(e) logging.info( 'This causes an error because the query returned nothing.') logging.info('normally it would be fine to slice a string like this.') return {}
def create_dorm() -> ApiResponse: """Endpoint for dorm creation Example post request: { "latitude": 73.33333, "longitude": "77.444j4f4", "room_num": 120, "floor": 2, "building": "Building Name", "quad": "Quad Name", "address": "address information" } """ data = request.get_json() logger.debug(f'Inside Dorm POST endpoint, got {data}') connection = get_connection() cursor = connection.cursor() try: schema = AddDormRequestSchema() params = schema.load(data) logging.debug('Incoming JSON validated against schema') except MalformedRequestException as e: logger.error( f'CREATE DORM: Error converting incoming data to a dictionary \n\tGot {data}' ) return RateMyDormApiResponse(data, 400, 'invalid input').response except ValidationError as e: logger.error(f'Invalid request {e}') return RateMyDormApiResponse(data, 400, 'Invalid request').response query_params = { key: val for key, val in params.items() if key != 'features' } logger.debug(f'Converted params {params}') # Check for duplicate dorms insert = "INSERT INTO Dorm (latitude, longitude, room_num, floor, building, quad, address) VALUES " \ "(%(latitude)s, %(longitude)s, %(room_num)s, %(floor)s, %(building)s, %(quad)s, %(address)s )" duplicate_check = """SELECT * FROM Dorm WHERE room_num=%(room_num)s AND floor=%(floor)s AND building=%(building)s AND quad=%(quad)s AND address=%(address)s LIMIT 1""" cursor.execute(duplicate_check, query_params) res = cursor.fetchone() if res is not None: connection.rollback() connection.close() return RateMyDormMessageResponse(400, "Duplicate dorm").response # Insert into Dorm table try: cursor.execute(insert, query_params) except Error as e: logger.error(f'Error inserting into db: {e.msg}') response = RateMyDormApiResponse(data, 400, 'Database error').response connection.rollback() return response except KeyError as e: logger.error(f'Missing required field in query: {e}') response = RateMyDormMessageResponse( 400, f"Missing a key field in the query {e}").response connection.rollback() return response # Get dorm id we just created & feature_ids from the features cursor.execute("""SELECT LAST_INSERT_ID()""") dorm_id = cursor.fetchone()[0] logger.debug('Beginning feature iteration insertion') try: for feature in data['features']: value = data['features'][feature] insert_features = """INSERT INTO feature_lut(dorm_id, feature_id, feature_value) SELECT %(dorm_id)s, feature_id, %(feature_value)s FROM features WHERE features.feature = %(feature_key)s""" feature_param = { 'dorm_id': dorm_id, 'feature_value': value, 'feature_key': feature } cursor.execute(insert_features, feature_param) except Exception as e: logger.error(f'Error during feature insertion {e}') response = RateMyDormMessageResponse( 400, f'Error during feature insertion {e}') connection.rollback() return response.response connection.commit() return RateMyDormApiResponse(code=200, payload={ 'dorm_id': dorm_id }).response
def load_dorm() -> ApiResponse: logger.debug('Inside Dorm GET endpoint') logger.debug(request.json) data_response = {'success': False} connection = get_connection() if request.method == 'POST': dorm_id = request.json['dorm_id'] error = None params = {'dorm_id': dorm_id} logger.info(params) '''Things we need to load: -all info from: Dorms{lat/lng/room_num/floor/building/quad/address || DONE -Dorm image: dorm_image{url} || DONE -Features: format is a bit strange, going to have to do it like so -> join features with features_lut on feature_id or just reference both return the features name and value || DONE -Reviews: the reviews need to returned like so -> retrieve; review_id, user_id, timestamp,rating, text also need to return user's name from the user_id to display on the review from Users -Tags: Won't worry about this for now ''' #load dorm basic info cursor = connection.cursor(buffered=True) cursor.execute( 'SELECT latitude, longitude, room_num, floor, building, quad, address ' 'FROM Dorm ' 'WHERE Dorm.dorm_id = %(dorm_id)s', params) dorm_info = cursor.fetchone() if dorm_info is None: error = 'No dorms match your query' data_response['message'] = error return data_response, 401 dorm_info_returned = [] if error is None: dorm_info_returned = [ str(dorm_info[0]), # latitude str(dorm_info[1]), # longitude str(dorm_info[2]), # room_num str(dorm_info[3]), # floor str(dorm_info[4]), # building str(dorm_info[5]), # quad str(dorm_info[6]), # address ] cursor.execute( 'SELECT url ' 'FROM dorm_image ' 'WHERE dorm_id = %(dorm_id)s', params) dorm_image = cursor.fetchall() if dorm_image is None: error = 'No dorms match your query' dorm_images_returned = [] if error is None: for i in range(len(dorm_image)): dorm_images_returned.append(dorm_image[i]) cursor.execute( 'SELECT feature, feature_value ' 'FROM feature_lut ' 'LEFT JOIN features ' 'ON feature_lut.feature_id = features.feature_id ' 'WHERE feature_lut.dorm_id = %(dorm_id)s', params) features = cursor.fetchall() if features is None: error = 'No dorms match your query' features_returned = [] if error is None: for i in range(len(features)): features_returned.append(features[i]) ''' Features are returned in array of this layout: [0] = room_type [1] = bathroom [2] = ac [3] = gym [4] = laundry [5] = internet [6] = kitchen ''' cursor.execute( 'SELECT review_text, rating, username, timestamp, review_id, review.user_id ' 'FROM review ' 'LEFT JOIN users ' 'ON review.user_id = users.user_id ' 'WHERE review.dorm_id = %(dorm_id)s', params) reviews = cursor.fetchall() if reviews is None: error = 'No dorms match your query' reviews_returned = [] if error is None: for i in range(len(reviews)): reviews_returned.append(reviews[i]) ''' Reviews are returned in array of this layout: [i] [ [0] = review_text [1] = rating [2] = username [3] = timestamp [4] = user_id (this one unsure if we should keep for moderation purposes) ] ''' dorm_data = { 'dorm_info': dorm_info_returned, 'dorm_features': features_returned, 'dorm_images': dorm_images_returned, 'dorm_reviews': reviews_returned } response = RateMyDormApiResponse(dorm_data, 200).response return response
def register(): """ Register endpoint accepts JSON POST of the form. { 'username': value, 'password': value, 'email': value, 'first_name': value, 'last_name': value } :return: 200 status if registration was successful 400 status plus error message if registration failed """ logger.info('Register route') connection = get_connection() cursor = connection.cursor() if request.method == 'POST': # Get form data username = request.json['username'] password = request.json['password'] email = request.json['email'] first_name = request.json['first_name'] last_name = request.json['last_name'] error = None params = { 'username': username, 'password': password, 'email': email, 'first_name': first_name, 'last_name': last_name } # Check for various errors if not username: error = 'Username is required.' elif not password: error = 'Password is required.' elif cursor.execute( 'SELECT user_id FROM users WHERE username = %(username)s', params) is not None: error = f'User {username} is already registered.' elif cursor.execute( 'SELECT user_id FROM users WHERE email = %(email)s', params) is not None: error = f'Email {email} is already in use' elif None in params.values(): error = 'All form fields are required' if error is None: params['password'] = generate_password_hash(password) try: cursor.execute( 'INSERT INTO users (username, password, first_name, last_name, email) VALUES (%(username)s, ' '%(password)s, %(first_name)s, %(last_name)s, %(email)s)', params) connection.commit() data = {'message': 'User registered'} response = RateMyDormMessageResponse(200, None).response return response except IntegrityError as e: logger.error(f'User exists: {e}') if 'users_email_uindex' in e.msg: message = "Email already in use" else: message = "Username already exists" return RateMyDormMessageResponse(400, message).response logger.error(f'Error in input data {error}') return error, 400 return 400
def load_cards(): #logger.debug(request.json) data_response = {'success': False} connection = get_connection() if request.method == 'POST': latitude = request.json['latitude'] longitude = request.json['longitude'] radius = request.json['radius'] room_type = request.json['room_type'] bathroom = request.json['bathroom'] dining = request.json['dining'] internet = request.json['internet'] laundry = request.json['laundry'] fitness = request.json['fitness'] airConditioning = request.json['airConditioning'] error = None params = { 'latitude': latitude, 'longitude': longitude, 'radius': radius, 'room_type': room_type, 'bathroom': bathroom, 'dining': dining, 'internet': internet, 'laundry': laundry, 'fitness': fitness, 'airConditioning': airConditioning } logger.info(params) cursor = connection.cursor(buffered=True) cursor.execute( #formula taken from gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql courtesy of users: Mapperz and sachleen 'SELECT dorm_id, latitude, longitude, room_num, floor, building, quad, address, ' '(3959 * acos(' 'cos(radians(%(latitude)s)) ' '* cos(radians(latitude)) ' '* cos(radians(longitude) - radians(%(longitude)s)) ' '+ sin(radians(%(latitude)s)) ' '* sin(radians(latitude))' ')' ') AS distance ' 'FROM Dorm ' 'HAVING distance < %(radius)s' 'ORDER BY distance ' 'LIMIT 30', params) dormRows = cursor.fetchall() if dormRows is None: error = 'No dorms match your query' if error is None: dorm_dict = [] for i in range(len(dormRows)): dorm_dict.append([ str(dormRows[i][0]), #dorm_id str(dormRows[i][1]), #latitude str(dormRows[i][2]), #longitude str(dormRows[i][3]), #room_num str(dormRows[i][4]), #floor str(dormRows[i][5]), #building str(dormRows[i][6]), #quad str(dormRows[i][7]), #address ]) logger.debug(dorm_dict) for i in range(len(dorm_dict)): temp_param = {'dorm_id': dorm_dict[i][0]} #print(dorm_dict[i][0]) cursor.execute( 'SELECT feature, feature_value ' 'FROM feature_lut ' 'LEFT JOIN features ' 'ON feature_lut.feature_id = features.feature_id ' 'WHERE feature_lut.dorm_id = %(dorm_id)s', temp_param) features = cursor.fetchall() if features is None: error = 'No dorms match your query' if error is None: for j in range(len(features)): dorm_dict[i].append(features[j]) ''' Features are added into dorm_dict of this layout: [8] = room_type [9] = bathroom [10] = ac [11] = gym [12] = laundry [13] = internet [14] = kitchen ''' #print('starting length' , len(dorm_dict)) i = 0 while (i < len(dorm_dict)): #print(len(dorm_dict[i]), ':ID:', dorm_dict[i][0], ':Iteration:' ,i) if len(dorm_dict[i]) > 14: if (room_type != 'Any'): if dorm_dict[i][8][1] != room_type: #print('pop room:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (bathroom != 'Any'): if dorm_dict[i][9][1] != bathroom: #print('pop br:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (airConditioning != 'Any'): if dorm_dict[i][10][1] != airConditioning: #print('pop air:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (fitness != 'Any'): if dorm_dict[i][11][1] != fitness: #print('pop fit:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (laundry != 'Any'): if dorm_dict[i][12][1] != laundry: #print('pop laund:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (internet != 'Any'): if dorm_dict[i][13][1] != internet: #print('pop internet:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue if (dining != 'Any'): if dorm_dict[i][14][1] != dining: #print('pop din:', dorm_dict[i][0], 'iter:', i) dorm_dict.pop(i) continue else: #print('pop malformat:', dorm_dict[i][0] , 'iter:', i) dorm_dict.pop(i) continue i += 1 for j in range(len(dorm_dict)): temp_param = {'dorm_id': dorm_dict[j][0]} cursor.execute( 'SELECT rating ' 'FROM review ' 'LEFT JOIN users ' 'ON review.user_id = users.user_id ' 'WHERE review.dorm_id = %(dorm_id)s', temp_param) reviews = cursor.fetchall() if reviews is None: error = 'No dorms match your query' if error is None: review_arr = [] for k in range(len(reviews)): review_arr.append(reviews[k][0]) dorm_dict[j].append(review_arr) j = 0 for j in range(len(dorm_dict)): temp_param = {'dorm_id': dorm_dict[j][0]} cursor.execute( 'SELECT url ' 'FROM dorm_image ' 'WHERE dorm_id = %(dorm_id)s ' 'LIMIT 1', temp_param) dorm_image = cursor.fetchall() dorm_dict[j].append(dorm_image) return {'data': dorm_dict}, 200 data_response['message'] = error return data_response, 401