def cityTotalElect():
x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from ELECTRICITYREPORT""")
if x:
return x
else:
return "N/A"
def cityTotalWater():
x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from WaterReport""")
if x:
return x
else:
return "N/A"
def cityAvgWater():
x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from WaterReport""")
if x:
return x
else:
return "N/A"
def cityAvgElect():
x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from ELECTRICITYREPORT""")
if x:
return x
else:
return "N/A"
def cityAvgNatGas():
x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avg from NaturalGasReport""")
if x:
return x
else:
return "N/A"
def cityTotalNatGas():
x = dbutil.runSQLAsDict("""SELECT SUM(Consumption) as tot from NaturalGasReport""")
if x:
return x
else:
return "N/A"
def cityTotalWater():
x = dbutil.runSQLAsDict(
"""SELECT SUM(Consumption) as tot from WaterReport""")
if x:
return x
else:
return "N/A"
def cityAvgWater():
x = dbutil.runSQLAsDict(
"""SELECT AVG(Consumption) as avg from WaterReport""")
if x:
return x
else:
return "N/A"
def totalTuples():
totAddress = dbutil.runSQLAsDict(
"""SELECT Count(ID) as cAd from address""")
totElect = dbutil.runSQLAsDict(
"""SELECT Count(ID) as cEl from Electricityreport""")
totNatGas = dbutil.runSQLAsDict(
"""SELECT Count(ID) as cNa from NaturalGasReport""")
totCodeVio = dbutil.runSQLAsDict(
"""SELECT Count(ID) as cCoVi from CodeViolationsReport""")
totWater = dbutil.runSQLAsDict(
"""SELECT Count(ID) as cWa from WaterReport""")
# total = totAddress[0].cAD + totElect[0].cEL + totNatGas[0].cNA + totWater[0].cWA
total2 = totAddress + totElect + totNatGas + totCodeVio + totWater
return total2
def findOneByID(id):
x = dbutil.runSQLAsDict("""SELECT * FROM ADDRESS WHERE
ADDRESS.ID = {}""".format(id))
if x:
return x
else:
return "N/A"
def getInfo(addrs):
x = dbutil.runSQLAsDict("""SELECT id, Streetaddress, coord_lat, coord_lon from ADDRESS
Where streetaddress = {}""".format( '\''+ addrs+'\''))
if x:
return x
else:
return "N/A"
def getId(addrs):
x = dbutil.runSQLAsDict("""SELECT ADDRESS.ID FROM ADDRESS WHERE
ADDRESS.StreetAddress = {}""".format( '\''+ addrs+'\''))
if x:
return x
else:
return "N/A"
def findOneByID(id):
x = dbutil.runSQLAsDict("""SELECT * FROM ADDRESS WHERE
ADDRESS.ID = {}""".format(id))
if x:
return x
else:
return "N/A"
def cityTotalElect():
x = dbutil.runSQLAsDict(
"""SELECT SUM(Consumption) as tot from ELECTRICITYREPORT""")
if x:
return x
else:
return "N/A"
def getId(addrs):
x = dbutil.runSQLAsDict("""SELECT ADDRESS.ID FROM ADDRESS WHERE
ADDRESS.StreetAddress = {}""".format('\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def cityAvgElect():
x = dbutil.runSQLAsDict(
"""SELECT AVG(Consumption) as avg from ELECTRICITYREPORT""")
if x:
return x
else:
return "N/A"
def cityAvgNatGas():
x = dbutil.runSQLAsDict(
"""SELECT AVG(Consumption) as avg from NaturalGasReport""")
if x:
return x
else:
return "N/A"
def cityTotalNatGas():
x = dbutil.runSQLAsDict(
"""SELECT SUM(Consumption) as tot from NaturalGasReport""")
if x:
return x
else:
return "N/A"
def getInfo(addrs):
x = dbutil.runSQLAsDict(
"""SELECT id, Streetaddress, coord_lat, coord_lon from ADDRESS
Where streetaddress = {}""".format('\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def findCodeVio(addrs):
x = dbutil.runSQLAsDict("""SELECT Violation, CaseType, Inspector from CodeViolationsreport, address
where address.id = CodeViolationsreport.ADDRESS_ID and
address.STREETADDRESS = {}""".format( '\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def findNatGas(addrs):
x = dbutil.runSQLAsDict("""SELECT Consumption from NaturalGasReport, Address Where
Address.ID = NaturalGasReport.ADDRESS_ID AND
Address.StreetAddress = {}""".format( '\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def avgNumSeven():
return dbutil.runSQLAsDict("""SELECT avg(electricityreport.consumption) as ElectAvg,
avg(waterreport.consumption) as WaterAvg,
avg(naturalgasreport.consumption) as NatGasAvg
from ELECTRICITYREPORT, waterreport, address, naturalgasreport
where ELECTRICITYREPORT.ADDRESS_ID = address.id AND
Waterreport.address_id = address.id and
NaturalGasReport.address_id = address.id and
address.streetaddress LIKE '7 %'""")
def findElectricity(addrs):
x = dbutil.runSQLAsDict("""SELECT Consumption from ELECTRICITYREPORT, Address Where
Address.ID = ELECTRICITYREPORT.ADDRESS_ID AND
Address.StreetAddress = {}""".format( '\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def findCodeVio(addrs):
x = dbutil.runSQLAsDict(
"""SELECT Violation, CaseType, Inspector from CodeViolationsreport, address
where address.id = CodeViolationsreport.ADDRESS_ID and
address.STREETADDRESS = {}""".format('\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def findElectricity(addrs):
x = dbutil.runSQLAsDict(
"""SELECT Consumption from ELECTRICITYREPORT, Address Where
Address.ID = ELECTRICITYREPORT.ADDRESS_ID AND
Address.StreetAddress = {}""".format('\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def findNatGas(addrs):
x = dbutil.runSQLAsDict(
"""SELECT Consumption from NaturalGasReport, Address Where
Address.ID = NaturalGasReport.ADDRESS_ID AND
Address.StreetAddress = {}""".format('\'' + addrs + '\''))
if x:
return x
else:
return "N/A"
def avgNumSix():
return dbutil.runSQLAsDict(
"""SELECT avg(electricityreport.consumption) as ElectAvg,
avg(waterreport.consumption) as WaterAvg,
avg(naturalgasreport.consumption) as NatGasAvg
from ELECTRICITYREPORT, waterreport, address, naturalgasreport
where ELECTRICITYREPORT.ADDRESS_ID = address.id AND
Waterreport.address_id = address.id and
NaturalGasReport.address_id = address.id and
address.streetaddress LIKE '6 %'""")
def streetCodeVio(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict("""SELECT Violation, CaseType, Inspector from CodeViolationsreport, Address Where
Address.ID = CodeViolationsreport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def streetWater(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avgSt from WaterReport, Address Where
Address.ID = WaterReport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def streetCodeVio(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict(
"""SELECT Violation, CaseType, Inspector from CodeViolationsreport, Address Where
Address.ID = CodeViolationsreport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def streetElectricity(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict("""SELECT AVG(Consumption) as avgSt from ELECTRICITYREPORT, Address Where
Address.ID = ElectricityReport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def streetWater(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict(
"""SELECT AVG(Consumption) as avgSt from WaterReport, Address Where
Address.ID = WaterReport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def streetElectricity(addrs):
newAddrs = addrs.split(' ', 1)
x = dbutil.runSQLAsDict(
"""SELECT AVG(Consumption) as avgSt from ELECTRICITYREPORT, Address Where
Address.ID = ElectricityReport.ADDRESS_ID and
Address.StreetAddress LIKE {}""".format('\'' + '% ' + newAddrs[1] + '\''))
if x:
return x
else:
return "N/A"
def maxCityWater():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, waterreport
where ( select Max(Consumption) from waterreport) = waterreport.consumption AND
address.id = waterreport.address_id""")
def maxCityWater():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, waterreport
where ( select Max(Consumption) from waterreport) = waterreport.consumption AND
address.id = waterreport.address_id""")
def potentialLandscapingCustomers():
return dbutil.runSQLAsDict("""SELECT Count(violation) as LawnsInNeed from codeviolationsreport
where violation = 'Overgrown Yard / Weeds'""")
def maxCityElect():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, electricityreport
where ( select Max(Consumption) from electricityreport) = electricityreport.consumption AND
address.id = electricityreport.address_id""")
def potentialLandscapingCustomers():
return dbutil.runSQLAsDict(
"""SELECT Count(violation) as LawnsInNeed from codeviolationsreport
where violation = 'Overgrown Yard / Weeds'""")
def fowlPlay():
return dbutil.runSQLAsDict(
"""SELECT count(id) as from CODEVIOLATIONSREPORT
where VIOLATION = 'Fowl or Livestock Prohibited'""")
def treeSqueezers():
return dbutil.runSQLAsDict(
"""SELECT count(id) as from CODEVIOLATIONSREPORT
where VIOLATION = 'Dead or Hazardous Trees'""")
def fowlPlay():
return dbutil.runSQLAsDict("""SELECT count(id) as from CODEVIOLATIONSREPORT
where VIOLATION = 'Fowl or Livestock Prohibited'""")
def maxCityNatGas():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, naturalgasreport
where ( select Max(Consumption) from naturalgasreport) = naturalgasreport.consumption AND
address.id = naturalgasreport.address_id""")
def treeSqueezers():
return dbutil.runSQLAsDict("""SELECT count(id) as from CODEVIOLATIONSREPORT
where VIOLATION = 'Dead or Hazardous Trees'""")
def maxCityNatGas():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, naturalgasreport
where ( select Max(Consumption) from naturalgasreport) = naturalgasreport.consumption AND
address.id = naturalgasreport.address_id""")
def maxCityElect():
return dbutil.runSQLAsDict("""SELECT streetaddress, consumption
from address, electricityreport
where ( select Max(Consumption) from electricityreport) = electricityreport.consumption AND
address.id = electricityreport.address_id""")
