def getRecordsByThreshold(self, minTimestamp = None, maxTimestamp = None, orderAsc = True, asDict = False):
result = []
if(self.database.isOpen()):
cursor = None
order = "ASC"
if not orderAsc:
order = "DESC"
if(minTimestamp == None and maxTimestamp == None):
cursor = self.database.execute("SELECT * FROM mixerData ORDER BY timestamp " + order)
if(maxTimestamp == None):
cursor = self.database.execute("SELECT * FROM mixerData WHERE timestamp=? ORDER BY timestamp " + order, (minTimestamp, ))
else:
cursor = self.database.execute("SELECT * FROM mixerData WHERE ?<=timestamp AND timestamp<? ORDER BY timestamp " + order,
(minTimestamp, maxTimestamp, ))
for row in cursor:
mixerData = RMMixerData(row[2])
mixerData.temperature = row[3]
mixerData.rh = row[4]
mixerData.wind = row[5]
mixerData.solarRad = row[6]
mixerData.skyCover = row[7]
mixerData.rain = row[8]
mixerData.et0 = row[9]
mixerData.pop = row[10]
mixerData.qpf = row[11]
mixerData.condition = row[12]
mixerData.pressure = row[13]
mixerData.dewPoint = row[14]
mixerData.minTemp = row[15]
mixerData.maxTemp = row[16]
mixerData.minRH = row[17]
mixerData.maxRH = row[18]
mixerData.et0calc = row[19]
mixerData.et0final = row[20]
result.append(mixerData)
return result
def getLastRecordForDayForSimulator(self, dayTimestamp):
result = OrderedDict()
if(self.database.isOpen()):
minTimestamp = dayTimestamp
maxTimestamp = dayTimestamp + 86400
row = self.database.execute("SELECT * FROM mixerData WHERE ?<=timestamp AND timestamp<? GROUP BY timestamp ORDER BY forecastID DESC LIMIT 1",
(minTimestamp, maxTimestamp, )).fetchone()
if row:
mixerData = RMMixerData(row[2])
mixerData.temperature = row[3]
mixerData.rh = row[4]
mixerData.wind = row[5]
mixerData.solarRad = row[6]
mixerData.skyCover = row[7]
mixerData.rain = row[8]
mixerData.et0 = row[9]
mixerData.pop = row[10]
mixerData.qpf = row[11]
mixerData.condition = row[12]
mixerData.pressure = row[13]
mixerData.dewPoint = row[14]
mixerData.minTemp = row[15]
mixerData.maxTemp = row[16]
mixerData.minRH = row[17]
mixerData.maxRH = row[18]
mixerData.et0calc = row[19]
mixerData.et0final = row[20]
mixerDataDict = {
row[2]: mixerData
}
result = {
"forecastID": row[0],
"forecastTimestamp": row[1],
"data": mixerDataDict
}
return result
def getRecordsByForecast(self, useInsertOrder = False):
result = OrderedDict()
if(self.database.isOpen()):
cursor = None
if useInsertOrder:
cursor = self.database.execute("SELECT * FROM mixerData ORDER BY forecastID ASC, timestamp ASC")
else:
cursor = self.database.execute("SELECT * FROM mixerData ORDER BY forecastID DESC, timestamp ASC")
for row in cursor:
forecastID = row[0]
forecastTimestamp = row[1]
mixerData = RMMixerData(row[2])
mixerData.temperature = row[3]
mixerData.rh = row[4]
mixerData.wind = row[5]
mixerData.solarRad = row[6]
mixerData.skyCover = row[7]
mixerData.rain = row[8]
mixerData.et0 = row[9]
mixerData.pop = row[10]
mixerData.qpf = row[11]
mixerData.condition = row[12]
mixerData.pressure = row[13]
mixerData.dewPoint = row[14]
mixerData.minTemp = row[15]
mixerData.maxTemp = row[16]
mixerData.minRH = row[17]
mixerData.maxRH = row[18]
mixerData.et0calc = row[19]
mixerData.et0final = row[20]
if forecastID in result:
result[forecastID]["values"].append(mixerData)
else:
result[forecastID] = {"timestamp" : forecastTimestamp, "values": [mixerData, ]}
return result
def getRecordsForLastForecast(self):
forecast = None
values = None
if(self.database.isOpen()):
cursor = self.database.execute("SELECT * FROM mixerData WHERE forecastID=(SELECT MAX(forecastID) from mixerData) ORDER BY timestamp ASC")
for row in cursor:
if not forecast:
forecast = RMForecastInfo(row[0], row[1])
values = []
mixerData = RMMixerData(row[2])
mixerData.temperature = row[3]
mixerData.rh = row[4]
mixerData.wind = row[5]
mixerData.solarRad = row[6]
mixerData.skyCover = row[7]
mixerData.rain = row[8]
mixerData.et0 = row[9]
mixerData.pop = row[10]
mixerData.qpf = row[11]
mixerData.condition = row[12]
mixerData.pressure = row[13]
mixerData.dewPoint = row[14]
mixerData.minTemp = row[15]
mixerData.maxTemp = row[16]
mixerData.minRH = row[17]
mixerData.maxRH = row[18]
mixerData.et0calc = row[19]
mixerData.et0final = row[20]
values.append(mixerData)
return forecast, values
def getLastRecordsByThreshold(self, minTimestamp = None, maxTimestamp = None, orderAsc = True, asDict = False, noOfRecords = None):
if asDict:
result = OrderedDict()
else:
result = []
if(self.database.isOpen()):
cursor = None
order = "ASC"
if not orderAsc:
order = "DESC"
limit = ""
if noOfRecords:
limit = " LIMIT %d" % noOfRecords
if minTimestamp is None and maxTimestamp is None:
cursor = self.database.execute("SELECT MAX(forecastID), * FROM mixerData GROUP BY timestamp ORDER BY timestamp " + order + limit)
elif minTimestamp is None:
cursor = self.database.execute("SELECT MAX(forecastID), * FROM mixerData WHERE timestamp<=? GROUP BY timestamp ORDER BY timestamp " + order + limit, (maxTimestamp, ))
elif maxTimestamp is None:
cursor = self.database.execute("SELECT MAX(forecastID), * FROM mixerData WHERE ?<=timestamp GROUP BY timestamp ORDER BY timestamp " + order + limit, (minTimestamp, ))
else:
cursor = self.database.execute("SELECT MAX(forecastID), * FROM mixerData WHERE ?<=timestamp AND timestamp<=? GROUP BY timestamp ORDER BY timestamp " + order + limit,
(minTimestamp, maxTimestamp, ))
for row in cursor:
mixerData = RMMixerData(row[3])
mixerData.temperature = row[4]
mixerData.rh = row[5]
mixerData.wind = row[6]
mixerData.solarRad = row[7]
mixerData.skyCover = row[8]
mixerData.rain = row[9]
mixerData.et0 = row[10]
mixerData.pop = row[11]
mixerData.qpf = row[12]
mixerData.condition = row[13]
mixerData.pressure = row[14]
mixerData.dewPoint = row[15]
mixerData.minTemp = row[16]
mixerData.maxTemp = row[17]
mixerData.minRH = row[18]
mixerData.maxRH = row[19]
mixerData.et0calc = row[20]
mixerData.et0final = row[21]
if asDict:
result[row[3]] = mixerData
else:
result.append(mixerData)
return result
