raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

getting two Pis to dump sensor readings into one SQL file

Mon Jan 25, 2016 6:37 pm

I am working on setting up two Pis to measure temperate and humidity for two different places in my house, both Pis are connected to the same network. I would like the readings from the sensors connected to both Pis to be inserted into one .db file. How do I accomplish this?
I'm a Pi noob, but I have experience with Python and SQLite.

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Jan 25, 2016 7:25 pm

raniat123 wrote:I am working on setting up two Pis to measure temperate and humidity for two different places in my house, both Pis are connected to the same network. I would like the readings from the sensors connected to both Pis to be inserted into one .db file. How do I accomplish this?
I'm a Pi noob, but I have experience with Python and SQLite.
Quite a few ways of doing this, but a quick solution would be to use MQTT:
PiX has the db and MQTT Server on it and subscribes to (for example) topics PiX/Temp/*, PiY/Temp/*,PiX/Hum/* and PiY/Temp/* and writes the received data to the db.
PiX publishes its data to topics PiX/Temp/ and PiX/Hum/.
PiY publishes its data to topics PiY/Temp/ and PiY/Hum/.

geekinthesticks
Posts: 97
Joined: Fri Feb 08, 2013 7:22 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Wed Jan 27, 2016 9:22 pm

The way I do this is to set up a MySQL database on a non Pi server on my network. You can post the sensor readings to this remote database (see the host parameter in the mysql docs) from your pi's. I have a field for the hostname if the database, so I can identify which Pi the reading originated from.

You could probably do the same thing using sqlite.

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Thu Jan 28, 2016 7:48 pm

[/quote]
and subscribes to (for example) topics PiX/Temp/*, PiY/Temp/*,PiX/Hum/* and PiY/Temp/* and writes the received data to the db.
[/quote]

How do you access/or subscribe to a topic on the .db file that exists on PiX from PiY?
I'm guessing I would probably have to mount PiX on the network. I apologize if I'm using the wrong terminologies, my experience with the Pi is no more than few simple projects such as LED blinking, and using the push button.

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Fri Jan 29, 2016 8:22 pm

raniat123 wrote:
and subscribes to (for example) topics PiX/Temp/*, PiY/Temp/*,PiX/Hum/* and PiY/Temp/* and writes the received data to the db.
How do you access/or subscribe to a topic on the .db file that exists on PiX from PiY?
I'm guessing I would probably have to mount PiX on the network. I apologize if I'm using the wrong terminologies, my experience with the Pi is no more than few simple projects such as LED blinking, and using the push button.
MQTT is one of several products that use Message Queues. A tutorial for RabbitMQ is here, and most of the concepts will apply to MQTT (routing and RPC might be beyond its capabilities).
Basically, you have the 'clients' sending messages (publish) and receiving them (subscribe) over the network. There is a server (the broker) that manages which messages are routed where, and can (optionally) store them until a client can receive them.

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 01, 2016 2:51 am

asandford wrote:
raniat123 wrote:
and subscribes to (for example) topics PiX/Temp/*, PiY/Temp/*,PiX/Hum/* and PiY/Temp/* and writes the received data to the db.
How do you access/or subscribe to a topic on the .db file that exists on PiX from PiY?
I'm guessing I would probably have to mount PiX on the network. I apologize if I'm using the wrong terminologies, my experience with the Pi is no more than few simple projects such as LED blinking, and using the push button.
MQTT is one of several products that use Message Queues. A tutorial for RabbitMQ is here, and most of the concepts will apply to MQTT (routing and RPC might be beyond its capabilities).
Basically, you have the 'clients' sending messages (publish) and receiving them (subscribe) over the network. There is a server (the broker) that manages which messages are routed where, and can (optionally) store them until a client can receive them.
Thank you so much. It looks like I have to go through a learning curve here, but it's totally worth it.

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 01, 2016 5:59 pm

raniat123 wrote:
Thank you so much. It looks like I have to go through a learning curve here, but it's totally worth it.
Let us know how you get on.

Slackware
Posts: 131
Joined: Mon Jan 18, 2016 3:45 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Thu Feb 11, 2016 1:10 pm

Why use two pi's? One will handle all the sensors you could ever want and then some. I've got 12 temp / humidity sensors in the attic alone.

grahamed
Posts: 277
Joined: Mon Jan 30, 2012 7:01 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Thu Feb 11, 2016 7:46 pm

"Why use 2 Pi's....."

Perhaps for the same reason as I use 4 - physical location and wiring.

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 3:49 pm

grahamed wrote:"Why use 2 Pi's....."

Perhaps for the same reason as I use 4 - physical location and wiring.
That's it. actually, 2 different locations that are not within my house. And it would be impossible to run wires to the other location.

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 3:59 pm

The MQTT sounds like a good solution for my situation. However, I have one small problem. I setup a cron job to run a python script on the first Pi that reads the sensor data and puts it in my SQL database, the cron job runs the script every 30 minutes. You can see my data here http://pi.ranialabib.com/cgi-bin/Test_3.py . The problem is I need the same python script to run on my second time at the same time the script on the first Pi runs because I'm going to graph the results o top of existing graphs. In other words, the "Timestap" created in my SQL database has to be identical on both tables from both Pis. Any ideas?

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 7:35 pm

raniat123 wrote:The MQTT sounds like a good solution for my situation. However, I have one small problem. I setup a cron job to run a python script on the first Pi that reads the sensor data and puts it in my SQL database, the cron job runs the script every 30 minutes. You can see my data here http://pi.ranialabib.com/cgi-bin/Test_3.py . The problem is I need the same python script to run on my second time at the same time the script on the first Pi runs because I'm going to graph the results o top of existing graphs. In other words, the "Timestap" created in my SQL database has to be identical on both tables from both Pis. Any ideas?
You could publish a message to a /command (or something) topic that both receive at the same time to trigger the script. So instead of the cron job running the script, it publishes a message. All subscribers should receive the message at the same time (more-or-less, you can test for delays and factor them in).

User avatar
joan
Posts: 14180
Joined: Thu Jul 05, 2012 5:09 pm
Location: UK

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 7:54 pm

The Pis appear to be networked so presumably NTP will give each machine the same time. Why not just take a reading at known minute boundaries?

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 10:30 pm

joan wrote:The Pis appear to be networked so presumably NTP will give each machine the same time. Why not just take a reading at known minute boundaries?
Do you mean use cron job at the same specific minute on both Pis?

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 10:36 pm

raniat123 wrote: Do you mean use cron job at the same specific minute on both Pis?
Instead of using cron to run scripts, use MQTT as a (crude) job scheduler or even better, use Node Red.

User avatar
joan
Posts: 14180
Joined: Thu Jul 05, 2012 5:09 pm
Location: UK

Re: getting two Pis to dump sensor readings into one SQL fil

Mon Feb 22, 2016 11:09 pm

You could use cron or the following type of Python fragment.

Code: Select all

#!/usr/bin/env python

import time

INTERVAL=10

while True:
   next = ((time.time()//INTERVAL)*INTERVAL)+INTERVAL
   time.sleep(next-time.time())
   print(time.time()) # add code here

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 8:33 pm

I've heard about the XBee module. Can I use this module for such a project? doesn't it accomplish what MQTT does? I'm totally confused :roll: After reading about the XBee module I got the feeling that it's much easier to use than MQTT, at lease for me because I'm totally a newbie in networking. I'm practicing on this project for a much larger project where real-time interaction between nodes is crucial, do you guys think that MQTT or XBee would be best in providing real-time interaction?

User avatar
joan
Posts: 14180
Joined: Thu Jul 05, 2012 5:09 pm
Location: UK

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 8:38 pm

Real-time wasn't mentioned before and doesn't tie in with what you previously posted.

Perhaps you had better explain in more detail what you are trying to do.

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 8:52 pm

raniat123 wrote:I've heard about the XBee module. Can I use this module for such a project? doesn't it accomplish what MQTT does? I'm totally confused :roll: After reading about the XBee module I got the feeling that it's much easier to use than MQTT, at lease for me because I'm totally a newbie in networking. I'm practicing on this project for a much larger project where real-time interaction between nodes is crucial, do you guys think that MQTT or XBee would be best in providing real-time interaction?
XBees are just radios. They can be setup to be point-to-point (one-to-one) or star (one-to-many). Depending on the range you require, they can be expensive. You'll still need software to pump data into them and process anything received.

MQTT wasn't built for speed (though it is quite fast), it was built for simplicity and resilience. If you need a blindingly fast MQ client (which also happens to be brokerless), try 0MQ (Zero MQ).

hackernotcracker
Posts: 14
Joined: Tue Feb 23, 2016 6:23 am

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 9:15 pm

There is also a pure python mysql client, that can connect to remote mysql databases; You simply make text strings that have SQL queries and commands, and it can do the rest. Since python is platform independent, you don't need any special binaries if you go that route.

I've written web pages completely in python for commercial applications using this method.
If you are interested, I can pull up the source code from an old web page I wrote and find the http:// for the source code to the client. As it's GPL, I can also give you a copy of the code that I have.

raniat123
Posts: 33
Joined: Fri Jan 22, 2016 10:09 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 9:49 pm

joan wrote:Real-time wasn't mentioned before and doesn't tie in with what you previously posted.

Perhaps you had better explain in more detail what you are trying to do.
Real-time doesn't matter for this current project. I'm only practicing how to network multiple Pis together. However for my next project I'm going to network multiple Pis or sensor nodes and utilize real-time interactions between them. I've read somewhere that real-time and speed are better achieved in C which probably means that an Arduino might be a better micro-controller to use for such a project. I'd love to hear the opinions of you all.

asandford
Posts: 1997
Joined: Mon Dec 31, 2012 12:54 pm
Location: Waterlooville

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 10:50 pm

raniat123 wrote: Real-time doesn't matter for this current project. I'm only practicing how to network multiple Pis together. However for my next project I'm going to network multiple Pis or sensor nodes and utilize real-time interactions between them. I've read somewhere that real-time and speed are better achieved in C which probably means that an Arduino might be a better micro-controller to use for such a project. I'd love to hear the opinions of you all.
The Pi will never give you real-time with linux (depending on how 'real-time' you need). Arduinos will, but connectivity, memory and processing power are limited. You might be better off with a hybrid solution - reading the sensors with Arduinos and sending the data to a Pi for further analysis.

User avatar
joan
Posts: 14180
Joined: Thu Jul 05, 2012 5:09 pm
Location: UK

Re: getting two Pis to dump sensor readings into one SQL fil

Tue Feb 23, 2016 11:18 pm

Real-time has a particular meaning in software engineering. If you do have real-time requirements you need to specify them all before you even start thinking of hardware.

flyswatter12
Posts: 3
Joined: Tue Feb 23, 2016 11:20 pm

Re: getting two Pis to dump sensor readings into one SQL fil

Wed Feb 24, 2016 12:18 am

For "real time" sensor nodes, I assume you mean on the order of same second or same minute type networking. The Pi is much better for that in my opinion than an Arduino or other embedded processor.

If you're a few milliseconds off here or there, it's not a big deal for most data logging.

Also, for your original post, I'd set up a MySQL or SQLite instance on one of the two Pi's, and designate it as the master. All the other Pi's should report back to the Master Pi's SQL database and update their sensor reading, and that would give you a "Push" type system. If you want them to be more coordinated, the "Master" PI could query each of the other nodes as to it's current reading, on some schedule you've set up, then update the master database with that data. This would be relatively easy to do using any number of HTTP or TCP client / server type of architectures.

hackernotcracker
Posts: 14
Joined: Tue Feb 23, 2016 6:23 am

Re: getting two Pis to dump sensor readings into one SQL fil

Wed Feb 24, 2016 9:06 pm

asandford wrote: The Pi will never give you real-time with linux (depending on how 'real-time' you need). Arduinos will, but connectivity, memory and processing power are limited. You might be better off with a hybrid solution - reading the sensors with Arduinos and sending the data to a Pi for further analysis.
Actually, there are patches to the linux kernel in order to handle real time applications, like CNC controllers. It would require patching and recompiling the kernel, and probably using a much older kernel; so there would be lots of problems to fight... but it does exist, and I have done it before. :D

Also, if the rasperry PI 2 with 4 cores it the device in use; there is the possibility of dedicating a single core entirely to reading a sensor; eg: detach it from linux, and run a single assembly language program on it.

The hybrid solution with a microcontroller is probably the easiest/best. There are also simple TI MSP430 processors which compete with adruinos. Simple ones sell for around U.S. 60 cents to $1.20 each; They are 16 bit processors, and ultra low power. Those often have SPI buses implemented in hardware, depending on which one you choose. So, interfacing to the PI is almost trivial.

The last time I checked, TI was preparing to release a solder RF module that allows the MSP 430 to do full .80211B and maybe G.
That's about the fastest wireless you are going to get...

There are also BlueTooth wireless USART modules from china (eg: Like RS232C signals but only 0V to +3.3 or +5V levels). They are normally sold for Adruino, but they work with TI MSP430 just fine, too. I just bought two of them to interface to a parallax controller for CNC work. Those modules sell for around $4 to $6 from China (post paid) on ebay.

Note: my MSP programmer (USB dongle) ] does work on linux. The development software is just GNU GCC. eg: Software can even be developed on the PI itself, and windows is not required. If you're on a super budget, and just experimenting ... there are also people like me who don't mind programming a few of our spare chips and sending them to people as long as the postage isn't excessive.

Return to “Automation, sensing and robotics”