def all(): db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.FutureBorough;") cursor.execute(query) result = cursor.fetchall() return jsonify(result)
def all(): db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.CityWater;") cursor.execute(query) result = cursor.fetchall() db.connection.close() return jsonify(result)
def division(): db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT C1.City FROM City C1 WHERE NOT EXISTS ( " "SELECT B1.City FROM Borough B1 " "WHERE B1.Borough NOT IN ( " "SELECT B2.Borough FROM Borough B2 WHERE B2.City = C1.City) )") cursor.execute(query) result = cursor.fetchall() db.connection.close() return jsonify(result)
def index(): # Format is /borough?name=XXXX # All arguments are mandatory name = request.args['name'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.Borough WHERE borough = %s") cursor.execute(query, (name,)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def kingkong(): # Format is admin/deletecity?city=XXXX city = request.args['city'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("DELETE FROM jakk.City WHERE City = %s") cursor.execute(query, (city, )) print(cursor.statement) db.connection.commit() db.connection.close() return jsonify(["Number of rows affected", cursor.rowcount])
def updateBoroughSize(): # Format is admin/updateborough/size?borough=XXXX&new_value=XXXX borough = request.args['borough'] new_value = request.args['new_value'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("UPDATE jakk.Borough SET Size = %s WHERE Borough = %s") cursor.execute(query, (new_value, borough)) db.connection.commit() db.connection.close() return jsonify(["Number of rows affected", cursor.rowcount])
def updateBorough(): # Format is admin/updateborough?target=XXXX&result=XXXX target = request.args['target'] result = request.args['result'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("UPDATE jakk.Borough SET Borough = %s WHERE Borough = %s") cursor.execute(query, (result, target)) db.connection.commit() db.connection.close() return jsonify(["Number of rows affected", cursor.rowcount])
def insertCity(): # Format is admin/insertcity?city=XXXX city = request.args['city'] db = Database.fromconfig() cursor = db.connection.cursor() query = ( "INSERT INTO jakk.City (City, Country, Population, Size) VALUES (%s, 'Canada', 630000, 500)" ) cursor.execute(query, (city, )) db.connection.commit() db.connection.close() return jsonify(["Number of rows affected", cursor.rowcount])
def aggregationGroupBy(): # Format is admin/groupby?largerthan=XXXX # Returns boroughs and their sum(consumption) larger than XXXX largerThan = int(request.args['largerthan']) db = Database.fromconfig() cursor = db.connection.cursor() query = ( "SELECT borough, CAST(sum(consumption) AS float) FROM jakk.BoroughWater " "Group by borough having sum(consumption) > %s") # "Group by borough having sum(consumption) > 1000000") cursor.execute(query, (largerThan, )) # cursor.execute(query) result = cursor.fetchall() db.connection.close() return jsonify(result)
def boroughList(): # Format is /futureborough?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD # All arguments are mandatory name = request.args['name'] intervalStart = request.args['intervalStart'] intervalEnd = request.args['intervalEnd'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.FutureBorough WHERE Borough = %s AND StartDate BETWEEN %s AND %s") start = datetime.strptime(intervalStart, '%Y-%m-%d') end = datetime.strptime(intervalEnd, '%Y-%m-%d') cursor.execute(query, (name, start, end)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def boroughList(): # Provide arguments intervalStart & intervalEnd, or 400/500 will be returned # Format is /futurecity?intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD intervalStart = request.args['intervalStart'] intervalEnd = request.args['intervalEnd'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.FutureCity WHERE StartDate BETWEEN %s AND %s") start = datetime.strptime(intervalStart, '%Y-%m-%d') end = datetime.strptime(intervalEnd, '%Y-%m-%d') cursor.execute(query, (start, end)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def join(): # Format is /admin/join?borough1=XXXX&borough2=XXXX # Returns borough1 and borough2 alongside their consumptions based on the same month respectively borough1 = request.args['borough1'] borough2 = request.args['borough2'] db = Database.fromconfig() cursor = db.connection.cursor() query = ( "SELECT b1.startDate, b1.borough, b1.consumption, b2.borough, b2.consumption " "FROM BoroughWater b1 " "INNER JOIN BoroughWater b2 ON b1.startDate = b2.startDate " "WHERE b1.borough = %s AND b2.borough = %s") cursor.execute(query, (borough1, borough2)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def futureBoroughStats(): # Format is /futureborough?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD # All arguments are mandatory # Returns [avg(consumption), max(consumption), min(consumption), avg(charges), max(charges), min(charges)] name = request.args['name'] intervalStart = request.args['intervalStart'] intervalEnd = request.args['intervalEnd'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption)" "FROM jakk.FutureBorough WHERE Borough = %s AND StartDate BETWEEN %s AND %s") start = datetime.strptime(intervalStart, '%Y-%m-%d') end = datetime.strptime(intervalEnd, '%Y-%m-%d') cursor.execute(query, (name, start, end)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def login(): # Format is /auth/login?username=XXXX&password=YYYY # All arguments are mandatory authorized = False username = request.args['username'] password = request.args['password'] db = Database.fromconfig() cursor = db.connection.cursor() query = ("SELECT * FROM jakk.Account WHERE Username = %(Username)s") cursor.execute(query, {'Username': username}) account = cursor.fetchone() if (account): if password == account[2]: current_app.config['DB_USERNAME'] = username current_app.config['DB_PASSWORD'] = password current_app.config['DB_USERTYPE'] = 'admin' authorized = True db.connection.close() return jsonify(authorized)
def boroughListStats(): # Format is /userwater?name=XXXX&intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD # All arguments are mandatory name = request.args['name'] intervalStart = request.args['intervalStart'] intervalEnd = request.args['intervalEnd'] db = Database.fromconfig() cursor = db.connection.cursor() query = ( "SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption)," "CAST(AVG(Charges) AS float), MAX(Charges), MIN(Charges)" "FROM jakk.UserWater WHERE User = %s AND StartDate BETWEEN %s AND %s") start = datetime.strptime(intervalStart, '%Y-%m-%d') end = datetime.strptime(intervalEnd, '%Y-%m-%d') cursor.execute(query, (name, start, end)) result = cursor.fetchall() db.connection.close() return jsonify(result)
def cityStats(): # Provide arguments intervalStart & intervalEnd, or 400/500 will be returned # Format is /citywater?intervalStart=YYYY-MM-DD&intervalEnd=YYYY-MM-DD # Returns [avg(consumption), max(consumption), min(consumption), avg(charges), max(charges), min(charges)] intervalStart = request.args['intervalStart'] intervalEnd = request.args['intervalEnd'] db = Database.fromconfig() cursor = db.connection.cursor() query = ( "SELECT CAST(AVG(Consumption) AS float), MAX(Consumption), MIN(Consumption)," "CAST(AVG(Charges) AS float), MAX(Charges), MIN(Charges)" "FROM jakk.CityWater WHERE StartDate BETWEEN %s AND %s") start = datetime.strptime(intervalStart, '%Y-%m-%d') end = datetime.strptime(intervalEnd, '%Y-%m-%d') cursor.execute(query, (start, end)) result = cursor.fetchall() db.connection.close() return jsonify(result)