def api_get_cities_by_region(request): region_id = request.params[0] query = 'SELECT id, name FROM cities WHERE region_id=? AND deleted=0;' params = (region_id, ) cities = app.sql(query, params)[1] response = Response.as_json(dict(cities)) return response
def api_delete_comments_from_db(request): if request.method == 'POST': comment_id = request.POST.get('id') if not comment_id: return app.bad_request() # RETURN 400 try: comment_id = int(comment_id) except ValueError: return app.bad_request() # RETURN 400 # find user_id and delete user query = "SELECT user_id FROM comments WHERE id=?" params = (comment_id, ) user_id = app.sql(query, params)[1][0][0] # delete comment from db query = "DELETE FROM comments WHERE id=?" params = (comment_id, ) res_c = app.sql(query, params)[0] # delete user from db query = "DELETE FROM users WHERE id=?" params = (user_id, ) res_u = app.sql(query, params)[0] response = Response.as_json({'success': bool(res_c and res_u)}) return response
def api_delete_comments(request): if request.method == 'POST': comment_id = request.POST.get('id', None) if not comment_id: return app.bad_request() # RETURN 400 try: comment_id = int(comment_id) except ValueError: return app.bad_request() # RETURN 400 # find user_id and mark as deleted user query = "SELECT user_id FROM comments WHERE id=?" params = (comment_id, ) user_id = app.sql(query, params)[1][0][0] # mark comment as deleted query = "UPDATE comments SET deleted=1 WHERE id=?" params = (comment_id, ) res_c = app.sql(query, params)[0] # mark user as deleted query = "UPDATE users SET deleted=1 WHERE id=?" params = (user_id, ) res_u = app.sql(query, params)[0] response = Response.as_json({'success': bool(res_c and res_u)}) return response
def api_statistics(request): query = ("SELECT c.name, COUNT(u.id), r.name " "FROM (SELECT * FROM cities WHERE deleted=0) c " "LEFT JOIN (SELECT * FROM users WHERE deleted=0) u, " " (SELECT * FROM regions WHERE deleted=0) r " "ON u.city_id = c.id " "AND c.region_id = r.id " "GROUP BY c.id") data = app.sql(query)[1] result = {} for city_name, records_count, region_name in data: if region_name in result: result[region_name].update({city_name: records_count}) else: result[region_name] = {city_name: records_count} response = Response.as_json(result) return response
def api_get_comments(request): deleted = request.GET.get('deleted', 0) if deleted: try: deleted = int(deleted) except ValueError: return app.bad_request() # RETURN ERROR 400 query = ( "SELECT c.id, c.text, c.timestamp, u.first_name, u.last_name, city.name " "FROM (SELECT * FROM comments WHERE deleted=?) c " "LEFT JOIN users u, cities city " "ON c.user_id=u.id AND u.city_id=city.id;") params = (deleted, ) data = app.sql(query, params)[1] comments = {k: v for k, *v in data} response = Response.as_json(comments) return response
def api_get_regions(request): query = 'SELECT id, name FROM regions WHERE deleted=0;' regions = app.sql(query)[1] response = Response.as_json(dict(regions)) return response