Сохранение данных для ESP32/Arduino в удаленной базе MySQL и не только

Любой любительский проект имеет дело с теми или иными данными, которые могут модифицироваться, генерироваться и, соответственно, требуют некоего хранения. В этой статье мы попробуем обзорно рассмотреть основные способы, с помощью которых можно организовать хранение данных для любительских проектов.

Одним из самых известных способов является отправка данных на некий сервер, где соответствующий веб-скрипт получает их и помещает в базу. Например, скрипт на основе PHP.

Показанный ниже способ является частью более широкого решения, которое используется для сохранения на сервере данных с датчиков температуры, влажности и давления. В оригинале база данных является всего лишь частью системы, где сохранённые данные затем могут быть отображены по запросу и являются доступными через браузер.

В этой статье мне хотелось больше остановиться на способах работы с базой данных, чем с фронтендом, и поэтому этот момент я оставлю «за скобками». Однако, тем кто-то заинтересовался темой, оставляю ссылку для более подробного ознакомления.<

Источник картинки

php-скрипт для помещения данных в базу MySQL

<?php  /*   Rui Santos   Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/     Permission is hereby granted, free of charge, to any person obtaining a copy   of this software and associated documentation files.     The above copyright notice and this permission notice shall be included in all   copies or substantial portions of the Software. */  $servername = "localhost";  // REPLACE with your Database name $dbname = "REPLACE_WITH_YOUR_DATABASE_NAME"; // REPLACE with Database user $username = "REPLACE_WITH_YOUR_USERNAME"; // REPLACE with Database user password $password = "REPLACE_WITH_YOUR_PASSWORD";  // Keep this API Key value to be compatible with the ESP32 code provided in the project page. // If you change this value, the ESP32 sketch needs to match $api_key_value = "tPmAT5Ab3j7F9";  $api_key= $sensor = $location = $value1 = $value2 = $value3 = "";  if ($_SERVER["REQUEST_METHOD"] == "POST") {  $api_key = test_input($_POST["api_key"]);  if($api_key == $api_key_value) {      $sensor = test_input($_POST["sensor"]);      $location = test_input($_POST["location"]);      $value1 = test_input($_POST["value1"]);      $value2 = test_input($_POST["value2"]);      $value3 = test_input($_POST["value3"]);            // Create connection      $conn = new mysqli($servername, $username, $password, $dbname);      // Check connection      if ($conn->connect_error) {          die("Connection failed: " . $conn->connect_error);      }            $sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3)      VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";            if ($conn->query($sql) === TRUE) {          echo "New record created successfully";      }      else {          echo "Error: " . $sql . "<br>" . $conn->error;      }           $conn->close();  }  else {      echo "Wrong API Key provided.";  }  } else {  echo "No data posted with HTTP POST."; }  function test_input($data) {  $data = trim($data);  $data = stripslashes($data);  $data = htmlspecialchars($data);  return $data; }

Перед использованием этого скрипта в рамках нашего проекта требуется предварительно:

1. Создать базу данных на сервере.
2. После чего ввести её реквизиты в соответствующие строки скрипта:

   // REPLACE with your Database name $dbname = "REPLACE_WITH_YOUR_DATABASE_NAME"; // REPLACE with Database user $username = "REPLACE_WITH_YOUR_USERNAME"; // REPLACE with Database user password $password = "REPLACE_WITH_YOUR_PASSWORD";

Примечание: этот способ позволяет только помещать данные в базу. Для извлечения их из базы – требуется либо дописать этот, либо найти соответствующий готовый скрипт.

Казалось бы, вот он способ для работы с базой! Однако, существует гораздо более красивое решение, которое позволяет микроконтроллеру напрямую обращаться к базе данных на сервере.

Для этого следует использовать библиотеку MySQL_Connector_Arduino.

Библиотека позволяет вашему микроконтроллеру напрямую работать с базой, что даёт возможность сохранять собранные данные, извлекать их и использовать для определённых событий в рамках вашего скетча.

Это позволит вам использовать MySQL-сервер как находящийся в интернете, так и локального типа (позволяющий хранить ваши данные локально, даже без подключения к сети интернет).

Однако следует сделать оговорку: я не считаю, что такой способ прямого подключения (без серверного скрипта) является наиболее предпочтительным, — у серверного варианта могут быть свои плюсы, среди которых можно назвать его большую гибкость, так как к нему могут обращаться абсолютно разные устройства, на основе разных операционных систем и т. д.

