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
