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)