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)
