def migrateLegacyInverterTableToPony(configdbns, excerpt):
if excerpt:
sampleSize = 10
else:
sampleSize = 10000
with PlantmonitorDB(configdbns) as db:
curr = db._client.cursor()
# fetch column names
curr.execute('select * from sistema_inversor limit 1;')
colNames = [c[0] for c in curr.description]
insertSignature = [
'time', 'inverter_name', 'location', '1HR', '1HR0', '2HR2', '3HR3',
'4HR4', 'daily_energy_h', 'daily_energy_l', 'e_total_h',
'e_total_l', 'h_total_h', 'h_total_l', 'pac_r', 'pac_s', 'pac_t',
'powereactive_t', 'powerreactive_r', 'powerreactive_s',
'probe1value', 'probe2value', 'probe3value', 'probe4value',
'temp_inv'
]
assert (insertSignature == colNames)
curr = db._client.cursor("header")
curr.execute(
'select location from sistema_inversor group by location order by location asc;'
)
plantNames = [r[0] for r in curr.fetchall()]
curr.close()
for plantName in plantNames:
logger.info("Migrating inverters from plant {}".format(plantName))
with orm.db_session:
plant = Plant.get(name=plantName)
if not plant:
plant = Plant(name=plantName, codename=plantName)
logger.warning(
"Plant {} did not exist. We creted it.".format(plantName))
with PlantmonitorDB(configdbns) as db:
curr = db._client.cursor("header")
curr.execute(
"select inverter_name from sistema_inversor where location = '{}' group by inverter_name order by inverter_name asc;"
.format(plantName))
inverterNames = [r[0] for r in curr.fetchall()]
curr.close()
for inverterName in inverterNames:
with PlantmonitorDB(configdbns) as db:
migrateLegacyInverterToPony(db, inverterName, plantName,
sampleSize, excerpt)
logger.info("Inverters migrated")
def migrateLegacyMeterTableToPony(configdbns, excerpt=False):
# createPlants has to have been run
with orm.db_session:
if orm.count(p for p in Plant) == 0:
print(
"Database Doesn't have any plant to migrate to. Run createPlants()."
)
return
if excerpt:
sampleSize = 10
else:
sampleSize = 1000
with PlantmonitorDB(configdbns) as db:
curr = db._client.cursor()
curr.execute(
"select name from sistema_contador group by name order by name asc;"
)
deviceNames = [r[0] for r in curr.fetchall()]
for deviceName in deviceNames:
with PlantmonitorDB(configdbns) as db:
# TODO normalize facility names
facilities = [
f for f, m in db.getFacilityMeter() if m == deviceName
]
with orm.db_session:
plants = [
Plant.get(codename=f) for f in facilities
if Plant.get(codename=f)
]
if not plants:
print(
"Plant codename {} does not exist, assuming old record"
.format(facilities))
continue
#plant = Plant(name=plantName, codename=facilityName)
elif len(plants) > 1:
print("[WARNING] Meter {} has several plants: {}".format(
deviceName, [plant.name for plant in plants]))
plantName = plants[0].name
migrateLegacyMeterToPony(db, deviceName, plantName, sampleSize,
excerpt)
def migrateLegacySensorTableToPony(configdbns,
plantName,
tableName,
deviceType,
dataColumnName,
deviceName,
excerpt=False):
if excerpt:
sampleSize = 10
else:
sampleSize = 1000
with orm.db_session:
plant = Plant.get(name=plantName)
if not plant:
plant = Plant(name=plantName, codename=plantName)
if deviceType == 'SensorIrradiation':
device = SensorIrradiation(name=deviceName, plant=plant)
elif deviceType == 'SensorTemperature':
device = SensorTemperatureAmbient(name=deviceName, plant=plant)
else:
raise PlantmonitorDBError(
"Unknown device type {}".format(deviceType))
with PlantmonitorDB(configdbns) as db:
curr = db._client.cursor()
curr.execute(
"select distinct on (time) time, {} from {} limit {};".format(
dataColumnName, tableName, sampleSize))
while True:
records = curr.fetchmany(sampleSize)
if not records:
break
for r in records:
time, value = r
time = time.replace(tzinfo=dt.timezone.utc)
# TODO should we change to mw_m2 on the values to support decimals?
valueInt = round(value)
device.insertRegistry(valueInt, time=time)
if excerpt:
break
def migrateLegacySensorIrradiationTableToPony(configdbns,
plantName,
deviceName,
excerpt=False):
if excerpt:
sampleSize = 10
else:
sampleSize = 1000
with orm.db_session:
plant = Plant.get(name=plantName)
if not plant:
plant = Plant(name=plantName, codename=plantName)
sensor = SensorIrradiation(name=deviceName, plant=plant)
with PlantmonitorDB(configdbns) as db:
curr = db._client.cursor()
curr.execute(
"select distinct on (time) time, irradiation_w_m2, temperature_celsius from sensors limit {};"
.format(sampleSize))
while True:
records = curr.fetchmany(sampleSize)
if not records:
break
for r in records:
print(r)
time, irradiation_w_m2, temperature_celsius = r
time = time.replace(tzinfo=dt.timezone.utc)
# TODO should we change to mw_m2 on the values to support decimals?
irradiation_w_m2 = round(irradiation_w_m2)
temperature_dc = round(temperature_celsius * 10)
sensor.insertRegistry(irradiation_w_m2=irradiation_w_m2,
temperature_dc=temperature_dc,
time=time)
if excerpt:
break
def upload_meter_data(configdb, test_env=True):
if test_env:
target_wsdl = configdb['meteo_test_url']
lastDateFile = 'lastDateFile-test.yaml'
else:
target_wsdl = configdb['meteo_url']
lastDateFile = 'lastDateFile.yaml'
params = dict(
wsdl=target_wsdl,
username=configdb['meteo_user'],
password=configdb['meteo_password'],
lastDateFile=lastDateFile,
showResponses=False,
)
excludedFacilities = ['test_facility']
responses = {}
start = time.perf_counter()
with MeteologicaApi(**params) as api:
with PlantmonitorDB(configdb) as db:
facilities = db.getFacilities()
apifacilities = api.getAllFacilities()
logger.info('Uploading data from {} facilities in db'.format(
len(facilities)))
if not facilities:
logger.warning("No facilities in db {} at {}:{}".format(
configdb['psql_db'], configdb['psql_host'],
configdb['psql_port']))
return
for facility in facilities:
if facility in excludedFacilities:
logger.info(
"Facility {} excluded manually".format(facility))
continue
if facility not in apifacilities:
logger.warning(
"Facility {} in db is not known for the API, skipping."
.format(facility))
responses[facility] = "INVALID_FACILITY_ID: {}".format(
facility)
continue
lastUploadDT = api.lastDateUploaded(facility)
logger.debug("Facility {} last updated: {}".format(
facility, lastUploadDT))
meterData = {}
if not lastUploadDT:
meterData = db.getMeterData(facility)
else:
toDate = dt.datetime.now(dt.timezone.utc)
# TODO refactor this undo the hour shift due to api understanding start-hours instead of end-hours (see below @101)
fromDate = lastUploadDT + dt.timedelta(hours=1)
meterData = db.getMeterData(facility, fromDate, toDate)
if not meterData:
logger.warning(
"No meter readings for facility {} since {}".format(
facility, lastUploadDT))
# if not meterData[facility]:
if facility not in meterData:
logger.warning("Missing {} in db meter readings {}".format(
facility, meterData))
continue
#Seems to be correct according to documentation (meteologica said the contrary):
#Hour correction, meteologica expects start hour insted of end hour for readings
#ERP has end hour. Verified with validated readings. Assumes horari type.
#Uploaded are one hour behind validated
meterDataShifted = shiftOneHour(meterData)
# conversion from energy to power
# (Not necessary for hourly values)
logger.debug("Uploading {} data: {} ".format(
facility, meterDataShifted[facility]))
response = api.uploadProduction(facility,
meterDataShifted[facility])
responses[facility] = response
logger.info(
"Uploaded {} observations for facility {} : {}".format(
len(meterDataShifted[facility]), facility, response))
elapsed = time.perf_counter() - start
logger.info('Total elapsed time {:0.4}'.format(elapsed))
return responses
def createPlantmonitorDB(self):
configdb = self.createConfig()
return PlantmonitorDB(configdb)
def tearDown(self):
config = self.createConfig()
PlantmonitorDB(config).dropDatabase(config)
self.cleanLastDateFile()
def setUp(self):
config = self.createConfig()
PlantmonitorDB(config).demoDBsetup(config)
self.cleanLastDateFile()
def createPlantmonitorDB(self):
configdb = ns.load('conf/configdb_test.yaml')
return PlantmonitorDB(configdb)
def tearDown(self):
configdb = self.createConfig()
PlantmonitorDB(configdb).dropDatabase(configdb)
def setUp(self):
configdb = self.createConfig()
PlantmonitorDB(configdb).demoDBsetup(configdb)
def download_meter_data(configdb, test_env=True):
if test_env:
target_wsdl = configdb['meteo_test_url']
lastDateFile = 'lastDateFile-test.yaml'
else:
target_wsdl = configdb['meteo_url']
lastDateFile = 'lastDateFile.yaml'
params = dict(
wsdl=target_wsdl,
username=configdb['meteo_user'],
password=configdb['meteo_password'],
lastDateFile='lastDateFile.yaml',
lastDateDownloadFile='lastDateDownloadFile.yaml',
showResponses=False,
)
start = time.perf_counter()
downloadStatus = {}
with MeteologicaApi(**params) as api:
with PlantmonitorDB(configdb) as db:
facilities = api.getAllFacilities()
if not facilities:
logger.info("No facilities in api {}".format(target_wsdl))
return downloadStatus
for facility in facilities:
lastDownload = db.lastDateDownloaded(facility)
now = dt.datetime.now(dt.timezone.utc)
toDate = now
if not lastDownload:
fromDate = now - dt.timedelta(days=14)
elif now - lastDownload < dt.timedelta(hours=1):
logger.info("{} already up to date".format(facility))
downloadStatus[facility] = "UPTODATE"
continue
else:
fromDate = lastDownload
try:
meterDataForecast = api.getForecast(
facility, fromDate, toDate)
downloadStatus[facility] = "OK"
except MeteologicaApiError as e:
logger.warning("Silenced exception: {}".format(e))
downloadStatus[facility] = str(e)
meterDataForecast = None
if not meterDataForecast:
logger.info("No forecast data for {}".format(facility))
continue
# TODO api uses start-hour, we should change to end-hour before inserting
# conversion from energy to power
# (Not necessary for hourly values)
forecastDict = {facility: meterDataForecast}
forecastDate = now
db.addForecast(forecastDict, forecastDate)
logger.info(
"Saved {} forecast records from {} to db - {} ".format(
len(meterDataForecast), facility,
downloadStatus[facility]))
elapsed = time.perf_counter() - start
logger.info('Total elapsed time {:0.4}'.format(elapsed))
return downloadStatus