def generate_user_name():
"""
Generates user names for each user with a NULL username field.
"""
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute("SELECT * FROM users WHERE username IS NULL")
users_obj = convert_objects(cursor.fetchall(), cursor.description)
cursor.close()
counter = random.randint(1, 101)
for user in users_obj:
# Set username. It will be
# [first letter of firstname][lastname without spaces/special charcters][a number to differentiate]
user_name = ""
if 'first_name' in user and user['first_name'] is not None:
user_name += user["first_name"][:1]
if 'last_name' in user and user['last_name'] is not None:
# https://stackoverflow.com/questions/5843518/remove-all-special-characters-punctuation-and-spaces-from-string
user_name += ''.join(e for e in user["last_name"] if e.isalnum())
user_name += str(counter)
counter += 1
put_cursor = connection.cursor()
put_cursor.execute("UPDATE users SET username=%s WHERE id=%s",
(user_name, user['id']))
connection.commit()
return make_response("OK", HTTPStatus.OK)
def execute_insert(sql_q_format, args):
"""
Executes an insert statement.
:param sql_q_format: A complete SQL query with zero or more %s
:param args: List of parameters to be substituted into the SQL query
"""
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute(sql_q_format, args)
connection.commit()
def _remove_user_from_event(user_id, event_id):
"""
Removes a user-event pair from the event_registration table.
:param user_id: id of user.
:param event_id: id of event
"""
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute(
"DELETE FROM event_registration WHERE id_event=%s AND id_guest=%s",
(event_id, user_id))
connection.commit()
def _add_user_to_event(user_id, event_id, role):
"""
Registers user to an event.
:param user_id: id of user
:param event_id: id of event
:param role: either "host" or "guest"
"""
connection = mysql.get_db()
event_registration_cursor = connection.cursor()
event_registration_cursor.execute(
"INSERT INTO event_registration VALUES (%s,%s,CURRENT_TIMESTAMP, %s)",
(user_id, event_id, role))
connection.commit()
def execute_single_tuple_query(sql_q_format, args):
"""
Returns a single tuple of results from a SQL query
format string and correpsonding arguments.
:param sql_q_format: A complete SQL query with zero or more %s
:param args: List of parameters to be substituted into the SQL query
:return: A response object ready to return to the client
"""
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute(sql_q_format, args)
response = make_response_from_single_tuple(cursor)
cursor.close()
return response
def execute_mod(sql_q_format, args):
"""
Executes a SQL statement that modifies the database without getting data.
:param sql_q_format: A complete SQL query with zero or more %s
:param args: List of parameters to be substituted into the SQL query
"""
connection = mysql.get_db()
cursor = connection.cursor()
try:
cursor.execute(sql_q_format, args)
except Exception as e:
connection.commit()
raise e
connection.commit()
def get_user_by_email(email):
"""
Checks database and returns object representing user with that username.
:param email: email of CultureMesh account (string)
:return: user_obj from db or None if no corresponding found.
"""
connection = mysql.get_db()
cursor = connection.cursor()
query = "SELECT * FROM users WHERE email=%s"
cursor.execute(query, (email, ))
user_db_tuple = cursor.fetchone()
if user_db_tuple is None:
return None
user = convert_objects([user_db_tuple], cursor.description)[0]
cursor.close()
return user
def execute_get_all(sql_q_format, args):
"""
Get all available items from the database that match a query.
:param sql_q_format: SQL command to execute, with ``%s`` to fill ``args``
:param args: Arguments used to replace ``%s`` in ``sql_q_format``
:return: Tuple of the form ``(items, description)`` where ``items`` is a
tuple of objects retrieved from the database and ``description`` is the
cursor description that names the attributes in the objects.
"""
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(sql_q_format, args)
items = cursor.fetchall()
descr = cursor.description
cursor.close()
return items, descr
def execute_get_one(sql_q_format, args):
"""
Get one item from the database.
:param sql_q_format: SQL command to execute, with ``%s`` to fill ``args``
:param args: Arguments used to replace ``%s`` in ``sql_q_format``
:return: Tuple of the form ``(item, description)`` where ``item`` is the
object retrieved from the database and ``description`` is the cursor
description that names the attributes in the object.
"""
connection = mysql.get_db()
cursor = connection.cursor()
cursor.execute(sql_q_format, args)
result = cursor.fetchone()
description = cursor.description
cursor.close()
return result, description
def network_exists(network_id):
"""
This function is used to validate endpoint input.
This function checks if the passed network id is a valid
network id (there is a corresponding network with that id.)
:param network_id:
:return: true if valid, false if no network found.
"""
connection = mysql.get_db()
network_check = connection.cursor()
network_check.execute("SELECT * \
FROM networks \
WHERE id=%s",
(network_id,))
possible_network = network_check.fetchone()
network_check.close()
return possible_network is not None
def user_exists(user_id):
"""
This function is used to validate endpoint input.
This function checks if the passed user id is a valid user id
(there is a corresponding user with that id.)
:param user_id:
:return: true if valid, false if no user found.
"""
connection = mysql.get_db()
user_check = connection.cursor()
user_check.execute("SELECT * \
FROM users \
WHERE id=%s",
(user_id,))
possible_user = user_check.fetchone()
user_check.close()
return possible_user is not None
def event_exists(event_id):
"""
This function is used to validate endpoint input.
This function checks if the passed event id is a valid event id
(there is a corresponding event with that id.)
:param event_id: the event id.
:return: true if valid, false if no event found.
"""
connection = mysql.get_db()
event_registration_check_cursor = connection.cursor()
event_registration_check_cursor.execute("SELECT * \
FROM events \
WHERE id=%s",
(event_id,))
possible_event = event_registration_check_cursor.fetchone()
event_registration_check_cursor.close()
return possible_event is not None
def execute_get_many(sql_q_format, args, count):
"""
Get many items from the database.
:param sql_q_format: SQL command to execute, with ``%s`` to fill ``args``
:param args: Arguments used to replace ``%s`` in ``sql_q_format``
:param count: The maximum number of items to return
:return: Tuple of the form ``(items, description)`` where ``items`` is a
tuple of objects retrieved from the database and ``description`` is the
cursor description that names the attributes in the objects.
"""
conn = mysql.get_db()
cursor = conn.cursor()
cursor.execute(sql_q_format, args)
items = cursor.fetchmany(count)
descr = cursor.description
cursor.close()
return items, descr
def get_user_by_id(id):
"""
Checks database and returns object representing user with that id.
:param id: id of CultureMesh account (string)
:return: user_obj from db or None if no corresponding found.
"""
connection = mysql.get_db()
cursor = connection.cursor()
# Note table_name is never supplied by a client, so we do not
# need to escape it.
query = "SELECT * FROM users WHERE id=%s"
cursor.execute(query, (id, ))
user_db_tuple = cursor.fetchone()
if user_db_tuple is None:
return None
user = convert_objects([user_db_tuple], cursor.description)[0]
cursor.close()
return user
def get_by_id(table_name, id_, cut_out_fields=[]):
"""
Given a table name and an id to search for, queries the table
and returns a response object ready to be returned to the client.
:param table_name: The name of the table to query
:param id_: The id of the object to fetch
:param cut_out_fields: a list of fields that should be removed for privacy reasons.
:returns: A response object ready to return to the client.
"""
connection = mysql.get_db()
cursor = connection.cursor()
# Note table_name is never supplied by a client, so we do not
# need to escape it.
query = "SELECT * FROM `%s` WHERE id=%%s" % (table_name,)
cursor.execute(query, (id_))
response = make_response_from_single_tuple(cursor, cut_out_fields)
cursor.close()
return response
def get_language_autocomplete():
input_text = request.args['input_text']
if input_text is None:
return make_response("Must have valid input_text field",
HTTPStatus.METHOD_NOT_ALLOWED)
connection = mysql.get_db()
cursor = connection.cursor()
# TODO: this is entirely unsafe, need better way to
# work with autocomplete.
query = "SELECT * FROM languages WHERE languages.name REGEXP %s ORDER BY num_speakers DESC LIMIT 20;"
cursor.execute(query, (input_text, ))
langs = cursor.fetchall()
if len(langs) == 0:
cursor.close()
return make_response(jsonify([]), HTTPStatus.OK)
langs = convert_objects(langs, cursor.description)
cursor.close()
return make_response(jsonify(langs), HTTPStatus.OK)
def get_paginated(sql_q_format, selection_fields, args,
order_clause, order_index_format, order_arg):
"""
Utility function for getting paginated results from a
database.
See OneNote documentation for Pagination mechanics.
NOTE: only works if the WHERE class of the SQL statement
matches a single id.
NOTE: the only thing here not provided by the user is args.
We call get_paginated_objects.
:param sql_q_format: A partial SQL query with zero or more %s
:param selection_fields: A list of the values to be substituted into sql_q_format
:param args: The query parameters (request.args)
:param order_clause: The SQL part that dictates order on the final results
:param order_index_format: The partial SQL query to be used for pagination
ordering, of the form "FIELD <= %s"
:param order_arg: The query param on which order is based for pagination
:returns: A response object ready to return to the client
"""
conn = mysql.get_db()
count = int(args.get("count", 100))
cursor = conn.cursor()
if order_arg in args:
order_arg_val = args[order_arg]
sql_q_format += " AND " + order_index_format
cursor.execute(sql_q_format + order_clause, (*selection_fields, order_arg_val))
else:
cursor.execute(sql_q_format + order_clause, (*selection_fields,))
items = cursor.fetchmany(count)
if len(items) == 0:
cursor.close()
return make_response(jsonify([]), HTTPStatus.OK)
items = convert_objects(items, cursor.description)
cursor.close()
return make_response(jsonify(items), HTTPStatus.OK)
