RF22 Wireless Temperature Sensor - Log Data in MySQL

[CN]

Hi, are there any tutorials\scripts available to log the temp data from the RF22 wireless temperature sensor in a local MySQL database on a Raspberry Pi?  Thanks!

[Gadjet Nut]

Not online, but I did do a chapter on it in this book:

https://books.google.ca/books?id=4R3yCQAAQBAJ&pg=PA207&dq=raspberry+pi+projects+temperature+logger&hl=en&sa=X&ved=0ahUKEwjj55r8isDKAhXElIMKHfZvCXQQ6AEILzAA#v=onepage&q=raspberry%20pi%20projects%20temperature%20logger&f=false

[CN]

FYI, after looking more into it, I found some Python scripts that updated temperatures in MySQL.  I changed some things around to get it to work with the RF22 wireless temperature sensor.  I just wanted to post the scripts I cobbled together to help others.  This was the first Python scripting I've edited, so I'm sure there are many more efficient ways to do this. 

These scripts will assume you've already installed\configured MySQL 5.4+, mysql-client package, PHPMyAdmin (if you prefer the graphical interface to MySQL), Python, python-serial package, and have a fully-built\communicating RF22 wireless sensor.  For help on this, please refer to the many tutorials on PrivateEyePi and online.

1. With MySQL installed, create a user that can access a database.  You can use root, but for better security, you can create one user per database.
2. Create a database that the user can manage.  I named my database [pi_temp]
3. Create a table in the [pi_temp] database.  I named it [temp_a].
4. In the [temp_a] table, create the following columns with these options:
1. id: MEDIUMINT type, NOT NULL, AUTO_INCREMENT, PRIMARY KEY
2. date: DATE type
3. time: TIME type
4. temperature: DECIMAL(4,2) type
5. Create a python script named get_temp.py (FYI, this was modified from rfthermtest.py that comes from PrivateEyePi).  
1. Paste the get_temp.py script from below into the file.  Save.
   
2. sudo chmod 755 get_temp.py
6. Create a python script name pitemp.py
1. Paste the pitemp.py script from below into the file
2. Modify the variables to match your MySQL data. Save.
3. sudo chmod 755 pitemp.py
7. Create bash script to query the database, named sensor_temps.sh
1. Paste the sensor_temp.sh script from below into the file
2. Modify the variables to match your MySQL data.  Save.
3. sudo chmod 755 sensor_temps.sh
8. You can run get_temp.py by itself to see if it returns data from your RF22
1. sudo python get_temp.py
2. This should return the temperature
3. The script waits 5 minutes 30 seconds to receive
   
4. If it does not return the temperature, it will put an error into /var/log/syslog, and quit
   
9. Make sure pitemp.py and get_temp.py are in the same folder.
10. Try running pitemp.py to see if it can connect to your MySQL database
1. sudo python pitemp.py
2. pitemp.py requires temperature from get_temp.py.  If this doesn't work, pitemp.py will quit out.
   
3. If successful, you won't see any output
4. If it has the ability to connect to the MySQL database, it will insert a row.
   
5. sudo python pitemp.py
11. Run sensor_temps.sh to view your data in the database
1.   ./sensor_temps.sh
12. Create a cron job to automatically schedule pitemp.py
1. I wanted the script to record temperatures once an hour
2. sudo crontab -e
3. At the bottom paste in this line: 0 * * * * python /path/to/pitemp.py
4. Save file
5. Reboot machine and at the top of every hour, the script will run
   

get_temp.py

#!/usr/bin/env python
"""
get_temp.py 1.03 modified from rfthermtest.py 1.00 PrivateEyePi RF Temperature Test Program
---------------------------------------------------------------------------------
 Visit projects.privateeyepi.com for full details                                 
                                                                                  
 J. Evans October 2013       
 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
 CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.                                                       
                                                                                  
 Revision History                                                                  
 V1.00 - Release                                                             
 V1.01 - Added 5 minute script limit, and quits after results are found - CN
 V1.02 - Added syslog error notification if no results received - CN
 V1.03 - Added lines to convert Celsius to Fahrenheit - CN
 -----------------------------------------------------------------------------------
"""

import serial
import time
import sys
import signal
import syslog
from time import sleep

def handler(signum, frame):
    # Sets up time limit for script.  If signal.alarm() time is met, script quits
    # Puts error message in system syslog
    syslog.syslog(syslog.LOG_ERR, "ERROR: Did not receive temp after 5.5 mins - check sensor")
    exit(0)

