Pico W writing into MySQL db?

Jon13024 · 11 September 2022 09:59

Hi Guys,
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.

Thoughts please?
Jon

Farhad164597 · 11 September 2022 13:36

Hi Jon,
You are not hosting the DB on the Pico. Are you?
Here is a link that may be a clue μSQLite module - MicroPython Forum (Archive)

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.

Jon13024 · 12 September 2022 00:38

Hi, thanks for the reply.
I am definately not wanting to host a SQL db on a PIco.

The Pico W is simply a device to capture a reading periodically and hopefully write that data into a SQL db thats hosted (on a virtual machine) on my NAS.

If I can get the data into the dB, I can configure Homeassistant to do everything else.

Id rather not setup other softwares just to handle this one bit of data as an ADC for the ESP32 becomes more attractive then.

Thanks
Jon

Fractal · 15 September 2022 07:47

Hi Jon

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

import urequests
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.

Jon13024 · 28 September 2022 09:28

Thanks for the replies - all good options.

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!

Jon

Med215682 · 3 November 2022 20:58

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.

Gramo · 3 November 2022 22:54

@Med215682 If the outcome of this topic (using MQTT) is not useful, then FYI I’m not aware of a functional MicroPython OBDC driver for SQL Server.

That doesn’t seem like the right way to go about it anyhow; SQL servers are best placed behind an API that allows you to set/get data via an auth key or the like.

Flask would be a super quick way to do that.

If you need help with that then it would be best to create a new topic as it may dilute the conversation/resolution of this one.

Fractal · 3 November 2022 23:54

Hi Med215682
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?”.

Med215682 · 4 November 2022 00:18

Hi Fractal,
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.

Fractal · 4 November 2022 01:27

Hi Med215682

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.

import urequests
try:
    r = urequests.get(url = 'https://www.myserver.com/iot.php?date='+rdate+'&time='+rtime+'&temp='+tempC+'&humidity='+humidity+'&pressure='+pressure).text
    print(r)
except:
    print("urequest send data error")

Fractal · 4 November 2022 01:50

Hi Med215682

On the server end this is what the iot.php looks like using my weather example. I assume you are able to setup MySQL etc.

<?php
if ($_SERVER["REQUEST_METHOD"] == "GET") {
	$date = $_GET['date'];
	$time = $_GET['time'];
	$temp = $_GET['temp'];
	$humidity = $_GET['humidity'];
	$pressure = $_GET['pressure'];
	$date = $date . ' ' . $time;
	
	$conn = mysqli_connect($servername, $username, $password, $dbname);
	if (!$conn) {
  		die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "INSERT INTO myreadings
	VALUES ('$date', '$temp', '$humidity', $pressure)";
	if (mysqli_query($conn, $sql)) {
  		echo "New record created successfully";
	} else {
  		echo "Error: " . $sql . "<br>" . mysqli_error($conn);
	}

	mysqli_close($conn);
	}
}
?>

Jon13024 · 4 November 2022 02:34

I ended up not using SQL in the end. I opted to use an MQTT broker on my HomeAssistant instance and use an MQTT Client on the Pico W. Works for my needs and seemed to be less complicated.

R
J

Fractal · 4 November 2022 04:34

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.

Liam120347 · 4 November 2022 06:56

Hi Fractal,

I run MosquittoMQTT Broker through HomeAssistant: addons/DOCS.md at master · home-assistant/addons · GitHub
It looks like there are some other additions you can add to save data into a SQL database.

Med215682 · 4 November 2022 18:39

Thank you very much Fractal! I am trying to adapt your code…

Pi Pico W

readingStation = str(1)
readingDate = "2022-10-10"
readingTime = "10:10:10"
readingGas = str(111)
readingPM1 = str(222)
readingPM25 = str(333)
readingPM10 = str(444)
readingTemperature = str(555.555)
readingPressure = str(666.666)
readingHumidity = str(777.777)
readingAltitude = str(888.888)

try:
    r = urequests.get(url = 'http://--link--/iosr.php?&station='+readingStation+'&date='+readingDate+'&time='+readingTime+'&gas='+readingGas+'&pm1='+readingPM1+'&pm25='+readingPM25+'&pm10='+readingPM10+'&temperature='+readingTemperature+'&pressure='+readingPressure+'&humidity='+readingHumidity+'&altitude='+readingAltitude).text
    print(r)
    print(r.status_code)
    r.close()
except:
    print("urequest send data error")

PHP file iosr.php
…

