Read my previous post about DIY home monitoring system – part two.
What have we done so far? We’ve installed Apache, PHP, MySQL and phpmyadmin on Rasbian. We’ve also installed Python and ruuvitag library. Now we need to get the data out of the tags and either store it or forward it to “some place”. The “place” will be close. First we are going to receive the data from ruuvi’s to Rasbian. Then we are going to store the data to a MySQL database and send it over to Power BI with the help of Power BI REST interface. Let’s continue…
Let’s take a look of the ready-made ruuvi library. Check the following code at https://pypi.org/project/ruuvitag_sensor/.
from ruuvitag_sensor.ruuvi import RuuviTagSensor
# List of macs of sensors which data will be collected
# If list is empty, data will be collected for all found sensors
macs = [‘AA:2C:6A:1E:59:3D’, ‘CC:2C:6A:1E:59:3D’]
# get_data_for_sensors will look data for the duration of timeout_in_sec
timeout_in_sec = 4datas = RuuviTagSensor.get_data_for_sensors(macs, timeout_in_sec)
# Dictionary will have lates data for each sensor
print(datas[‘AA:2C:6A:1E:59:3D’])
print(datas[‘CC:2C:6A:1E:59:3D’])
Copy the code create a new file under /home/pi/ by running the following (you can use whatever editor you like, I like pico).
sudo pico ruuvi.py
Paste the code. Look for the “macs” in the code. Type in the mac addresses you got from your Android. Change the url part in the code to match your IP address or localhost. To check your IP address, type ifconfig on your Rasbian or use “localhost”, which will point to your local web root directory.
See the line, that has print(datas… Add one of your ruuvitag’s mac addresses there. Then run the code by typing
sudo python3 ruuvi.py
If you get a result with temperature, humidity and pressure, congratulations! Your python code is working. Now, change the code to match the following:
from ruuvitag_sensor.ruuvi import RuuviTagSensor
# List of macs of sensors which data will be collected
# If list is empty, data will be collected for all found sensors
macs = [‘AA:2C:6A:1E:59:3D’, ‘CC:2C:6A:1E:59:3D’]
# get_data_for_sensors will look data for the duration of timeout_in_sec
timeout_in_sec = 4url = ‘http://localhost/data/’
datas = RuuviTagSensor.get_data_for_sensors(macs, timeout_in_sec)
for key, value in datas.items():
requests.post(url, json=value)
Remember to use your own MAC addresses. Next, you saw that we change the url address. We typed in the http://localhost/data/ address. There’s nothing in that address at the moment. We’ll cover this soon.
Type the following command for moving yourself to the correct folder:
cd /var/www/html/data/
Then create a new file (again using your favourite editor, for me, that’s pico)
sudo pico index.php
Copy and paste the code below. We are going to make some adjustments to it later on.
<?php
$in = file_get_contents(“php://input”);
$file = “values.txt”;
$current = file_get_contents($file);
$current .= “\n:” . date(“d.m.Y h:i:s”) . $in;
$put = file_put_contents($file, $current);
?>
Next, create an empty file called values.txt by running the following:
sudo pico values.txt
You can type in whatever and save the file. We are going to pick up the data from the http://localhost/data/index.php and print it out to a text file.
Now, run the python code you created earlier.
sudo python3 /home/pi/ruuvi.py
Yes, it might seem, that nothing happened, but let’s go and open the text file you created:
sudo pico /var/www/html/data/values.txt
You should see the following content depending on the amount of your tags. I have five of them running.
The identifier should match with the one you got from Android. Note, that the identifier can be a letter or a number.
Now, run again the command and open the text file again. You should see another five lines (or as many tags as you have). So, every time you run the python script, it will send the data to your local web server, Apache and Apache will pick it up from there. Pay attention to the lines. You’ll notice two things: 1) the lines don’t have exact same timestamp, i.e. collecting data might take longer than a split second and 2) the order might differ, i.e. sometimes the line starts with pressure, other times with something else. This is ok, you just have to handle this.
We are going to save the values to MySQL database for few reasons: 1) you might want to store the data for historical reasons and 2) you want to fiddle up with the timestamp and the format the Power BI is picking up the data.
Create a database for tag data
Run the following command:
sudo mysql -p -h localhost -u root;
If you did not give a password when creating the database just click enter next. Now we are going to create a database, let’s day called “ruuvi”.
CREATE DATABASE ruuvi;
Then we are going to create a user, that will use this database only, run the following command:
GRANT ALL PRIVILEGES ON ruuvi.* TO ruuvi@’localhost’ IDENTIFIED BY ‘whateveryoufeel’;
Next, create a database table, where you are going to store the data.
CREATE TABLE ruuvidata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tagid VARCHAR(2), sensor VARCHAR(15), sensorvalue DECIMAL(10,2), ts DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
If you don’t have any issues with syntax, you will get a result like this: Query OK, 0 rows affected (0.10 sec).
Now we are going to modify the code so, that you will store the data to MySQL database instead of a text file. And for now, we are going to save the data every time you run the python code. Later on, we will schedule this, so you don’t have to press anything.
Edit the index.php file again and add the following lines to it:
$nicearray = json_decode($in,true);
/* mysql db */
$mysqli = new mysqli(“localhost”, “ruuvi”, “whateveryoufeel”, “ruuvi”);/* check connection */
if ($mysqli->connect_errno) {
printf(“Connect failed: %s\n”, $mysqli->connect_error);
exit();
}
You probably guessed, you are making a connection to your database with PHP from your index.php.
Next you need to insert the lines into the MySQL database. However, you need to differ the tags from each others by giving a parameter name for tagid, so that the following line {“identifier”: “C”, “humidity”: 46.0, “pressure”: 994.0, “temperature”: 25.0} would be inserted in the database as in the picture below.
We see, that tag called “C” has three values, one for temperature, one for humidity and one for air pressure. Insert the following lines to your code after
if(strlen($in)>1){
foreach ($nicearray as $key => $value) {
if (!($key == “identifier”)){
$tagid = $key . “_” . $tagi;
$result = $mysqli->query(“INSERT INTO ruuvidata (tagid,sensor,sensorvalue) VALUES (‘” . $tagid. “‘,'” . $key . “‘,” . $value. “)”);}
}
}
$mysqli->close();
You might want to print out the contents of $result for some debugging.
Now, run again the python:
sudo python3 ruuvi.py
Check the database with phpmyadmin
Go to your phpmyadmin (open the browser and type: http://localhost/phpmyadmin/).
Check that you have something in the database. If you do, try running the python code again. Now everytime you run the code, two things happens: you store the data into a text file and also to MySQL database.
We are ready to move to the final part.