def main():
        # declare to variables, holding the com port we wish to talk to and the speed
        port = '/dev/ttyAMA0'
        baud = 9600
        
        # open a serial connection using the variables above
        ser = serial.Serial(port=port, baudrate=baud)
        
        # wait for a moment before doing anything else
        sleep(0.2)
        
        # Install signal handler
        signal.signal(signal.SIGALRM, handler)

        # Set alarm for 5 minutes 30 seconds; if time limit reached, script quits
        signal.alarm(330)

        # print "Please wait max 5 mins for the temperature transmitter to transmit..."

        while True:
                while ser.inWaiting():
                        # read a single character
                        char = ser.read()
                        # check we have the start of a LLAP message
                        if char == 'a':
                                # start building the full llap message by adding the 'a' we have
                                llapMsg = 'a'
                                
                                # read in the next 11 characters form the serial buffer
                                # into the llap message
                                llapMsg += ser.read(11)
                                
                                # now we split the llap message apart into devID and data
                                devID = llapMsg[1:3]
                                data = llapMsg[3:]
                                
                                # print "Device Number : " + devID
                                # print "Temperature data : " + data
                                
                                # Strips the "TMPA" string from the output and converts to float for temp conversion
                                ctemp = float(data.strip('TMPA'))
                                
                                # convert celsius to fahrenheit, round to 2 decimal points
                                ftemp = round(ctemp * 1.8 + 32,2)
                                
                                # print data
                                print ftemp

                                # Comment out the 2 ftemp lines above and uncomment the "print ctemp" line
                                # if you want temperatures in celsius
                                
                                # print ctemp
                                
                        sleep(0.2)
                        sys.exit()
if __name__ == "__main__":
        main()

pitemp.py

#!/usr/bin/python
import time
import MySQLdb
import sys
import subprocess

dbhost="localhost"             # Enter hostname of MySQL database
dbuser="your_db_user"          # Enter MySQL user
dbpass="your_db_pass"          # Enter MySQL user password
pidb="your_mysql_db"           # Enter MySQL database
pitable="your_mysql_table"     # Enter MySQL database table
gettemp="/path/to/get_temp.py" # Enter path to get_temp.py

# Pull temp from temp sensor with get_temp.py
# Can take up to 5 minutes 30 seconds
temp = subprocess.check_output(('python',gettemp))

if not temp:

# If no temp was received from get_temp.py, quit script
   print "No temperature received from get_temp.py...quitting."
   sys.exit()

# If get_temp.py returns a temperature, attempt db write
else:

   #connect to database
   db = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpass,db=pidb)
   curs=db.cursor()

   #insert into database
   try:
           # Inserts date, time, temperature (id should auto increment) into database table entered above
           curs.execute ("INSERT INTO " + pitable + " (id, date, time, temperature) VALUES (NULL, CURDATE(), CURTIME(), %s) """, (temp,))
           db.commit()

   except db.Error, e:
           # Reports back an error if insert fails and rolls back change
           print "Error %d: %s" % (e.args[0],e.args[1])
           db.rollback()

   db.close()

sensor_temps.sh

#!/bin/bash

dbuser="your_db_user"       # Enter MySQL database user
dbpass="your_db_pass"       # Enter MySQL database password
pidb="your_mysql_db"        # Enter MySQL database name
pitable="your_mysql_table"  # Enter MySQL table name
dbrows="12"                 # Number of rows to return

# lists latest temps from PrivateEyePi Temp Sensor
echo ""
echo "Temp Sensor:"
mysql --user=$dbuser --password=$dbpass -e "SELECT * FROM $pidb.$pitable ORDER BY id desc LIMIT $dbrows;"
echo ""

[CN]

FYI, here are a few commands to create the database and tables in mysql-client:

1. Launch the mysql client and log in with a privileged account:
   mysql --user=username --password=password
   
   
2. Create a database named "pi_temp" at the mysql> prompt:
   CREATE DATABASE pi_temp;
   
   
3. Select the pi_temp database:
   USE pi_temp
   
   
4. Create the "temp_a" table:
   CREATE TABLE temp_a (id MEDIUMINT NOT NULL AUTO_INCREMENT, date DATE, time TIME, temperature DECIMAL(4,2), PRIMARY KEY (id));
   
   
5. Done.

