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

oujw3lakoqzf8doi7pwfixn98ki.png

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

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

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

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

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

php-скрипт для помещения данных в базу MySQL 
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 . "
" . $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 
#include 

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 
#include 
#include 

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 
#include 
#include 

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 
#include 
#include 

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 
#include 
#include 

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 
#include 
#include 

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                // Use this for WiFi instead of Ethernet.h
#include 
#include 

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 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 

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.

© Habrahabr.ru