Подытоживая — я хотел сказать, что если вы работаете предпочтительно только с Arduino или esp32 и «лень — ваше второе имя» (как и у меня, кстати, хе-хе), то можно просто использовать библиотеку коннектора — это будет более элегантным решением в данном случае.

Чтобы проверить работу с базой и с использованием этой библиотеки, можно воспользоваться рядом вариантов.

Просто подключение к базе — этот вариант можно назвать самым базовым (если он заработает, то значит, и дальше у вас всё будет хорошо):

Базовый скетч

#include <Ethernet.h> #include <MySQL_Connection.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  EthernetClient client; MySQL_Connection conn((Client *)&client);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);  // You would add your code here to run a query once on startup.   }   else  Serial.println("Connection failed.");   conn.close(); }  void loop() { }

Следующий пример показывает, как подключиться к серверу MySQL, используя вместо адреса — имя хоста:

Подключение по имени хоста

#include <Ethernet.h> #include <MySQL_Connection.h> #include <Dns.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  char hostname[] = "www.google.com"; // change to your server's hostname/URL char user[] = "root";            // MySQL user login username char password[] = "secret";      // MySQL user login password  IPAddress server_ip; EthernetClient client; MySQL_Connection conn((Client *)&client); DNSClient dns_client;   // DNS instance  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   // Begin DNS lookup   dns_client.begin(Ethernet.dnsServerIP());   dns_client.getHostByName(hostname, server_ip);   Serial.println(server_ip);   // End DNS lookup   Serial.println("Connecting...");   if (conn.connect(server_ip, 3306, user, password)) {  delay(1000);  // You would add your code here to run a query once on startup.   }   else  Serial.println("Connection failed.");   conn.close(); }  void loop() { }

В качестве базового примера для вставки данных в базу можно воспользоваться следующим ходом. Он вставляет одну строку в таблицу MySQL каждые 2 секунды:

Базовая вставка

#include <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  // Sample query char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";  EthernetClient client; MySQL_Connection conn((Client *)&client);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);   }   else  Serial.println("Connection failed."); }  void loop() {   delay(2000);    Serial.println("Recording data.");    // Initiate the query class instance   MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);   // Execute the query   cur_mem->execute(INSERT_SQL);   // Note: since there are no results, we do not need to read any data   // Deleting the cursor also frees up memory used   delete cur_mem; }

Ниже показано использование переменных для формирования строки, для вставки в таблицу:

Сложная вставка

#include <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  // Sample query char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (message, sensor_num, value) VALUES ('%s',%d,%s)"; char query[128]; char temperature[10];  EthernetClient client; MySQL_Connection conn((Client *)&client);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);  // Initiate the query class instance  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);  // Save  dtostrf(50.125, 1, 1, temperature);  sprintf(query, INSERT_DATA, "test sensor", 24, temperature);  // Execute the query  cur_mem->execute(query);  // Note: since there are no results, we do not need to read any data  // Deleting the cursor also frees up memory used  delete cur_mem;  Serial.println("Data recorded.");   }   else  Serial.println("Connection failed.");   conn.close(); }  void loop() { }

Далее посмотрим на то, как можно «наосуществлять» SELECT-запросы, то есть запросы, которые возвращают определённые результаты. Пример ниже показывает, как можно использовать запрос SELECT для извлечения строки из таблицы и сохранить его результат в переменной. Чтобы этот пример заработал, вам необходимо загрузить и установить образец «World»-базы данных сайта документации Oracle MySQL:

Базовый SELECT-запрос

#include <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  // Sample query char query[] = "SELECT population FROM world.city WHERE name = 'New York'";  EthernetClient client; MySQL_Connection conn((Client *)&client); // Create an instance of the cursor passing in the connection MySQL_Cursor cur = MySQL_Cursor(&conn);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);   }   else  Serial.println("Connection failed."); }   void loop() {   row_values *row = NULL;   long head_count = 0;    delay(1000);    Serial.println("1) Demonstrating using a cursor dynamically allocated.");   // Initiate the query class instance   MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);   // Execute the query   cur_mem->execute(query);   // Fetch the columns (required) but we don't use them.   column_names *columns = cur_mem->get_columns();    // Read the row (we are only expecting the one)   do {  row = cur_mem->get_next_row();  if (row != NULL) {    head_count = atol(row->values[0]);  }   } while (row != NULL);   // Deleting the cursor also frees up memory used   delete cur_mem;    // Show the result   Serial.print("  NYC pop = ");   Serial.println(head_count);    delay(500);    Serial.println("2) Demonstrating using a local, global cursor.");   // Execute the query   cur.execute(query);   // Fetch the columns (required) but we don't use them.   cur.get_columns();   // Read the row (we are only expecting the one)   do {  row = cur.get_next_row();  if (row != NULL) {    head_count = atol(row->values[0]);  }   } while (row != NULL);   // Now we close the cursor to free any memory   cur.close();    // Show the result but this time do some math on it   Serial.print("  NYC pop = ");   Serial.println(head_count);   Serial.print("  NYC pop increased by 12 = ");   Serial.println(head_count+12); }

