def __init__(self, dbname = ""): self.c = pg.connect(user = "******" , dbname = dbname , port = settings.DATABASE_PORT ) self.weatherData = WeatherData() self.pyrgeometerData = PyrgeometerData()
class TestWeatherData(unittest.TestCase): def setUp(self): self.pd = PyrgeometerData() def testGetHourMedianDownwardIrradiance(self): exp = [ 349.567321777, 369.547271729, 363.796203613, 367.676879883, 360.617858887, 366.510101318, 366.566253662, 369.266937256, 376.525360107, 373.901947021, 369.190643311, 371.558563232, 380.395202637, 380.33782959, 382.432250977, 387.385009766, 387.474578857, 391.059753418, 396.60168457, 392.65020752, 383.975860596, 371.716430664, 376.309783936, 352.016723633 ] start = datetime(2009, 5, 1, 0) for i in range(24): dt = start + timedelta(hours=i) d = self.pd.getHourMedianDownwardIrradiance(dt) self.assertAlmostEqual(exp[i], d)
class TestWeatherData(unittest.TestCase): def setUp(self): self.pd = PyrgeometerData() def testGetHourMedianDownwardIrradiance(self): exp = [ 349.567321777 , 369.547271729 , 363.796203613 , 367.676879883 , 360.617858887 , 366.510101318 , 366.566253662 , 369.266937256 , 376.525360107 , 373.901947021 , 369.190643311 , 371.558563232 , 380.395202637 , 380.33782959 , 382.432250977 , 387.385009766 , 387.474578857 , 391.059753418 , 396.60168457 , 392.65020752 , 383.975860596 , 371.716430664 , 376.309783936 , 352.016723633 ] start = datetime(2009, 5, 1, 0) for i in range(24): dt = start + timedelta(hours = i) d = self.pd.getHourMedianDownwardIrradiance(dt) self.assertAlmostEqual(exp[i], d)
class Weather2DBImport: """ This class contains logic to populate the weather database with GBT weather data. """ def __init__(self, dbname = ""): self.c = pg.connect(user = "******" , dbname = dbname , port = settings.DATABASE_PORT ) self.weatherData = WeatherData() self.pyrgeometerData = PyrgeometerData() def getNeededWeatherDates(self, dt = None): """ Get's those dates that don't have any accompanying weather data. """ if dt is None: dt = datetime.utcnow() r = \ self.c.query(""" SELECT id, date FROM weather_dates WHERE id NOT IN (SELECT weather_date_id FROM gbt_weather) AND date <= '%s' """ % dt) return [(row['id'], row['date']) for row in r.dictresult()] def getNeededWeatherDatesInRange(self, start, end): """ Get's those dates that don't have any accompanying weather data. """ r = self.c.query(""" SELECT id, date FROM weather_dates WHERE id NOT IN (SELECT weather_date_id FROM gbt_weather) AND date >= '%s' AND date < '%s' """ % (start, end)) return [(row['id'], row['date']) for row in r.dictresult()] def insert(self, weatherDateId, wind, irradiance): """ Inserts a row of data into the weather table. """ # handle missing data - put in what you can windOK = wind and wind == wind irradianceOK = irradiance and irradiance == irradiance if windOK and irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,wind_speed,irradiance) VALUES (%s, %s, %s) """ % (weatherDateId, wind, irradiance) elif windOK and not irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,wind_speed) VALUES (%s, %s) """ % (weatherDateId, wind) elif not windOK and irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,irradiance) VALUES (%s, %s) """ % (weatherDateId, irradiance) if windOK or irradianceOK: self.c.query(query) def update(self): """ Looks to see what weather times need updating, then retrieves that data from the sampler logs, and finally writes results to DB. """ results = [] # look for any missing data within the last year end = datetime.utcnow() start = end - timedelta(days = 365) dts = self.getNeededWeatherDatesInRange(start, end) for dtId, dtStr in dts: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") try: wind = self.weatherData.getHourDanaMedianSpeeds(dt) except: continue di = self.pyrgeometerData.getHourMedianDownwardIrradiance(dt) results.append((dtId, wind, di)) self.insert(dtId, wind, di) return results def findNullValues(self, column): "Who is missing a value?" query = """ SELECT gbt.id, wd.date FROM gbt_weather AS gbt, weather_dates AS wd WHERE gbt.%s is NULL AND gbt.weather_date_id = wd.id """ % column r = self.c.query(query) return [(row['id'], row['date']) for row in r.dictresult()] def updateRow(self, rowId, column, value): """ Updates a row in the weather table with a value. """ query = """ UPDATE gbt_weather SET %s = %s WHERE id = %d """ % (column, value, rowId) self.c.query(query) def backfill(self, column, callback, test = False): """ Generic method for looking for null values in the weather table, and updating those rows with the appropriate value from the sampler logs. """ results = [] missing = self.findNullValues(column) for id, dtStr in missing: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") v = callback(dt) # watch for NaN values if v and v == v: results.append((id, dtStr, v)) if not test: self.updateRow(id, column, v) return results def backfillWind(self, test = False): return self.backfill("wind_speed" , self.weatherData.getLastHourMedianWindSpeeds , test) def backfillIrradiance(self): return self.backfill("irradiance" , self.pyrgeometerData.getLastHourMedianDownwardIrradiance) def backfillReport(self, filename): "Backfills the DB, and creates report on results." # NOTE: current results for this method: using the weather or # or weather_unit_test DB's, since we only have 2006 & 2009 - present # data in those, and there is no 2006 pygeometer data, this method # only backfills in 2009 - present f = open(filename, 'w') # wind lines = [] lines.append("Wind Speed\n") lines.append("Start (ET): %s\n" % datetime.now()) results = self.backfillWind() for r in results: lines.append("%s,%s,%s\n" % (r[0], r[1], r[2])) lines.append("End (ET): %s\n" % datetime.now()) f.writelines(lines) # irradiance lines = [] lines.append("Irradiance\n") lines.append("Start (ET): %s\n" % datetime.now()) results = self.backfillIrradiance() for r in results: lines.append("%s,%s,%s\n" % (r[0], r[1], r[2])) lines.append("End (ET): %s\n" % datetime.now()) f.writelines(lines) f.close() print "printed report to: ", filename def backfillDatabase(self, starttime, endtime): """ Acquires the needed data whose dates that don't have any accompanying weather data, and inserts it into the database. """ dts = self.getNeededWeatherDatesInRange(starttime, endtime) for dtId, dtStr in dts: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") # Is there wind data? try: wind = self.weatherData.getHourDanaMedianSpeeds(dt) except: continue di = self.pyrgeometerData.getHourMedianDownwardIrradiance(dt) # Is irradiance a NaN? if di != di: di = None print dt, wind, di self.insert(dtId, wind, di)
def setUp(self): self.pd = PyrgeometerData()
def setUp(self): self.pd = PyrgeometerData()
def __init__(self, dbname=""): self.c = pg.connect(user="******", dbname=dbname, port=settings.DATABASE_PORT) self.weatherData = WeatherData() self.pyrgeometerData = PyrgeometerData()
class Weather2DBImport: """ This class contains logic to populate the weather database with GBT weather data. """ def __init__(self, dbname=""): self.c = pg.connect(user="******", dbname=dbname, port=settings.DATABASE_PORT) self.weatherData = WeatherData() self.pyrgeometerData = PyrgeometerData() def getNeededWeatherDates(self, dt=None): """ Get's those dates that don't have any accompanying weather data. """ if dt is None: dt = datetime.utcnow() r = \ self.c.query(""" SELECT id, date FROM weather_dates WHERE id NOT IN (SELECT weather_date_id FROM gbt_weather) AND date <= '%s' """ % dt) return [(row['id'], row['date']) for row in r.dictresult()] def getNeededWeatherDatesInRange(self, start, end): """ Get's those dates that don't have any accompanying weather data. """ r = self.c.query(""" SELECT id, date FROM weather_dates WHERE id NOT IN (SELECT weather_date_id FROM gbt_weather) AND date >= '%s' AND date < '%s' """ % (start, end)) return [(row['id'], row['date']) for row in r.dictresult()] def insert(self, weatherDateId, wind, irradiance): """ Inserts a row of data into the weather table. """ # handle missing data - put in what you can windOK = wind and wind == wind irradianceOK = irradiance and irradiance == irradiance if windOK and irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,wind_speed,irradiance) VALUES (%s, %s, %s) """ % (weatherDateId, wind, irradiance) elif windOK and not irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,wind_speed) VALUES (%s, %s) """ % (weatherDateId, wind) elif not windOK and irradianceOK: query = """ INSERT INTO gbt_weather (weather_date_id,irradiance) VALUES (%s, %s) """ % (weatherDateId, irradiance) if windOK or irradianceOK: self.c.query(query) def update(self): """ Looks to see what weather times need updating, then retrieves that data from the sampler logs, and finally writes results to DB. """ results = [] # look for any missing data within the last year end = datetime.utcnow() start = end - timedelta(days=365) dts = self.getNeededWeatherDatesInRange(start, end) for dtId, dtStr in dts: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") try: wind = self.weatherData.getHourDanaMedianSpeeds(dt) except: continue di = self.pyrgeometerData.getHourMedianDownwardIrradiance(dt) results.append((dtId, wind, di)) self.insert(dtId, wind, di) return results def findNullValues(self, column): "Who is missing a value?" query = """ SELECT gbt.id, wd.date FROM gbt_weather AS gbt, weather_dates AS wd WHERE gbt.%s is NULL AND gbt.weather_date_id = wd.id """ % column r = self.c.query(query) return [(row['id'], row['date']) for row in r.dictresult()] def updateRow(self, rowId, column, value): """ Updates a row in the weather table with a value. """ query = """ UPDATE gbt_weather SET %s = %s WHERE id = %d """ % (column, value, rowId) self.c.query(query) def backfill(self, column, callback, test=False): """ Generic method for looking for null values in the weather table, and updating those rows with the appropriate value from the sampler logs. """ results = [] missing = self.findNullValues(column) for id, dtStr in missing: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") v = callback(dt) # watch for NaN values if v and v == v: results.append((id, dtStr, v)) if not test: self.updateRow(id, column, v) return results def backfillWind(self, test=False): return self.backfill("wind_speed", self.weatherData.getLastHourMedianWindSpeeds, test) def backfillIrradiance(self): return self.backfill( "irradiance", self.pyrgeometerData.getLastHourMedianDownwardIrradiance) def backfillReport(self, filename): "Backfills the DB, and creates report on results." # NOTE: current results for this method: using the weather or # or weather_unit_test DB's, since we only have 2006 & 2009 - present # data in those, and there is no 2006 pygeometer data, this method # only backfills in 2009 - present f = open(filename, 'w') # wind lines = [] lines.append("Wind Speed\n") lines.append("Start (ET): %s\n" % datetime.now()) results = self.backfillWind() for r in results: lines.append("%s,%s,%s\n" % (r[0], r[1], r[2])) lines.append("End (ET): %s\n" % datetime.now()) f.writelines(lines) # irradiance lines = [] lines.append("Irradiance\n") lines.append("Start (ET): %s\n" % datetime.now()) results = self.backfillIrradiance() for r in results: lines.append("%s,%s,%s\n" % (r[0], r[1], r[2])) lines.append("End (ET): %s\n" % datetime.now()) f.writelines(lines) f.close() print "printed report to: ", filename def backfillDatabase(self, starttime, endtime): """ Acquires the needed data whose dates that don't have any accompanying weather data, and inserts it into the database. """ dts = self.getNeededWeatherDatesInRange(starttime, endtime) for dtId, dtStr in dts: dt = datetime.strptime(dtStr, "%Y-%m-%d %H:%M:%S") # Is there wind data? try: wind = self.weatherData.getHourDanaMedianSpeeds(dt) except: continue di = self.pyrgeometerData.getHourMedianDownwardIrradiance(dt) # Is irradiance a NaN? if di != di: di = None print dt, wind, di self.insert(dtId, wind, di)