def get_problem_owner(problem_id): """Gets all photos posted by user to problem.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `user_id` FROM `problem` WHERE `id`=%s;""" cursor.execute(query, (problem_id,)) return cursor.fetchone()
def get_problem_owner(problem_id): """Gets all photos posted by user to problem.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `user_id` FROM `problem` WHERE `id`=%s;""" cursor.execute(query, (problem_id, )) return cursor.fetchone()
def count_users(): """Users per page """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT COUNT(*) FROM `user`;""" cursor.execute(query) return cursor.fetchone()
def get_all_resources(): """Get all resources. :return: tuple of resources """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `resource_name` FROM `resource`;""" cursor.execute(query) return cursor.fetchall()
def delete_role_by_id(role_id): """Delete role name in db. :params: role_id - key for searching resource name in DB for deleting """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `role` WHERE `id`=%s;""" cursor.execute(query, (role_id, )) conn.commit()
def delete_user_avatar(user_id): """Deletes user profile photo from db. :params: user_id - unique id user """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `user` SET `avatar`='' WHERE `id`=%s;""" cursor.execute(query, (user_id,)) conn.commit()
def get_all_roles(): """Return all roles in db. :return: tuple, containing all roles """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `name` FROM `role`;""" cursor.execute(query) return cursor.fetchall()
def insert_role(role_name): """Insert new role into db. :params: role_name - name of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """INSERT INTO `role` (`name`) VALUES (%s);""" cursor.execute(query, (role_name,)) conn.commit()
def insert_role(role_name): """Insert new role into db. :params: role_name - name of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """INSERT INTO `role` (`name`) VALUES (%s);""" cursor.execute(query, (role_name, )) conn.commit()
def delete_user_avatar(user_id): """Deletes user profile photo from db. :params: user_id - unique id user """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `user` SET `avatar`='' WHERE `id`=%s;""" cursor.execute(query, (user_id, )) conn.commit()
def delete_permission_by_id(permission_id): """Delete permission row in db. :params: permission_id - key for searching resource name in DB for deleting """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `permission` WHERE `id`=%s;""" cursor.execute(query, (permission_id, )) conn.commit()
def delete_role_by_id(role_id): """Delete role name in db. :params: role_id - key for searching resource name in DB for deleting """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `role` WHERE `id`=%s;""" cursor.execute(query, (role_id,)) conn.commit()
def get_pages_titles(): """This method retrieves brief info from db about all pages(ex-resources). """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `title`, `alias`, `is_enabled` FROM `page`;""" cursor.execute(query) return cursor.fetchall()
def get_problem_photos(problem_id): """Gets all photos posted by user to problem.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `name`, `description`, `user_id` FROM `photo` WHERE `problem_id`=%s; """ cursor.execute(query, (problem_id,)) return cursor.fetchall()
def get_problem_photos(problem_id): """Gets all photos posted by user to problem.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `name`, `description`, `user_id` FROM `photo` WHERE `problem_id`=%s; """ cursor.execute(query, (problem_id, )) return cursor.fetchall()
def delete_permission_by_id(permission_id): """Delete permission row in db. :params: permission_id - key for searching resource name in DB for deleting """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `permission` WHERE `id`=%s;""" cursor.execute(query, (permission_id,)) conn.commit()
def delete_page_by_id(page_id): """This method deletes page by it's id from db. :params: id - id of the page, which needs to be deleted. """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `page` WHERE `id`=%s;""" cursor.execute(query, (page_id, )) conn.commit()
def delete_page_by_id(page_id): """This method deletes page by it's id from db. :params: id - id of the page, which needs to be deleted. """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE FROM `page` WHERE `id`=%s;""" cursor.execute(query, (page_id,)) conn.commit()
def change_user_password(user_id, new_password): """Change password to user account. :params: new_password - new password user_id - id of user """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `user` SET `password`=%s WHERE `id`=%s;""" cursor.execute(query, (new_password, user_id)) conn.commit()
def change_user_role(role_id, user_id): """Update users role. :params: user_id - id of user role_id - id of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `user_role` SET `role_id`=%s WHERE `user_id`=%s;""" cursor.execute(query, (role_id, user_id)) conn.commit()
def edit_role(new_role_name, role_id): """Edit role name. :params: role_name - new name of role role_id - if of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `role` SET `name`=%s WHERE `id`=%s;""" cursor.execute(query, (new_role_name, role_id)) conn.commit()
def get_resource_id(resource_name): """Gets resource id. :params: resource_name - name of resource :return: tuple, containing id """ with db_pool().manager() as conn: cursor = conn.cursor() sql = """SELECT `id` FROM `resource` WHERE `resource_name`=%s;""" cursor.execute(sql, (resource_name,)) return cursor.fetchone()
def get_resource_id(resource_name): """Gets resource id. :params: resource_name - name of resource :return: tuple, containing id """ with db_pool().manager() as conn: cursor = conn.cursor() sql = """SELECT `id` FROM `resource` WHERE `resource_name`=%s;""" cursor.execute(sql, (resource_name, )) return cursor.fetchone()
def insert_user_avatar(user_id, img_path): """Insert new user avatar into db. :params: user_id - unique id user img_path - path to avatar image """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `user` SET `avatar`=%s WHERE id=%s;""" cursor.execute(query, (img_path, user_id)) conn.commit()
def add_resource(resource_name): """Adds new resource in db. :params: resource_name - name of new resource """ with db_pool().manager() as conn: cursor = conn.cursor() query = """INSERT INTO `resource` (`resource_name`) VALUES (%s); """ cursor.execute(query, (resource_name, )) conn.commit()
def delete_permissions_by_role_id(role_id): """Deletes all permissions from role_permission table by role_id. :params: role_id - id of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE `role_permission` FROM `role_permission` WHERE `role_id`=%s; """ cursor.execute(query, (role_id, )) conn.commit()
def delete_permissions_by_role_id(role_id): """Deletes all permissions from role_permission table by role_id. :params: role_id - id of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """DELETE `role_permission` FROM `role_permission` WHERE `role_id`=%s; """ cursor.execute(query, (role_id,)) conn.commit()
def get_role_by_name(name='user'): """Gets role id by it's name. :params: name - name of role, default - 'user' :return: tuple with id of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id` FROM `role` WHERE `name`=%s;""" cursor.execute(query, (name, )) return cursor.fetchone()
def get_role_by_name(name='user'): """Gets role id by it's name. :params: name - name of role, default - 'user' :return: tuple with id of role """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id` FROM `role` WHERE `name`=%s;""" cursor.execute(query, (name,)) return cursor.fetchone()
def add_resource(resource_name): """Adds new resource in db. :params: resource_name - name of new resource """ with db_pool().manager() as conn: cursor = conn.cursor() query = """INSERT INTO `resource` (`resource_name`) VALUES (%s); """ cursor.execute(query, (resource_name,)) conn.commit()
def get_all_permissions_by_resource(resource_id): """Find all permissions by resource. :params: resource_id - id of resource :return: tuple, containing permissions """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `action`, `modifier`, `description` FROM `permission` WHERE `resource_id`=%s; """ cursor.execute(query, (resource_id, )) return cursor.fetchall()
def edit_resource_name(new_resource_name, resource_id): """Edit resource name. :params: new_resource_name - new name of resource resource_id - id of resource we change name """ with db_pool().manager() as conn: cursor = conn.cursor() query = """UPDATE `resource` SET `resource_name`=%s WHERE `id`=%s; """ cursor.execute(query, (new_resource_name, resource_id)) conn.commit()
def get_all_permissions_by_resource(resource_id): """Find all permissions by resource. :params: resource_id - id of resource :return: tuple, containing permissions """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `action`, `modifier`, `description` FROM `permission` WHERE `resource_id`=%s; """ cursor.execute(query, (resource_id,)) return cursor.fetchall()
def get_user_role_by_id(user_id): """Get all resources. :return: tuple of resources """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT r.name FROM `role` AS r INNER JOIN `user_role` AS ur ON r.id=ur.role_id WHERE ur.user_id=%s; """ cursor.execute(query, (user_id, )) return cursor.fetchone()
def get_user_by_oauth_id(user_id): """Return user, found by id. :params: user_id - id of user :return: tuple with user info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `first_name`, `last_name`, `email`, `password` FROM `user` WHERE `oauth_uid`=%s; """ cursor.execute(query, (user_id, )) return cursor.fetchone()
def get_user_by_oauth_id(user_id): """Return user, found by id. :params: user_id - id of user :return: tuple with user info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `first_name`, `last_name`, `email`, `password` FROM `user` WHERE `oauth_uid`=%s; """ cursor.execute(query, (user_id,)) return cursor.fetchone()
def get_user_role_by_id(user_id): """Get all resources. :return: tuple of resources """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT r.name FROM `role` AS r INNER JOIN `user_role` AS ur ON r.id=ur.role_id WHERE ur.user_id=%s; """ cursor.execute(query, (user_id,)) return cursor.fetchone()
def get_all_problems(): """Return all problems in db. :return: tuple, containing all problems """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `title`, `latitude`, `longitude`, `problem_type_id`, `status`, `created_date` FROM `problem`; """ cursor.execute(query) return cursor.fetchall()
def get_user_problems(user_id): """Gets all problems posted by given user.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `title`, `latitude`, `longitude`, `problem_type_id`, `status`, `created_date`, `is_enabled`, `severity` FROM `problem` WHERE `user_id`=%s """ cursor.execute(query, (user_id,)) return cursor.fetchall()
def add_users_role(user_id, role_id): """Adds to recenty registered user role "User". :params: user_id - id of recenty created user role_id - default is "User" """ with db_pool().manager() as conn: conn.autocommit(True) cursor = conn.cursor() query = """INSERT INTO `user_role` (`user_id`, `role_id`) VALUES (%s, %s); """ cursor.execute(query, (user_id, role_id))
def get_user_problems(user_id): """Gets all problems posted by given user.""" with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `title`, `latitude`, `longitude`, `problem_type_id`, `status`, `created_date`, `is_enabled`, `severity` FROM `problem` WHERE `user_id`=%s """ cursor.execute(query, (user_id, )) return cursor.fetchall()
def get_user_by_email(email): """Return user, found by email. :params: email - user email :retrun: tuple with user info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `first_name`, `last_name`, `email`, `password`, `avatar` FROM `user` WHERE `email`=%s; """ cursor.execute(query, (email,)) return cursor.fetchone()
def get_id_problem_owner(problem_id): """Return problem, found by id. :params: problem_id - id of problem which was selected :return: tuple with problem_activity info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `created_date`, `problem_id`, `user_id`, `activity_type` FROM `problem_activity` WHERE `problem_id`=%s; """ cursor.execute(query, (problem_id, )) return cursor.fetchone()
def get_all_users(): """Return all registered users from db. :return: tuples with user info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name FROM `user_role` as ur INNER JOIN `user` as u ON ur.user_id=u.id INNER JOIN `role` as r ON ur.role_id=r.id; """ cursor.execute(query) return cursor.fetchall()
def get_users_pagination(offset, per_page): """Users per page """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name FROM `user_role` AS ur INNER JOIN `user` AS u ON ur.user_id=u.id INNER JOIN `role` AS r ON ur.role_id=r.id ORDER BY `id` LIMIT %s,%s; """ cursor.execute(query % (offset, per_page)) return cursor.fetchall()
def get_user_by_email(email): """Return user, found by email. :params: email - user email :retrun: tuple with user info """ with db_pool().manager() as conn: cursor = conn.cursor() query = """SELECT `id`, `first_name`, `last_name`, `email`, `password`, `avatar` FROM `user` WHERE `email`=%s; """ cursor.execute(query, (email, )) return cursor.fetchone()