def get_group_id_by_name(name, db_config): select_query = "select id from %s where name = '%s'" % (db_config.group_table, name) group_id = db_utils.return_select_results_as_list(select_query, db_config) if len(group_id) > 0: return group_id[0][0] else: return None
def get_group_object_by_name(name, db_config): select_query = "select json_build_object('name', name) from %s" % db_config.group_table select_query += " where name = '%s'" % name group_json = db_utils.return_select_results_as_list(select_query, db_config) if len(group_json) > 0: group_json = group_json[0][0] return create_group_object(group_json) else: return None
def get_user_object_by_userid(userid, db_config): select_query = "select json_build_object('first_name', first_name, " \ "'last_name', last_name, 'userid', userid)" \ " from %s" % db_config.user_table select_query += " where userid = '%s'" % userid user_json = db_utils.return_select_results_as_list(select_query, db_config) if len(user_json) > 0: user_json = user_json[0][0] return create_user_object(user_json) else: return None
def GET(self, userid): user = get_user_object_by_userid(userid, self.db_config) if user is not None: user_id = get_user_id_by_userid(user['userid'], self.db_config) select_query = "select name from %s" % self.db_config.group_table select_query += " g inner join %s ug on g.id = ug.group_id where ug.user_id = %s " \ % (self.db_config.user_group_table, user_id) groups = db_utils.return_select_results_as_list(select_query, self.db_config) groups = [i[0] for i in groups] # flatten cherrypy.response.body = dict() cherrypy.response.body['userid'] = user['userid'] cherrypy.response.body['last_name'] = user['last_name'] cherrypy.response.body['first_name'] = user['first_name'] cherrypy.response.body['groups'] = groups return json.dumps(cherrypy.response.body) else: raise cherrypy.HTTPError(404, 'No records found for userid: %s' % userid)
def GET(self, name): #get group_id select_query = "select id from %s" % self.db_config.group_table select_query += " where name = '%s'" % name group_id = get_group_id_by_name(name, self.db_config) if group_id is None: raise cherrypy.HTTPError(404, 'No records found for name: %s' % name) # get userids for group select_query = "select userid from %s" % self.db_config.user_table select_query += " u inner join %s ug on u.id = ug.user_id where ug.group_id = %s" \ % (self.db_config.user_group_table, group_id) userids = db_utils.return_select_results_as_list(select_query, self.db_config) userids = [i[0] for i in userids] # flatten cherrypy.response.body = dict() cherrypy.response.body['userids'] = userids return json.dumps(cherrypy.response.body)
def get_user_id_by_userid(userid, db_config): select_query = "select id from %s where userid = '%s'" % (db_config.user_table, userid) user_id = db_utils.return_select_results_as_list(select_query, db_config)[0][0] return user_id