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 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]))