|
|
Post by carlos on Jun 15, 2019 6:05:39 GMT -8
Trying to setup the our weather to store data in a mySQL table. I am finding very hard to follow where or which codes you need to modify to get the system to log data back into SQL. So far I have been able to identify that I need to modify OURWEATHERFunctions.py to point out to the correct IP address.
When I run
sudo python datalogger.py
I get the following error
pi@PiHomeLab:~/SDL_Pi_DataLogger $ sudo python DataLogger.py
SDL_Pi_Datalogger
Will work with the INA3221 SwitchDoc Labs Breakout Board
Will work with the ADS1115 SwitchDoc Labs Breakout Board
Will work with OurWeather - Complete Weather Kit
Will work with SwitchDoc Labs WxLink Wireless LInk
Program Started at:2019-06-15 08:24:56
readOURWEATHERData - The time is: 2019-06-15 08:24:56.936232
-----Can't read from OurWeather
Traceback (most recent call last):
File "DataLogger.py", line 158, in <module>
OURWEATHERFunctions.readOURWEATHERData(password)
File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 85, in readOURWEATHERData
preSplitData = data['FullDataString']
UnboundLocalError: local variable 'data' referenced before assignment
I am assuming that it is missing a password, but at this point I am not sure which password it is looking (and for which product).
I really wish there was a clear document that outlines the steps, not a list of all item in GitHub.
|
|
|
|
Post by carlos on Jun 15, 2019 7:17:10 GMT -8
After further research it look like the password that the system is looking is the one for the root account on mysql. The password needs to be modified in the file Datalogger.py
Once I modified that I got the system to read, but it still fails to write to MySql. the new error reads.
pi@PiHomeLab:~/SDL_Pi_DataLogger $ sudo python DataLogger.py
SDL_Pi_Datalogger
Will work with the INA3221 SwitchDoc Labs Breakout Board
Will work with the ADS1115 SwitchDoc Labs Breakout Board
Will work with OurWeather - Complete Weather Kit
Will work with SwitchDoc Labs WxLink Wireless LInk
Program Started at:2019-06-15 11:10:20
readOURWEATHERData - The time is: 2019-06-15 11:10:20.160151
[u'0.00', u'nan', u'43.16', u'110107.00', u'-45.04', u'0.52', u'1.25', u'0.00', u'0.98', u'0.30', u'1.94', u'1.13', u'4.86', u'0.00', u'315.00', u'1', u'2019-06-15 11:08:55', u'', u'0', u'-1', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'V:1', u'WXLMB ', u'0', u'', u'', u'0', u'', u'', u'0']
writing SQLdata
query=INSERT INTO OURWEATHERTable(timestamp, deviceid, Outdoor_Temperature , Outdoor_Humidity , Indoor_Temperature , Barometric_Pressure , Altitude , Current_Wind_Speed , Current_Wind_Gust , Current_Wind_Direction , Rain_Total , Wind_Speed_Minimum , Wind_Speed_Maximum , Wind_Gust_Minimum , Wind_Gust_Maximum , Wind_Direction_Minimum , Wind_Direction_Maximum , Display_English_Metrice , OurWeather_DateTime , OurWeather_Station_Name , Current_Air_Quality_Sensor , Current_Air_Quality_Qualitative, Battery_Voltage, Battery_Current, Solar_Voltage, Solar_Current, Load_Voltage, Load_Current ) VALUES(UTC_TIMESTAMP(), 1, 0.000, nan, 43.160, 110107.000, -45.040, 0.520, 1.250, 0.000, 0.980, 0.300, 1.940, 1.130, 4.860, 0.000, 315.000, 1, "2019-06-15 11:08:55" , "", 0, -1,0.000, 0.000, 0.000,0.000,0.000,0.000)
Traceback (most recent call last):
File "DataLogger.py", line 158, in <module>
OURWEATHERFunctions.readOURWEATHERData(password)
File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 118, in readOURWEATHERData
cur.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'DataLogger.OURWEATHERTable' doesn't exist")
|
|
|
|
Post by SDL on Jun 15, 2019 7:40:50 GMT -8
Make sure you have installed the MySQL tables It is saying that the MySQL table does not exist.
BP
|
|
|
|
Post by carlos on Jun 15, 2019 9:36:44 GMT -8
Yes SQL tables are in. Now the issue is that I cannot write to the tables because I get Nan for the outdoor humidity (probe was rma) I still have to research how to modify the insert statement so that if it reads nan it assigns a value of zero. That is unless you guys have already solved for that and wish to share the statement. Thanks  I had to redo the tables as I believed that the code is case sensitive and my tables were all in lowercase. I have fixed that. I still get the error but like I said before it should be because the insert statement doesn't know how to deal with nan values. sudo python DataLogger.py SDL_Pi_Datalogger Will work with the INA3221 SwitchDoc Labs Breakout Board Will work with the ADS1115 SwitchDoc Labs Breakout Board Will work with OurWeather - Complete Weather Kit Will work with SwitchDoc Labs WxLink Wireless LInk Program Started at:2019-06-15 13:37:46 readOURWEATHERData - The time is: 2019-06-15 13:37:46.728494 [u'0.00', u'nan', u'30.40', u'110095.00', u'-44.16', u'0.00', u'0.00', u'135.00', u'1.40', u'0.00', u'0.11', u'0.00', u'0.40', u'90.00', u'315.00', u'1', u'2019-06-15 13:36:29', u'', u'0', u'-1', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'V:1', u'WXLMB ', u'0', u'', u'', u'0', u'', u'', u'0'] writing SQLdata query=INSERT INTO OURWEATHERTable(timestamp, deviceid, Outdoor_Temperature , Outdoor_Humidity , Indoor_Temperature , Barometric_Pressure , Altitude , Current_Wind_Speed , Current_Wind_Gust , Current_Wind_Direction , Rain_Total , Wind_Speed_Minimum , Wind_Speed_Maximum , Wind_Gust_Minimum , Wind_Gust_Maximum , Wind_Direction_Minimum , Wind_Direction_Maximum , Display_English_Metrice , OurWeather_DateTime , OurWeather_Station_Name , Current_Air_Quality_Sensor , Current_Air_Quality_Qualitative, Battery_Voltage, Battery_Current, Solar_Voltage, Solar_Current, Load_Voltage, Load_Current ) VALUES(UTC_TIMESTAMP(), 1, 0.000, nan, 30.400, 110095.000, -44.160, 0.000, 0.000, 135.000, 1.400, 0.000, 0.110, 0.000, 0.400, 90.000, 315.000, 1, "2019-06-15 13:36:29" , "", 0, -1,0.000, 0.000, 0.000,0.000,0.000,0.000) Traceback (most recent call last): File "DataLogger.py", line 158, in <module> OURWEATHERFunctions.readOURWEATHERData(password) File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 118, in readOURWEATHERData cur.execute(query) File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorvalue _mysql_exceptions.ProgrammingError: (1146, "Table 'DataLogger.OURWEATHERTable' doesn't exist") |
|
|
|
Post by carlos on Jun 16, 2019 7:09:12 GMT -8
I made further adjustments to the datalogger.py script. It looks like it is not passing successfully the database, password, and user configuration. Maybe it is because of mysetup (even though followed the install guides suggested here). In any case, I am getting confirmation that I have db connectivity. Now all I need to wait is for a new AM2315 to arrive so that I get a different value than nan for humidity. Latest error
pi@PiHomeLab:~/SDL_Pi_DataLogger $ sudo python DataLogger.py
SDL_Pi_Datalogger
Will work with the INA3221 SwitchDoc Labs Breakout Board
Will work with the ADS1115 SwitchDoc Labs Breakout Board
Will work with OurWeather - Complete Weather Kit
Will work with SwitchDoc Labs WxLink Wireless LInk
Program Started at:2019-06-16 11:01:37
readOURWEATHERData - The time is: 2019-06-16 11:01:37.496093
[u'0.00', u'nan', u'25.50', u'110210.00', u'-52.93', u'0.26', u'0.64', u'225.00', u'19.28', u'0.00', u'0.48', u'0.00', u'1.36', u'180.00', u'225.00', u'1', u'2019-06-16 11:00:13', u'', u'0', u'-1', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'V:1', u'WXLMB ', u'0', u'', u'', u'0', u'', u'', u'0']
writing SQLdata
query=INSERT INTO OURWEATHERTable(timestamp, deviceid, Outdoor_Temperature , Outdoor_Humidity , Indoor_Temperature , Barometric_Pressure , Altitude , Current_Wind_Speed , Current_Wind_Gust , Current_Wind_Direction , Rain_Total , Wind_Speed_Minimum , Wind_Speed_Maximum , Wind_Gust_Minimum , Wind_Gust_Maximum , Wind_Direction_Minimum , Wind_Direction_Maximum , Display_English_Metrice , OurWeather_DateTime , OurWeather_Station_Name , Current_Air_Quality_Sensor , Current_Air_Quality_Qualitative, Battery_Voltage, Battery_Current, Solar_Voltage, Solar_Current, Load_Voltage, Load_Current ) VALUES(UTC_TIMESTAMP(), 1, 0.000, nan, 25.500, 110210.000, -52.930, 0.260, 0.640, 225.000, 19.280, 0.000, 0.480, 0.000, 1.360, 180.000, 225.000, 1, "2019-06-16 11:00:13" , "", 0, -1,0.000, 0.000, 0.000,0.000,0.000,0.000)
Traceback (most recent call last):
File "DataLogger.py", line 158, in <module>
OURWEATHERFunctions.readOURWEATHERData(password)
File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 124, in readOURWEATHERData
cur.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'nan' in 'field list'")
|
|
|
|
Post by carlos on Jun 17, 2019 17:13:24 GMT -8
I don't know what happened, I rebooted the Raspberry PI and now none of the python scripts work. I am back to square 1 trying to figure out how the system works. So Step1 is changing the password(looks like it is blank if you installed mysql using pimylifeup instructions) in Datalogger.py and add the ip addresss of the weather station on OURWEATHERFunctions.py.
After that change I am getting this error...
pi@PiHomeLab:~/SDL_Pi_DataLogger $ sudo python DataLogger.py
SDL_Pi_Datalogger
Will work with the INA3221 SwitchDoc Labs Breakout Board
Will work with the ADS1115 SwitchDoc Labs Breakout Board
Will work with OurWeather - Complete Weather Kit
Will work with SwitchDoc Labs WxLink Wireless LInk
Program Started at:2019-06-17 21:05:09
readOURWEATHERData - The time is: 2019-06-17 21:05:09.119292
-----Can't read from OurWeather
Traceback (most recent call last):
File "DataLogger.py", line 158, in <module>
OURWEATHERFunctions.readOURWEATHERData(password)
File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 85, in readOURWEATHERData
preSplitData = data['FullDataString']
UnboundLocalError: local variable 'data' referenced before assignment
Do really appreciate help.
|
|
|
|
Post by carlos on Jun 17, 2019 17:37:17 GMT -8
Ok so to solve the unbound error just add the following line
data=''
add it before line 85 on OURWEATHERFunctions.py This initialize the variable data giving it a value of empty. in my case a placed it above
# fetch the JSON data from the OurWeather device
Now my new error is going against MySQL. Basically it cannot connect against the database.
pi@PiHomeLab:~/SDL_Pi_DataLogger $ sudo python DataLogger.py
SDL_Pi_Datalogger
Will work with the INA3221 SwitchDoc Labs Breakout Board
Will work with the ADS1115 SwitchDoc Labs Breakout Board
Will work with OurWeather - Complete Weather Kit
Will work with SwitchDoc Labs WxLink Wireless LInk
Program Started at:2019-06-17 21:34:49
readOURWEATHERData - The time is: 2019-06-17 21:34:49.629352
[u'0.00', u'nan', u'26.74', u'110154.00', u'-48.69', u'0.00', u'0.00', u'315.00', u'51.13', u'0.00', u'0.37', u'0.00', u'1.23', u'45.00', u'315.00', u'1', u'2019-06-17 21:33:19', u'', u'0', u'-1', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'0.00', u'V:1', u'WXLMB ', u'0', u'', u'', u'0', u'', u'', u'0']
writing SQLdata
query=INSERT INTO OURWEATHERTable(timestamp, deviceid, Outdoor_Temperature , Outdoor_Humidity , Indoor_Temperature , Barometric_Pressure , Altitude , Current_Wind_Speed , Current_Wind_Gust , Current_Wind_Direction , Rain_Total , Wind_Speed_Minimum , Wind_Speed_Maximum , Wind_Gust_Minimum , Wind_Gust_Maximum , Wind_Direction_Minimum , Wind_Direction_Maximum , Display_English_Metrice , OurWeather_DateTime , OurWeather_Station_Name , Current_Air_Quality_Sensor , Current_Air_Quality_Qualitative, Battery_Voltage, Battery_Current, Solar_Voltage, Solar_Current, Load_Voltage, Load_Current ) VALUES(UTC_TIMESTAMP(), 1, 0.000, nan, 26.740, 110154.000, -48.690, 0.000, 0.000, 315.000, 51.130, 0.000, 0.370, 0.000, 1.230, 45.000, 315.000, 1, "2019-06-17 21:33:19" , "", 0, -1,0.000, 0.000, 0.000,0.000,0.000,0.000)
Traceback (most recent call last):
File "DataLogger.py", line 158, in <module>
OURWEATHERFunctions.readOURWEATHERData(password)
File "/home/pi/SDL_Pi_DataLogger/OURWEATHERFunctions.py", line 120, in readOURWEATHERData
cur.execute(query)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'DataLogger.OURWEATHERTable' doesn't exist")
|
|
|
|
Post by SDL on Jun 20, 2019 8:03:15 GMT -8
Do you have phpMyAdmin installed? If not, grab a tutorial and install it on your Pi. It is a program that allows you to see the MySQL databases. This will help you debug this.
BP
|
|
|
|
Post by carlos on Jun 22, 2019 5:03:29 GMT -8
Yes I have mySQL database installed. It just seems thet the system is really unstable.
|
|
|
|
Post by SDL on Jun 22, 2019 16:25:58 GMT -8
(1146, "Table 'DataLogger.OURWEATHERTable' doesn't exist") is a hard error.
Set up phpmyadmin and see what is really in your database.
BP
|
|