The table looks like this in PHPMyAdmin:

Also FYI, here's what the sensor_temps.sh script looks like when it queries the database and returns temp results (mine are in Fahrenheit):

[Gadjet Nut]

Great tutorial, thanks. 

[Matthew Pook]

Hi,

Just following your instructions however running into a few issues. I'm getting an error when I can the get_temp.py script:

pi@raspberrypi:/tmp $ sudo python get_temp.py

Please wait max 5 mins for the temperature transmitter to transmit...

Device Number : 52
Temperature data : AWAKE----
Traceback (most recent call last):
  File "get_temp.py", line 92, in <module>
    main()
  File "get_temp.py", line 76, in main

    ctemp = float(data.strip('TMPA'))

ValueError: could not convert string to float: WAKE----



I assume it's trying to strip the incorrect value? Did you manage to get around this? It works with the standard serial_mon.py script..

Thanks!

[CN]

Sorry for the late response.  There are two lines in the script that are commented out:

# print "Device Number : " + devID
# print "Temperature data : " + data

I would delete the # symbols in front then run the script again manually.  It should show what data is returned by the variables.  The "Temperature data" should be a number. 

It's possible the firmware has been updated on devices and the data received is changed.  If the data is text, then it will break the rest of the script.  I seem to remember the temperature sensor would output text if the battery was dying for example.  If they are numbers, or have spaces in the data, you may have to mess with these lines to change how the text is split up:

# now we split the llap message apart into devID and data
devID = llapMsg[1:3]
data = llapMsg[3:]

[CN]

Also FYI, the script reads the data from the serial interface.  Then the two commands below split the string into 2 parts, the Device ID, and the temperature data:

devID = llapMsg[1:3] - this means it reads the string (which starts at 0), then takes character 1 and 2, and assigns them the "devID" variable (start at 1, stop at 3).

data = llapMsg[3:] - this line reads the string starting at character 3, and puts everything else into the "data" variable.

[Marko Kääriäinen]

I think there's something wrong with this line "ctemp = float(data.strip('TMPA'))"

When i remove this line, script print temperature: TMPAxx.xx. When i add float line i got error:

Device Number : 44

Temperature data : AWAKE----

Traceback (most recent call last):

  File "get_temp.py", line 92, in <module>

    main()

  File "get_temp.py", line 76, in main

    ctemp = float(data.strip('TMPA'))

ValueError: could not convert string to float: WAKE----

Original rfthertest.py output:

Device Number : 44

Temperature data : AWAKE----

Device Number : 44

Temperature data : TMPA20.99

Device Number : 44

Temperature data : SLEEPING-

[CN]

The issue is the script is reading the "AWAKE" output from the sensor, and trying to pull a temperature out of it, which doesn't work.  It should skip that output and wait for the line that has the temperature data (TMPA20.99 in your case). 

The python code could be modified to say  IF OUTPUT IS LIKE "TMPA" THEN proceed to chop up the output.  Unfortunately I am no longer using the serial sensor so I can't test it. 

[Make]

Has anyone got this to work?

[Gadjet Nut]

Yes we can share some code on how to do this. This is a very old thread so just want to be sure you are wanting to log data from the wireless sensors to a SQL database? 

[Make]

Yes :)

[Gadjet Nut]

We've published a new tutorial here that should help you with create a database logger for the wireless sensors:

http://projects.privateeyepi.com/home/create-a-database-logger

[Adrian Just]

Hey Gadjet

I realize I'm necro'ing this thread, my apologies on that but I've been using the guide you created via projects.privateeyepi.com and have run into an issue. I have all of the aforementioned hardware working correctly and its logging temperatures perfectly...in Celsius. I'm needing this to be done in Fahrenheit. I've been through the script over and over again (I'm a SQL newb to be clear) and I can't seem to get it to simply switch over to Fahrenheit seeing as the initial value at the top of the script of "Fahrenheit = False" being flipped over to True simply doesn't work and simply creates a floating point error message.

Is there any way you could provide some more insight into this, please? 

[Gadjet Nut]

Can you please post the error? 

[Gadjet Nut]

There was a bug in the Fahrenheit conversion. Its fixed now. You can download a new copy of rflog_db.py or change the following line:

value = value*1.8+32 

to

value = float(value)*1.8+32  

 

On Wed, Apr 10, 2019 at 5:45 PM Adrian Just <aj...@twinpine.com> wrote: