importing data from mariadb into zway

Discussions about Z-Way software and Z-Wave technology in general
philippescholtes
Posts: 17
Joined: 01 Aug 2019 05:14

importing data from mariadb into zway

Post by philippescholtes »

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
seattleneil
Posts: 172
Joined: 02 Mar 2020 22:41

Re: importing data from mariadb into zway

Post by seattleneil »

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.
virtual device.JPG
virtual device.JPG (88.29 KiB) Viewed 2522 times
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"
Sensor Device.JPG
Sensor Device.JPG (61.75 KiB) Viewed 2522 times
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.
enbemokel
Posts: 482
Joined: 08 Aug 2016 17:36

Re: importing data from mariadb into zway

Post by enbemokel »

Wow, this is something for z-way team to put in a FAQ.
Thanks a lot for the detailed description.
Best regards
Nico
philippescholtes
Posts: 17
Joined: 01 Aug 2019 05:14

Re: importing data from mariadb into zway

Post by philippescholtes »

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
philippescholtes
Posts: 17
Joined: 01 Aug 2019 05:14

Re: importing data from mariadb into zway

Post by philippescholtes »

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 ...
seattleneil
Posts: 172
Joined: 02 Mar 2020 22:41

Re: importing data from mariadb into zway

Post by seattleneil »

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).
philippescholtes
Posts: 17
Joined: 01 Aug 2019 05:14

Re: importing data from mariadb into zway

Post by philippescholtes »

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.
seattleneil
Posts: 172
Joined: 02 Mar 2020 22:41

Re: importing data from mariadb into zway

Post by seattleneil »

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]')
philippescholtes
Posts: 17
Joined: 01 Aug 2019 05:14

Re: importing data from mariadb into zway

Post by philippescholtes »

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.
seattleneil
Posts: 172
Joined: 02 Mar 2020 22:41

Re: importing data from mariadb into zway

Post by seattleneil »

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.
Post Reply