importing data from mariadb into zway
-
- Posts: 17
- Joined: 01 Aug 2019 05:14
importing data from mariadb into zway
InfluxDB "stores sensor data and ZWave Network data in InfluxDB instances". But what about the opposite migration, i.e. importing into ZWave/ZWay data stored in databases such as MariaDB?
Concretely: I have a DHT22 temp+humidity sensor connected to the GPIO board of a Raspberry Pi; a crontab routine runs every ten minutes a Python programme collecting temperature and humidity data and storing them into a MariaDB table. Another Raspberry Pi in the same LAN hosts a Razberry card that controls my home automation.
I would like ZWay to read the MariaDB temp+humidity data and integrate them in the UI that I can access through the cloud in a browser or in the ZWay iPhone app.
Thank you
Concretely: I have a DHT22 temp+humidity sensor connected to the GPIO board of a Raspberry Pi; a crontab routine runs every ten minutes a Python programme collecting temperature and humidity data and storing them into a MariaDB table. Another Raspberry Pi in the same LAN hosts a Razberry card that controls my home automation.
I would like ZWay to read the MariaDB temp+humidity data and integrate them in the UI that I can access through the cloud in a browser or in the ZWay iPhone app.
Thank you
-
- Posts: 173
- Joined: 02 Mar 2020 22:41
Re: importing data from mariadb into zway
Z-Way supports several ways to do what you want to do, all of which involve creating 2 sensor devices - a temperature sensor device and a humidity sensor device. The key difference between the various approaches is how the sensor devices get created. Here's a relatively easy approach for adding a temperature sensor device that relies on "pushing" the data to Z-Way instead of having Z-Way "pull" the data from a database located on a remote server. In general, Z-Way will be more reliable if you don't ask it do to things that might take a lot of time which is why I suggest pushing by your temperature Pi instead of pulling by Z-Way. Once you work through the various steps, repeat the steps to add a humidity sensor device.
1. Using the web UI (i.e., point your browser to http://[Your Z-Way IP address]:8083), click on the configuration menu in the upper-right corner and select "Apps". Search for and add the app "Virtual Device (JavaScript)".
2. Fill out the template similar to what appears in the screenshot. 3. After you save the virtual device, you should now have a new Z-Way device called "Temperature" that you can add to your dashboard. As configured in the screenshot, the default value is 20C.
4. Next, you need to determine the deviceID for the sensor device you created in the previous step. It's going to be something like: "Code_Device_sensorMultilevel_XX". To do that, locate the sensor device in the web UI (use the Elements icon) and click on the gear icon for the sensor device. The deviceID will appear in the browser URL and will also appear in the the API commands for developers section. As you can see in the screenshot, my device is "Code_Device_sensorMultilevel_21" 5. The last step involves updating the sensor device from the Pi where your DHT22 is located to the Pi running Z-Way. A shell command to do that would look like something like this:
TEMP=23; curl -s -u [ZWAY USER NAME SUCH AS admin]:[ZWAY PASSWORD] --globoff "http://[Your Z-Way IP address]:8083/JS/Run/controller.devices.get('HTTP_Device_sensorMultilevel_XX').set('metrics:level',$TEMP)"
The shell command you use needs to have the correct username/password, IP address, deviceID and temperature value.
Good luck.
1. Using the web UI (i.e., point your browser to http://[Your Z-Way IP address]:8083), click on the configuration menu in the upper-right corner and select "Apps". Search for and add the app "Virtual Device (JavaScript)".
2. Fill out the template similar to what appears in the screenshot. 3. After you save the virtual device, you should now have a new Z-Way device called "Temperature" that you can add to your dashboard. As configured in the screenshot, the default value is 20C.
4. Next, you need to determine the deviceID for the sensor device you created in the previous step. It's going to be something like: "Code_Device_sensorMultilevel_XX". To do that, locate the sensor device in the web UI (use the Elements icon) and click on the gear icon for the sensor device. The deviceID will appear in the browser URL and will also appear in the the API commands for developers section. As you can see in the screenshot, my device is "Code_Device_sensorMultilevel_21" 5. The last step involves updating the sensor device from the Pi where your DHT22 is located to the Pi running Z-Way. A shell command to do that would look like something like this:
TEMP=23; curl -s -u [ZWAY USER NAME SUCH AS admin]:[ZWAY PASSWORD] --globoff "http://[Your Z-Way IP address]:8083/JS/Run/controller.devices.get('HTTP_Device_sensorMultilevel_XX').set('metrics:level',$TEMP)"
The shell command you use needs to have the correct username/password, IP address, deviceID and temperature value.
Good luck.
Re: importing data from mariadb into zway
Wow, this is something for z-way team to put in a FAQ.
Thanks a lot for the detailed description.
Best regards
Nico
Thanks a lot for the detailed description.
Best regards
Nico
-
- Posts: 17
- Joined: 01 Aug 2019 05:14
Re: importing data from mariadb into zway
Thanks a million seattleneil for the step-by-step explanations. I can't get it to work though, but I will, eventually. At this point Step 1-4 are completed, but executing the shell command returns an error message "TypeError: Cannot read property 'set' of null". Something in the syntax of "set('metrics:level',$TEMP)" I guess. But it is my first attempt; I will keep trying.
Thanks again
Thanks again
-
- Posts: 17
- Joined: 01 Aug 2019 05:14
Re: importing data from mariadb into zway
Update: I can push a number from the Pi to which the DHT22 sensor is connected, to the Pi that hosts the Z-wave controller. However I haven't been able to far to push the actual DHT22 reading of temperature to the virtual device in Z-Way. But I am progressing ...
-
- Posts: 173
- Joined: 02 Mar 2020 22:41
Re: importing data from mariadb into zway
Happy to hear you're getting close to success. I'm guessing you're programming in JavaScript and using node-libcurl. To help debug your code, you can "see" what's being sent over the network by installing tcpdump and running the command: "sudo tcpdump -i eth0 -n -A tcp port 8083" (assuming your ethernet interface is eth0).
-
- Posts: 17
- Joined: 01 Aug 2019 05:14
Re: importing data from mariadb into zway
No, I am not programming in JavaScript--I have very limited programming skills, and I have been using a small Python programme running on the Raspberry Pi to which the DHT22 sensor is connected. The Python programme reads the temperature and humidity values measured by DHT22 and inserts them into a MariaDB table. So far I was reading the MariaDB table in SQL and PowerBi; with the Virtual Device (Javascript) App you recommended I can now push the data onto the Z-Way UI accessible remotely on a laptop or mobile device. For the detail, I found on internet a way to convert the linux curl shell you propose in your item 5 above, to the following line embedded in my Python programme:
response = requests.get('http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\',temperature)', auth=('[myzwaycredentials]'))
"temperature" (and "humidity") are the variables capturing the readings from DHT22, and inserting their spot value in the MariaDB table. A crontab instruction runs the Python programme every x minutes, such that every x minutes new values of temperature and humidity are appended to the MariaDB table.
The frustration now--and it shows my nonexistent programming skills--is that if I replace by a number the word "temperature" in the Python line "response = requests ...", it does push the figure to the icon of the virtual device in the Z-Way UI. However, Python fails to understand that by "temperature" I mean the spot value of the variable "temperature", not the variable itself and the DHT reading-to-virtual device transfer does not work. Sounds basic enough, but to someone who has never learnt Python it is annoying.
response = requests.get('http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\',temperature)', auth=('[myzwaycredentials]'))
"temperature" (and "humidity") are the variables capturing the readings from DHT22, and inserting their spot value in the MariaDB table. A crontab instruction runs the Python programme every x minutes, such that every x minutes new values of temperature and humidity are appended to the MariaDB table.
The frustration now--and it shows my nonexistent programming skills--is that if I replace by a number the word "temperature" in the Python line "response = requests ...", it does push the figure to the icon of the virtual device in the Z-Way UI. However, Python fails to understand that by "temperature" I mean the spot value of the variable "temperature", not the variable itself and the DHT reading-to-virtual device transfer does not work. Sounds basic enough, but to someone who has never learnt Python it is annoying.
-
- Posts: 173
- Joined: 02 Mar 2020 22:41
Re: importing data from mariadb into zway
No need to stress. At some point, if you have time and energy, there's lots of excellent on-line training for python that you may want to pursue. Check out: https://wiki.python.org/moin/BeginnersGuide/Programmers
Try this:
response = requests.get('http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + temperature + ')', auth=('[myzwaycredentials]'))
Explanation: You need to concatenate a beginning string with a variable and an end string to create a new string (that combines the beginning string with the value of the variable, followed by the end string). This link explains how to do that (using the '+' operator): https://www.w3schools.com/python/gloss_ ... nation.asp
It might be easier to understand if re-written as follows:
myURL='http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + temperature + ')'
response = requests.get(myURL, auth=('[myzwaycredentials]')
Try this:
response = requests.get('http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + temperature + ')', auth=('[myzwaycredentials]'))
Explanation: You need to concatenate a beginning string with a variable and an end string to create a new string (that combines the beginning string with the value of the variable, followed by the end string). This link explains how to do that (using the '+' operator): https://www.w3schools.com/python/gloss_ ... nation.asp
It might be easier to understand if re-written as follows:
myURL='http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + temperature + ')'
response = requests.get(myURL, auth=('[myzwaycredentials]')
-
- Posts: 17
- Joined: 01 Aug 2019 05:14
Re: importing data from mariadb into zway
Thank you for the guidance ... and for your patience so far. Python returns an error message "TypeError: can only concatenate str (not "float") to str". And when I try and set the type of the values of temperature and humidity to "str" (thinking that as long as the numbers do not enter into arithmetical operations they will simply be read as text and appear in my Z-Way dashboard) but Python returns this time an "invalid syntax" error.
I will keep trying though, because a solution to this problem would make possible a number of practical applications in my smart home(s): for one, I have inserted in the electricity distribution box an RPi-based device that reads energy consumption (and calculates average power between consecutive readings) and stores the data in a MariaDB table that I can read remotely. Solving the problem that we have been discussing would pave the way for mapping the values of energy and power in virtual devices on my Z-Way dashboard.
I will keep trying though, because a solution to this problem would make possible a number of practical applications in my smart home(s): for one, I have inserted in the electricity distribution box an RPi-based device that reads energy consumption (and calculates average power between consecutive readings) and stores the data in a MariaDB table that I can read remotely. Solving the problem that we have been discussing would pave the way for mapping the values of energy and power in virtual devices on my Z-Way dashboard.
-
- Posts: 173
- Joined: 02 Mar 2020 22:41
Re: importing data from mariadb into zway
You're very close to success. Have you tried to do a Google search for "python convert float to string"?
To help climb the learning curve, you can run python interactively - just enter "python" at the shell command prompt and you should see the python prompt ">>>". Then enter a floating point value for the variable temperature, something like this: "temperature=23.4". To display the value of temperature, enter: "print(temperature)". Now you can create and print the variable myURL.
>>> myURL='http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + str(temperature) + ')'
Does it look like the following:
>>> print (myURL)
http://[myzwayip]:8083/JS/Run/controller.devices.get('Code_Device_sensorMultilevel_[myvirtualdeviceid]').set('metrics:level\,23.4)
Hopefully you'll notice myURL is not correct - there's a backslash instead of a single quote after level. Try to change the myURL=... so that the following appears:
>>> print (myURL)
http://[myzwayip]:8083/JS/Run/controller.devices.get('Code_Device_sensorMultilevel_[myvirtualdeviceid]').set('metrics:level',23.4)
I think you'll discover the answer yourself and get to enjoy the satisfaction that programmers feel when their software finally works. Just let me know if you get stuck and become frustrated.
To help climb the learning curve, you can run python interactively - just enter "python" at the shell command prompt and you should see the python prompt ">>>". Then enter a floating point value for the variable temperature, something like this: "temperature=23.4". To display the value of temperature, enter: "print(temperature)". Now you can create and print the variable myURL.
>>> myURL='http://[myzwayip]:8083/JS/Run/controller.devices.get(\'Code_Device_sensorMultilevel_[myvirtualdeviceid]\').set(\'metrics:level\,' + str(temperature) + ')'
Does it look like the following:
>>> print (myURL)
http://[myzwayip]:8083/JS/Run/controller.devices.get('Code_Device_sensorMultilevel_[myvirtualdeviceid]').set('metrics:level\,23.4)
Hopefully you'll notice myURL is not correct - there's a backslash instead of a single quote after level. Try to change the myURL=... so that the following appears:
>>> print (myURL)
http://[myzwayip]:8083/JS/Run/controller.devices.get('Code_Device_sensorMultilevel_[myvirtualdeviceid]').set('metrics:level',23.4)
I think you'll discover the answer yourself and get to enjoy the satisfaction that programmers feel when their software finally works. Just let me know if you get stuck and become frustrated.