<?php
if ($_SERVER["REQUEST_METHOD"] == "GET") {
	$readingStation = $_GET['station'];
	$readingDate = $_GET['date'];
	$readingTime = $_GET['time'];
	$readingGas = $_GET['gas'];
	$readingPM1 = $_GET['pm1'];
	$readingPM25 = $_GET['pm25'];
	$readingPM10 = $_GET['pm10'];
	$readingTemperature = $_GET['temperature'];
	$readingPressure = $_GET['pressure'];
	$readingHumidity = $_GET['humidity'];
	$readingAltitude = $_GET['altitude'];

	//====================================================================
	//	connect to the database
	//====================================================================

    $server = "---";
    $user_name = "---";
    $pass_word = "---";
    $database = "---";

	// Create connection
	$conn = new mysqli($server, $user_name, $pass_word, $database);
	// Check connection
	if ($conn->connect_error) {
    	die("Connection failed: " . $conn->connect_error);
	}
	
	//====================================================================	
	//  inserting readings into new record
	//====================================================================

	$sql = "INSERT INTO readingdata
	VALUES ('$readingStation', '$readingDate', '$readingTime', '$readingGas', '$readingPM1', '$readingPM25', '$readingPM10', '$readingTemperature', '$readingPressure', '$readingHumidity', '$readingAltitude',)";
	if (mysqli_query($conn, $sql)) {
  		echo "New record created successfully";
	} else {
  		echo "Error: " . $sql . "<br>" . mysqli_error($conn);
	}

	mysqli_close($conn);
	}
}

But unfortunatelly it is not working at all… where I am making error ???

Med215682 · 4 November 2022 19:38

I am getting error “urequest send data error” in Thonny…

I found some little errors in PHP file so I change them to:

<?php
if ($_SERVER["REQUEST_METHOD"] == "GET") {
	$readingStation = $_GET['station'];
	$readingDate = $_GET['date'];
	$readingTime = $_GET['time'];
	$readingGas = $_GET['gas'];
	$readingPM1 = $_GET['pm1'];
	$readingPM25 = $_GET['pm25'];
	$readingPM10 = $_GET['pm10'];
	$readingTemperature = $_GET['temperature'];
	$readingPressure = $_GET['pressure'];
	$readingHumidity = $_GET['humidity'];
	$readingAltitude = $_GET['altitude'];

	//====================================================================
	//	connect to the database
	//====================================================================

	$server = "---";
    $user_name = "---";
    $pass_word = "---";
    $database = "---";

	// Create connection
	$conn = new mysqli($server, $user_name, $pass_word, $database);
	// Check connection
	if ($conn->connect_error) {
    	die("Connection failed: " . $conn->connect_error);
	}
	
	//====================================================================	
	//  inserting readings into new record
	//====================================================================

	$sql = "INSERT INTO readingdata (readingStation, readingDate, readingTime, readingGas, readingPM1, readingPM25, readingPM10, readingTemperature, readingPressure, readingHumidity, readingAltitude) VALUES ('$readingStation', '$readingDate', '$readingTime', '$readingGas', '$readingPM1', '$readingPM25', '$readingPM10', '$readingTemperature', '$readingPressure', '$readingHumidity', '$readingAltitude')";
	if (mysqli_query($conn, $sql)) {
  		echo "New record created successfully";
	} else {
  		echo "Error: " . $sql . "<br>" . mysqli_error($conn);
	}

	mysqli_close($conn);
	}
}

Med215682 · 4 November 2022 19:46

SORTED it is working THANK YOU Fractal !!!

Fractal · 5 November 2022 03:26

Hi Med215682

I am so pleased it worked for you.

The urequests send data error in python usually means your pico is not connected to wifi, the internet is down or the path does not exist.

At the server end I have found if you examine the error_log file it usually gives a good explanation of the PHP error.

With HTTP GET the url is in plain text. While big companies are not fussed about security I always add a function to stop SQL injection used by hackers. It works like this.

<?php
function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

if ($_SERVER["REQUEST_METHOD"] == "GET") {
	$readingStation = test_input($_GET['station']);
etc etc

Fractal · 5 November 2022 04:29

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.

Brian250100 · 9 June 2023 00:36

Fractal,

Can you post your entire code you upload to your pico w to be able to send the data to a database?

Topic		Replies	Views
Connecting Pico W to Home Assistant Support	7	2485	24 November 2022
PiicoDev BME280 with Pi Pico W - mqtt	11	721	8 October 2022
Pi, Pico, ESP32 or Arduino	6	466	26 August 2023
Pi Pico W watering system Support	10	303	10 September 2022
Get data from raspberrypi to home server Finding Parts	2	143	24 October 2023

Pico W writing into MySQL db?

Related Topics