def collectWeather():
global Weather
dbconn = mdb.connect(host=dbHost, user=dbUser, passwd=dbPassword, db=dbName)
c = dbconn.cursor()
c.execute("select reading from ftemperature where utime = " "(select max(utime) from ftemperature);")
Weather["currentOutsideTemp"] = c.fetchone()[0]
c.execute("select reading from rtemperature where utime = " "(select max(utime) from rtemperature);")
Weather["roofTemperature"] = c.fetchone()[0]
c.execute("select speed from wind where utime = " "(select max(utime) from wind);")
Weather["windSpeed"] = c.fetchone()[0]
c.execute("select directionc from wind where utime = " "(select max(utime) from wind);")
Weather["windDirectionC"] = c.fetchone()[0]
c.execute("select directiond from wind where utime = " "(select max(utime) from wind);")
Weather["windDirectionD"] = c.fetchone()[0]
c.execute("select reading from humidity where utime = " "(select max(utime) from wind);")
Weather["humidity"] = c.fetchone()[0]
c.execute("select reading from barometer where utime = " "(select max(utime) from barometer);")
Weather["currentBarometric"] = c.fetchone()[0]
# Get the cumulative readings
c.execute("select barometer from daily where utime = %s;", (midnight(1),))
Weather["midnightBarometric"] = c.fetchone()[0]
# now get the rest of the daily items
c.execute("select windhigh from daily where utime = %s;", (midnight(),))
Weather["maxWindSpeedToday"] = c.fetchone()[0]
c.execute("select hightemp from daily where utime = %s;", (midnight(),))
Weather["maxTempToday"] = c.fetchone()[0]
c.execute("select lowtemp from daily where utime = %s;", (midnight(),))
Weather["minTempToday"] = c.fetchone()[0]
c.execute("select raincount from daily where utime = %s;", (midnight(1),))
startCount = c.fetchone()[0]
c.execute("select reading from raincounter where utime = " "(select max(utime) from raincounter);")
endCount = c.fetchone()[0]
Weather["rainToday"] = str((float(endCount) - float(startCount)) * 0.01)
dbconn.close()
def getDailys():
# get maxs, mins and such out of the database to pick
# up where we left off in case of failure and restart.
# Midnight yesterday is considered the first instant of today.
m = midnight(1) # This time was recorded as the last reading yesterday
n = dbTimeStamp()
dbconn = mdb.connect(host=dbHost,
user=dbUser,
passwd=dbPassword,
db=dbName)
c = dbconn.cursor()
c.execute \
("select min(reading/1.0) from ftemperature where utime between %s and %s;",
(m, n))
Weather["minTempToday"] = c.fetchone()[0]
c.execute \
("select max(reading/1.0) from ftemperature where utime between %s and %s;",
(m, n))
Weather["maxTempToday"] = c.fetchone()[0]
c.execute \
("select max(speed/1.0) from wind where utime between %s and %s;",
(m, n))
Weather["maxWindSpeedToday"] = c.fetchone()[0]
c.execute \
("SELECT reading FROM barometer ORDER BY utime DESC LIMIT 1;")
Weather["latestBarometric"] = c.fetchone()[0]
dbconn.close()
def getDailys():
# get maxs, mins and such out of the database to pick
# up where we left off in case of failure and restart.
# Midnight yesterday is considered the first instant of today.
m = midnight(1) # This time was recorded as the last reading yesterday
n = dbTimeStamp()
dbconn = mdb.connect(host=dbHost, user=dbUser, passwd=dbPassword, db=dbName)
c = dbconn.cursor()
c.execute \
("select min(reading/1.0) from ftemperature where utime between %s and %s;",
(m, n))
Weather["minTempToday"] = c.fetchone()[0]
c.execute \
("select max(reading/1.0) from ftemperature where utime between %s and %s;",
(m, n))
Weather["maxTempToday"] = c.fetchone()[0]
c.execute \
("select max(speed/1.0) from wind where utime between %s and %s;",
(m, n))
Weather["maxWindSpeedToday"] = c.fetchone()[0]
c.execute \
("SELECT reading FROM barometer ORDER BY utime DESC LIMIT 1;")
Weather["latestBarometric"] = c.fetchone()[0]
dbconn.close()
def recordInDatabase():
#lprint("Recording weather data")
# First update the daily totals in the Weather dictionary
getDailys()
if ("default" in Weather.values()):
lprint("Weather data not ready yet")
lprint(Weather)
return
dbconn = mdb.connect(host=dbHost,
user=dbUser,
passwd=dbPassword,
db=dbName)
c = dbconn.cursor()
try:
c.execute(
"insert into rtemperature (reading, utime)"
"values(%s, %s);", (Weather["roofTemperature"], dbTimeStamp()))
c.execute("insert into humidity (reading, utime)"
"values(%s, %s);", (Weather["humidity"], dbTimeStamp()))
c.execute(
"insert into wind (speed, directionc, directiond, utime)"
"values(%s,%s,%s,%s);",
(Weather["windSpeed"], Weather["windDirectionC"],
Weather["windDirectionD"], dbTimeStamp()))
c.execute("insert into raincounter (reading, utime)"
"values(%s, %s);", (Weather["rainCounter"], dbTimeStamp()))
dbconn.commit() # just in case the code below fails
# now adjust the daily mins and maxs
# This record gets updated all day and a new one gets created
# whenever a reading comes in after midnight. This means that
# the cumulative number are held under midnight of the previous
# day. This makes it a little confusing when calculating things
# like rainfall because you get the record for yesterday to
# represent the last reading of yesterday. As in max(today) -
# yesterday will give you today's rainfall.
c.execute(
'''INSERT INTO daily
(hightemp, lowtemp, windhigh, barometer, raincount, utime)
VALUES
(%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
hightemp = values(hightemp),
lowtemp = values(lowtemp),
windhigh = values(windhigh),
barometer = values(barometer),
raincount = values(raincount); ''', (
Weather["maxTempToday"],
Weather["minTempToday"],
Weather["maxWindSpeedToday"],
Weather["latestBarometric"],
Weather["rainCounter"],
midnight(),
))
dbconn.commit()
except mdb.Error, e:
lprint("Database Error %d: %s" % (e.args[0], e.args[1]))
def recordInDatabase():
#lprint("Recording weather data")
# First update the daily totals in the Weather dictionary
getDailys()
if ("default" in Weather.values()):
lprint("Weather data not ready yet")
lprint(Weather)
return
dbconn = mdb.connect(host=dbHost, user=dbUser, passwd=dbPassword, db=dbName)
c = dbconn.cursor()
try:
c.execute("insert into rtemperature (reading, utime)"
"values(%s, %s);",
(Weather["roofTemperature"],dbTimeStamp()))
c.execute("insert into humidity (reading, utime)"
"values(%s, %s);",
(Weather["humidity"],dbTimeStamp()))
c.execute("insert into wind (speed, directionc, directiond, utime)"
"values(%s,%s,%s,%s);",
(Weather["windSpeed"],
Weather["windDirectionC"],
Weather["windDirectionD"],
dbTimeStamp()))
c.execute("insert into raincounter (reading, utime)"
"values(%s, %s);",
(Weather["rainCounter"],dbTimeStamp()))
dbconn.commit() # just in case the code below fails
# now adjust the daily mins and maxs
# This record gets updated all day and a new one gets created
# whenever a reading comes in after midnight. This means that
# the cumulative number are held under midnight of the previous
# day. This makes it a little confusing when calculating things
# like rainfall because you get the record for yesterday to
# represent the last reading of yesterday. As in max(today) -
# yesterday will give you today's rainfall.
c.execute('''INSERT INTO daily
(hightemp, lowtemp, windhigh, barometer, raincount, utime)
VALUES
(%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
hightemp = values(hightemp),
lowtemp = values(lowtemp),
windhigh = values(windhigh),
barometer = values(barometer),
raincount = values(raincount); ''',
(Weather["maxTempToday"],
Weather["minTempToday"],
Weather["maxWindSpeedToday"],
Weather["latestBarometric"],
Weather["rainCounter"],
midnight(),))
dbconn.commit()
except mdb.Error, e:
lprint ("Database Error %d: %s" % (e.args[0],e.args[1]))