Для осуществления сложного выбора из таблицы можно воспользоваться следующим примером, который позволяет использовать концепцию заполнителей:

Сложный запрос

#include <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  // Sample query // // Notice the "%lu" - that's a placeholder for the parameter we will // supply. See sprintf() documentation for more formatting specifier // options const char QUERY_POP[] = "SELECT name, population FROM world.city WHERE population > %lu ORDER BY population DESC;"; char query[128];  EthernetClient client; MySQL_Connection conn((Client *)&client);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect   Ethernet.begin(mac_addr);   Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);   }   else  Serial.println("Connection failed."); }   void loop() {   delay(1000);    Serial.println("> Running SELECT with dynamically supplied parameter");    // Initiate the query class instance   MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);   // Supply the parameter for the query   // Here we use the QUERY_POP as the format string and query as the   // destination. This uses twice the memory so another option would be   // to allocate one buffer for all formatted queries or allocate the   // memory as needed (just make sure you allocate enough memory and   // free it when you're done!).   sprintf(query, QUERY_POP, 9000000);   // Execute the query   cur_mem->execute(query);   // Fetch the columns and print them   column_names *cols = cur_mem->get_columns();   for (int f = 0; f < cols->num_fields; f++) {  Serial.print(cols->fields[f]->name);  if (f < cols->num_fields-1) {    Serial.print(',');  }   }   Serial.println();   // Read the rows and print them   row_values *row = NULL;   do {  row = cur_mem->get_next_row();  if (row != NULL) {    for (int f = 0; f < cols->num_fields; f++) {      Serial.print(row->values[f]);      if (f < cols->num_fields-1) {        Serial.print(',');      }    }    free_row_buffer();    Serial.println();  }   } while (row != NULL);   free_columns_buffer();   // Deleting the cursor also frees up memory used   delete cur_mem; }

Как вы могли увидеть выше, все примеры построены на использовании сети Ethernet (например, для случая, если в качестве вашего микроконтроллера вы используете плату Arduino Ethernet), однако, что же делать, если в нашем случае есть esp32, которая использует для подключения сеть wi-fi?

Для этого во все приведённые выше примеры следует внести изменения, которые позволят плате подключаться к базе с использованием беспроводного подключения:

Код wi-fi-подключения к базе данных MySQL

#include <WiFi.h>               // Use this for WiFi instead of Ethernet.h #include <MySQL_Connection.h> #include <MySQL_Cursor.h>  byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  IPAddress server_addr(10,0,1,35);  // IP of the MySQL *server* here char user[] = "root";           // MySQL user login username char password[] = "secret";     // MySQL user login password  // WiFi card example char ssid[] = "horse_pen"; // your SSID char pass[] = "noname";    // your SSID Password  WiFiClient client;         // Use this for WiFi instead of EthernetClient MySQL_Connection conn((Client *)&client);  void setup() {   Serial.begin(115200);   while (!Serial); // wait for serial port to connect. Needed for Leonardo only    // Begin WiFi section   int status = WiFi.begin(ssid, pass);   if ( status != WL_CONNECTED) {  Serial.println("Couldn't get a wifi connection");  while(true);   }   // print out info about the connection:   else {  Serial.println("Connected to network");  IPAddress ip = WiFi.localIP();  Serial.print("My IP address is: ");  Serial.println(ip);   }   // End WiFi section    Serial.println("Connecting...");   if (conn.connect(server_addr, 3306, user, password)) {  delay(1000);   }   else  Serial.println("Connection failed.");   conn.close(); }  void loop() { }

Все приведённые выше примеры могут использоваться для достаточно серьёзных задач, но как же быть, если наши задачи достаточно скромные и использование «микроскопа для забивания гвоздей» нам не требуется?

Для этого мы просто можем воспользоваться энергонезависимой памятью микроконтроллера. Это возможно осуществить с использованием библиотеки Preferences. Она входит в комплект встроенных библиотек для esp32, поэтому её спецаильно скачивать её не нужно.

Её можно использовать, чтобы:

