Looking for some know-how after Googling failed miserably.
I am currently modernising an old water tank pressure sensor that Ive been using on the famr for a year or so. The plan is to integrate the water pressure / derrived water depth into HomeAssistant so I can easily see the info and trigger alerts based on remaining water levels etc.
My current problem is writing the pressure (from this) into an existing MySQL db hosted elsewhere on my LAN. My hardware options at the moment are:
ESP32 (M5Stack Atom Matrix), or
Raspberry Pi Pico W
The advantage of the ESP32 is that integration with HomeAssistant (via ESPHOME) is super easy, it will also store the data into a SQL dB for easy integration. The problem, is the rubbish ADC required to get accurate readings from the sensor.
The advantage of the Pico W is the much better ADC, making the data much more reliable and accurate. The disadvantage is writing the data to a SQL db AND a lack of HomeAssistant integration. The latter I can deal with if I can write data directly into a SQL db from the Pico W.
To date I have the pressure sensor working and calibrated nicely for a tank and can put the data onto an OLED display from the Pico W. I also have it working from the ESP32, but that needs an external ADC to make it reliable (and I dont have a spare one on hand!).
Is it possible to use the PicoW to write to a MySQL dB? I only need 1 record updating perhaps every 15 mins.
Another possible solution is configuring your Pico W as a simple HTTP server. The server can read the sensor value and send it back to the requester. You can find an example. here.
You can program your ESP32 to send a request to the HTTP server every 15 mins. Use the sensor’s value in the response from the HTTP server and do the rest using the ESP32 integration with HomeAssistan.
I am currently trying a Pico W as a weather station. It is taking a reading every minute on the minute. The readings are date and time (from the inbuilt RTC), temperature (from PiicoDev TMP117), pressure and humidity (from PiicoDev BME280). Unlike others have not had any difficulty with the PiicoDev sensors.
It sends readings to my external webserver which writes to its SQL database.
To get the readings to the server I am using urequests.get() viz
r = urequests.get(url = 'https://www.myserver.com/iot.php?date='+date+'&time='+time+'&temp='+tempC+'&humidity='+humidity+'&pressure='+pressure)
The traps are the data must be in text format and there must be no blanks in the url.
The iot.php file uses PHP to receive the data as _GET['date'] _GET[‘temp’] etc. PHP writes the data into an SQL database.
I do not use HomeAssistant. Since I have my own webserver and with the data in an SQL database I have constructed my own. While web pages are normally static I use AJAX to keep the page continously up to date.
So to answer your question the Pico W can definitely store data in an external SQL database. Let me know if you need any help.
I had a think about what I actually needed from the data. Turns out I only need minimal info and there is no real need to be able to store it long term.
I started looking into MQTT. MQTT has an official Mosquitto broker add-on for HomeAssistant and a micropython port for the Pico.
A day or two later and I have a MQTT broker working on HomeAssistant and two devices sending data over the protocol (without having to buy more bits & bobs!). One of which is the Pico W and the other an old RaspberryPi 3B with a relay board attached. The Pico simply sends an ADC value from my pressure sensor which HomeAssistant picks up, displays the data on a chart and monitors the value against a set range inside a HomeAssisatnt Automation. If the range steps outside of the permissible range, I get a notification on my phone. Perfect.
The Pi3B has a relay board and ds18b20 temp sensor attached. It works similar, gets the temperature in the top of my comms cabinet and sends the temp to HomeAssistant. Within the python code on the Pi, if the temp gets to a trigger level, the relay closes and the cabiet exhaust fans come on… Once again, perfect.
Thanks again for the pointers. Amazing what a bit of lateral thinking to a problem produces!
Any chance to get help too as my project looks very similar to your project? I am trying to sent sensor data from Pi Pico W to external SQL database but I have no idea how to do that… I was trying to google it and find any valuable answer but mainly it is only showing me examples for a little bigger Pi using own SQL Lite server.
See my above post. Having a Pico W write to an external MySQL DB is actually quite easy and very secure. It is also very reliable - mine has been working for months without even a reboot. I included some code snippets. Where are you having trouble? Is it the Pico W or the server side? Note the Pico W does not directly write to the MySQL DB. That would not be secure and is usually prohibited by hosting providers.
p.s. I thought this topic was “Pico W writing into MySQL db?”.
I just started my adventure with Pi Pico and I am trying to build Air Station which is collecting data from PSMA003 sensor (PM 1.0, 2.5 & 10) as well as BME680 sensor (temp, hum, gas, press). Sensors works fine and I can display them on LCD or Thonny Shell. I also manage to stream sensor data over WiFi and to be honest that all what I was able to do…
I want keep sensors data in external MySQL DB for further analisys etc. but I have completetly no idea what to do and how sent data from sensors to MySQL DB.
I was trying to find some demo code or something but unfortunatelly it seems to be not so popular way. I have no idea even what MQTT is so as you can see I am a little bit “blind”.
I am coding in MicroPython using Thonny.
Thank you for your reply.
On the Pico W I gather you have been able to collect your sensor data. For my weather station I have date/time/temp/humidity/pressure. This is all sent to the server in a single urequests line. I have added try/except to handle a wifi or internet failure. I have the code in a loop which takes a measurement every minute on the minute. Let me know if there is anything you do not understand in the code snippet.
r = urequests.get(url = 'https://www.myserver.com/iot.php?date='+rdate+'&time='+rtime+'&temp='+tempC+'&humidity='+humidity+'&pressure='+pressure).text
print("urequest send data error")
I have looked at MQTT and it sounds appealing. But I have a standard VentraIP webserver and I have never been able to work out how to install an MQTT broker. Can anyone help? Looking at projects everyone seems to use public hosts but I want to store data on my own server where I can archive it, analyse it, and display data on a web page.
For my application I need to have data stored on a web hosting site. If it was on a home network I would have to open ports to access the data. I know from previous experience this is a security nightmare with swarms of hackers scanning ports looking for a vulnerability.
The MQTT broker spec seems to imply that once all clients have received subscribed data then that data is deleted. I guess an SQL client whose role is to store all data is feasible and may be a solution.
I am inclined to think that it is not possible to install an MQTT broker on a hosted web site. It has to listen on ports which may not be technically feasible. Certainly in cPanel there is no mention of installing MQTT. Perhaps it will become part of Apache in the future. Or perhaps web hosting companies will start offering Raspberry Pi’s.
So for me it is back to HTTP and it does have some advantages. I do not have to install anything. The single line of python urequests is easy to implement and for data transmission say every minute, very efficient.