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