• запомнить последнее состояние переменной;
• сохранить настройки;
• сохранить, сколько раз устройство было включено;
• закрепить какие-то другие данные, которые необходимо хранить на постоянной основе.

Данные, сохранённые с применением этой библиотеки, имеют такую структуру:

namespace {   key:value }

Можно хранить разные ключи в одном и том же пространстве имён, например:

namespace {   key1: value1   key2: value2 }

Также можно создать ряд пространств имён с одним и тем же ключом (но каждый ключ — со своим значением):

namespace1{   key:value1 } namespace2{   key:value2 }

Использование библиотеки стандартное — сначала она включается в скетч:

#include <Preferences.h>

Затем создаётся объект:

Preferences preferences;

И у объекта вызывается метод, который создаёт и открывает доступ к именованному пространству (внимание: длина имени пространства ограничена 15 символами). Аргумент false означает, что мы будем использовать это пространство для чтения/записи. Если поставить true — это будет означать использование только для чтения:

preferences.begin("my-app", false); 

Для работы с библиотекой можно использовать ряд методов.

Поместить значение (ниже показаны разные методы — выберите свой в зависимости от типа сохраняемой переменной):

putChar(const char* key, int8_t value) // Char putUChar(const char* key, int8_t value) // Unsigned Char putShort(const char* key, int16_t value) // Short putUShort(const char* key, uint16_t value) // Unsigned Short putInt(const char* key, int32_t value) // Int putUInt(const char* key, uint32_t value) // Unsigned Int putLong(const char* key, int32_t value) // Long     putULong(const char* key, uint32_t value) // Unsigned Long putLong64(const char* key, int64_t value) // Long64 putULong64(const char* key, uint64_t value) // Unsigned Long64 putFloat(const char* key, const float_t value) // Float putDouble(const char* key, const double_t value) // Double     putBool(const char* key, const bool value) // Bool putString(const char* key, const String value) // String putBytes(const char* key, const void* value, size_t len) // Bytes 

Аналогично показанному выше выберите свой метод (в зависимости от типа переменной) для получения сохранённой ранее переменной:

getChar(const char* key, const int8_t defaultValue) // Char getUChar(const char* key, const uint8_t defaultValue)  // Unsigned Char getShort(const char* key, const int16_t defaultValue) // Short getUShort(const char* key, const uint16_t defaultValue) // Unsigned Short getInt(const char* key, const int32_t defaultValue) // Int getUInt(const char* key, const uint32_t defaultValue) // Unsigned Int getLong(const char* key, const int32_t defaultValue) // Long getULong(const char* key, const uint32_t defaultValue) // Unsigned Long getLong64(const char* key, const int64_t defaultValue)  // Long64 gettULong64(const char* key, const uint64_t defaultValue) // Unsigned Long64 getFloat(const char* key, const float_t defaultValue) // Float getDouble(const char* key, const double_t defaultValue) // Double getBool(const char* key, const bool defaultValue) // Bool getString(const char* key, const String defaultValue) // String getString(const char* key, char* value, const size_t maxLen)  // String getBytes(const char* key, void * buf, size_t maxLen) // Bytes 

Очистка всех значений из именованного пространства (само пространство не удаляется при этом):

preferences.clear();

Удаление ключа:

preferences.remove(key);

Закрытие открытого именованного пространства (после окончания работы с ним):

preferences.end();

Если вам необходимо полностью удалить пространство/ства имён (например, вы забыли, как называются старые пространства из предыдущих скетчей, или в целом произошло переполнение), надо запустить следующий скетч, который полностью переформатирует область энергонезависимой памяти, отведённой под Preferences:

#include <nvs_flash.h>  void setup() {   nvs_flash_erase(); // erase the NVS partition and...   nvs_flash_init(); // initialize the NVS partition.   while(true); }  void loop() {  }

Подытоживая статью, хочется отметить, что я не ставил перед собой целью рассмотреть абсолютно все возможные варианты сохранения данных. Так как это вышло бы далеко за пределы этой статьи и сюда надо было бы включить ещё и сохранение на флеш-карту, передачу по сотовому каналу с использованием sim-карты и чуть ли не «выцарапывание этих данных на восковом барабане» — так как при желании способ сохранения может быть абсолютно любым и ограничивается только вашей фантазией. Я же постарался рассмотреть наиболее применимые и гибкие способы, которые позволят вам получить новые возможности по работе с вашими массивами данных.

Успехов всем в творчестве!

---

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

— 15% на все тарифы VDS (кроме тарифа Прогрев) — HABRFIRSTVDS.