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)