Jacob George asked 6 years ago
Is there an option/lesson/module where the esp32 can collect data and link it to a database or even Google Spreadsheets, and use SQL to insert/retrieve/modify the data
Rui Santos Staff answered 6 years ago
Hello Jacob, I have a tutorial about Publish Sensor Readings to Google Sheets with ESP32/ESP8266.
The ESP can’t insert data direclty on an SQL database, but you can make a HTTP/MQTT request to publish those readings to an app (PHP/Node-RED/NodeJS) that can access a database…
Rui Santos Staff answered 6 years ago
The ESP can insert data in an SQL database, but you need an app that does it for you… For example where’s the database stored? If that server has PHP, Python, NodeJS or something like that, you can create a simple app to receie ESP requests and actually insert the data in SQL database.
Jacob George answered 6 years ago
New school year…2019. Can you point me to anything that will help me create a ‘simple app’ that receive ESP requests and insert the data into the database using SQL? I have had a look at the link you provided earlier – using google spreadsheets and IFTTT. I would like to find out more about the SQL elements.
Rui Santos Staff answered 6 years ago
Hello again, as I said in my previous comment… The ESP can’t insert data directly on an SQL database, but you can make a HTTP/MQTT request to publish those readings to an app (PHP/Node-RED/NodeJS) that can access a database…
IFTTT will not publish sensor readings in an SQL database… But it’s a method that I recommend to create a simple data logging excel spreadsheet.
So, to answer your question here’s what I would do:
- Install SQLite database and Python 3 on a Raspberry Pi
- Create a Flask web server with Python
- Create a Python script (integrated with the Flash web server) that receives HTTP requests
- Python inserts that data in the SQLite database
- The ESP8266 publishes sensor readings via HTTP and requests data
- Your Python Flask web server acts as a layer to make the connection between the ESP and the SQLite database
Instead of using Python, you can use: PHP, Node.js, Node-RED platform, etc… Here’s a few tutorials that I’ve posted that might help:
- SQLite with Node-RED and Raspberry Pi: https://randomnerdtutorials.com/sqlite-with-node-red-and-raspberry-pi/
- Raspberry Pi Publishing MQTT Messages to ESP8266: https://randomnerdtutorials.com/raspberry-pi-publishing-mqtt-messages-to-esp8266/
- ESP8266 Publishing DHT22 Readings with MQTT to Raspberry Pi: https://randomnerdtutorials.com/esp8266-publishing-dht22-readings-with-mqtt-to-raspberry-pi/