def get_meter_id(mydb_connection, areaID):
sql_query_get_meter_id = "Select MeterID from meter WHERE AreaID={0}".format(int(areaID))
meter_id = connect.read_from_database(sql_query_get_meter_id, mydb_connection)
print("In get_meter_id function")# testing
print(meter_id)# testing
print(meter_id.shape)# testing
meter_id_to_array_in = meter_id.values.reshape(-1, ).tolist()
return meter_id_to_array_in
def get_meter_data(p_meter_id_to_array, mydb_connection):
sql_query_get_meter_data = "SELECT usage_date, KWH FROM month_data WHERE MeterID IN {0}".format(tuple(p_meter_id_to_array))
meter_data = connect.read_from_database(sql_query_get_meter_data, mydb_connection)
meter_data_log_in = resample_data(meter_data)
print(meter_data_log_in)# testing
return meter_data_log_in
meter_data_in['ReadAt'] = pd.to_datetime(meter_data_in['ReadAt'])
meter_data_in.rename(columns={'ReadAt': 'usage_date'}, inplace=True)
meter_data_in.set_index('usage_date', inplace=True)
meter_data_in['KWH'] = pd.to_numeric(meter_data_in['KWH'])
meter_data_in.interpolate(method='linear', inplace=True)
meter_data_diff = meter_data_in.diff()
data_in_month = meter_data_diff.resample('M').sum()
data_in_month['MeterId'] = int(meter_id_in)
connect.insert_to_database(data_in_month, mydb_connection,
TABLE_NAME_DATA_MONTH)
if __name__ == "__main__":
mydb_sqlalchemy = connect.database_connection_with_sqlalchemy()
sql_query_get_meter_id = "SELECT MeterId FROM Meter"
MeterID = connect.read_from_database(sql_query_get_meter_id,
mydb_sqlalchemy)
meter_id_to_array = MeterID.values.reshape(-1, ).tolist()
for meter_id in meter_id_to_array:
try:
sql_query_get_meter_data = "SELECT MAX(KWH) AS KWH, DATE_FORMAT(ReadAt, \"%Y-%m-%d 00:00:00\") AS ReadAt " \
"FROM MeterData WHERE MeterId={0} AND YEAR(ReadAt)={1} AND MONTH(ReadAt)={2} " \
"GROUP BY ReadAt, YEAR(ReadAt), MONTH(ReadAt), DAY(ReadAt)".format(meter_id,
YEAR, MONTH)
meter_data = connect.read_from_database(sql_query_get_meter_data,
mydb_sqlalchemy)
get_meter_data(meter_id, meter_data, mydb_sqlalchemy)
except Exception as err:
logging.basicConfig(
filename="Error_at_resample_data_log.log",
filemode='a',
format='%(asctime)s - %(levelname)s - %(message)s')
def get_service_area_id(mydb_connection):
sql_query_get_area_id = "Select AreaID from servicearea"
area_id = connect.read_from_database(sql_query_get_area_id, mydb_connection)
area_id_to_array = area_id.values.reshape(-1, ).tolist()
return area_id_to_array
def get_meter_id(mydb_connection, areaID):
sql_query_get_meter_id = "Select MeterId from Meter WHERE AreaId={0}".format(int(areaID))
meter_id = connect.read_from_database(sql_query_get_meter_id, mydb_connection)
meter_id_to_array_in = meter_id.values.reshape(-1, ).tolist()
return meter_id_to_array_in