单片机菜鸟哥

单片机菜鸟哥

关注
发信
关注(28)粉丝(399)

ESP32-CAM开发板 使用 sqlite3 数据库存储数据记录

1、前言

主要学习

以及如何使用sqlite来存储我们的业务数据。

该库支持通过 ESP32 SoC 从 SPIFFS 或 SD 卡访问 SQLite 数据库文件。但是对于我们来说SD卡可能更加合适，毕竟空间大。

2、esp32_arduino_sqlite3_lib 库

Arduino IDE -> 工具 -> 管理库 -> 搜索 Sqlite3Esp32-> 安装最新版本
ESP32-CAM开发板 使用 sqlite3 数据库存储数据记录-LMLPHP
除了 Arduino 和 ESP32 核心 SDK 之外，没有依赖项。Sqlite3 代码包含在库中。

3、硬件说明

这里直接选择esp32-cam，因为它直接有SD卡槽，插入一张sd卡即可，完美！！！！如果是其他esp32板子，就得自己连接一下SD卡连线。

4、API说明

4.1 sqlite3_initialize —— 初始化Sqlite数据库引擎

SQLite 数据库引擎的初始化函数。它在使用 SQLite 数据库之前必须被调用，以确保数据库引擎已被正确地初始化。该函数将创建或打开 SQLite 数据库的内部数据结构，设置全局变量和其他必要的初始化步骤。

4.2 sqlite3_open —— 打开或创建一个 SQLite 数据库文件

sqlite3_open 是 SQLite 数据库引擎的一个函数，用于打开或创建一个 SQLite 数据库文件。它的函数原型如下：

int sqlite3_open( 
 const char *filename,   /* 数据库文件名 */ 
  sqlite3 **ppDb          /* 指向指针的指针，用于存储打开的数据库句柄 */
  );

filename 参数是数据库文件的名称，如果该文件不存在，则会创建一个新的数据库文件。如果 filename 参数为 “:memory:”，则表示创建一个内存数据库。ppDb 参数是一个指向指针的指针，用于存储打开的数据库句柄。如果打开数据库成功，则会将句柄存储在该指针指向的内存中，如果打开失败，则会把指针设置为 NULL。

例如，以下代码段展示了如何使用 sqlite3_open 函数打开一个名为 test.db 的数据库文件：

sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) { 
 // 打开数据库失败
 } else {  
 // 打开数据库成功，可以使用 db 句柄进行后续操作
 }

在使用完数据库之后，需要调用 sqlite3_close 函数关闭数据库。

4.3 sqlite3_exec —— 执行 SQL 命令

它的函数原型如下：

int sqlite3_exec(  
sqlite3* db,                    /* 数据库句柄 */  
const char* sql,                /* SQL 命令 */  
int (*callback)(void*,int,char**,char**),  /* 回调函数 */  
void *data,                     /* 回调函数的上下文参数 */  
char **errmsg                   /* 错误信息，如果有错误发生 */
);
  • db 参数是 SQLite 数据库句柄
  • sql 参数是需要执行的 SQL 命令
  • callback 参数是回调函数指针
  • data 参数是回调函数的上下文参数
  • errmsg 参数是一个指向字符指针的指针，用于存储错误信息（如果有错误发生）。

当执行 SQL 命令时，sqlite3_exec 函数会多次调用回调函数，每次调用传递一个结果行的数据。回调函数的函数原型为：

int callback(void* data, int argc, char** argv, char** colName);

其中，data 参数为回调函数的上下文参数，argc 参数为结果行中的列数，argv 参数为指向结果行中每个列的指针数组，colName 参数为指向结果行中每个列的名称的指针数组。

例如，以下代码段展示了如何使用 sqlite3_exec 函数执行一条 SELECT 命令：

sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {  
// 打开数据库失败
} else {  
    char *errmsg;  
    // SELECT 命令的回调函数  
    int callback(void *data, int argc, char **argv, char **colName) {    for (int i = 0; i < argc; i++) {      
    printf("%s = %s\n", colName[i], argv[i] ? argv[i] : "NULL");    
    }    
    return 0;  
}  
    // 执行 SELECT 命令  
    rc = sqlite3_exec(db, "SELECT * FROM mytable", callback, 0, &errmsg);  
    if (rc != SQLITE_OK) {    
    printf("SELECT failed: %s\n", errmsg);    
    sqlite3_free(errmsg);  
    }  
    sqlite3_close(db);
}

在使用完数据库之后，需要调用 sqlite3_close 函数关闭数据库。

4.4 sqlite3_free —— 释放内存

用于释放先前通过 sqlite3_malloc 或 sqlite3_realloc 分配的内存。它的原型为：

void sqlite3_free(void*);

其中，参数是一个指向要释放的内存块的指针。

使用 sqlite3_free 可以避免内存泄漏的问题，因为它会将之前分配的内存块标记为可用，以便后续的内存分配可以复用这些空间。但是需要注意的是，必须使用相同的内存分配函数（即 sqlite3_malloc 或 sqlite3_realloc）来分配和释放内存块，否则可能会导致内存错误。

4.5 sqlite3_close —— 关闭先前打开的数据库连接

用于关闭先前打开的数据库连接。它的原型为：

int sqlite3_close(sqlite3*);

其中，参数是一个指向已打开的数据库连接的指针。

使用 sqlite3_close 可以释放数据库连接占用的资源，包括内存和文件句柄等。在关闭数据库连接之前，应该先释放所有相关的内存和资源，例如关闭所有已经打开的数据库句柄和清理所有的 prepared statements，以避免资源泄漏。

需要注意的是，当一个数据库连接被关闭后，与该连接相关的所有数据库句柄和 prepared statements 都会变成无效。因此，在调用 sqlite3_close 之后，不应该再使用任何与该连接相关的资源。

4.6 sqlite3_errmsg —— 用于获取最近一次 SQLite API 调用产生的错误信息

它的原型为：

const char *sqlite3_errmsg(sqlite3*);

其中，参数是一个指向已打开的数据库连接的指针。如果最近一次的 SQLite API 调用没有产生错误，那么 sqlite3_errmsg 返回一个空字符串。

当 SQLite API 调用出现错误时，sqlite3_errmsg 可以用来获取错误信息，以便排查问题。例如，假设在执行 SQL 查询时出现错误，可以使用 sqlite3_errmsg 来获取错误信息并输出到控制台：

sqlite3 *db;
sqlite3_open("example.db", &db);
sqlite3_exec(db, "SELECT * FROM users WHERE id = 0;", NULL, NULL, NULL);
fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);

4.7 sqlite3_errcode —— 获取最近一次 SQLite API 调用产生的标准错误码

它的原型为：

int sqlite3_errcode(sqlite3*);

其中，参数是一个指向已打开的数据库连接的指针。

sqlite3_errcode 返回的错误码是一个整数，可以与 SQLite 的错误码宏比较，以判断错误的类型。例如，如果 sqlite3_errcode 返回 SQLITE_CONSTRAINT_UNIQUE，那么表示最近一次 SQLite API 调用遇到了一个唯一性约束错误。

需要注意的是，sqlite3_errcode 返回的错误码是一个标准错误码，与 sqlite3_extended_errcode 返回的扩展错误码不同。如果想要同时获取标准错误码和扩展错误码，可以使用 sqlite3_errcode 和 sqlite3_extended_errcode 配合使用。同时需要注意，sqlite3_errcode 只能返回最近一次 SQLite API 调用的错误码，如果想要获取之前的错误码，需要使用 sqlite3_extended_errcode。

4.8 sqlite3_extended_errcode —— 获取最近一次 SQLite API 调用产生的扩展错误码

与 sqlite3_errcode 不同的是，sqlite3_extended_errcode 提供了更加详细的错误信息，可以用于更好地排查问题。它的原型为：

int sqlite3_extended_errcode(sqlite3*);

其中，参数是一个指向已打开的数据库连接的指针。

sqlite3_extended_errcode 返回的错误码是一个整数，可以与 SQLite 的错误码宏比较，以判断错误的类型。例如，如果 sqlite3_extended_errcode 返回 SQLITE_CONSTRAINT_FOREIGNKEY，那么表示最近一次 SQLite API 调用遇到了一个外键约束错误。

需要注意的是，sqlite3_extended_errcode 返回的错误码是一个扩展错误码，与 sqlite3_errcode 返回的标准错误码不同。如果想要获取标准错误码，可以使用 sqlite3_errcode。如果想要同时获取标准错误码和扩展错误码，可以使用 sqlite3_errcode 和 sqlite3_extended_errcode 配合使用。

5、练习

接下来会通过几个练习来学习一下sqlite3的具体使用场景和用法。

5.1 利用SPIFFS运行数据库

/*
    This creates two empty databases, populates values, and retrieves them back
    from the SPIFFS file
    创建两个空数据库，用来测试简单CURD操作
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SPIFFS.h"

/* You only need to format SPIFFS the first time you run a
   test or else use the SPIFFS plugin to create a partition
   https://github.com/me-no-dev/arduino-esp32fs-plugin */
#define FORMAT_SPIFFS_IF_FAILED true

const char* data = "Callback function called";
/***
 * 回调方法，用于数据库操作工程中结果回调
 */
static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   Serial.printf("%s: ", (const char*)data);
   for (i = 0; i<argc; i++){
       Serial.printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   Serial.printf("\n");
   return 0;
}


/***
 * 打开数据库
 */
int db_open(const char *filename, sqlite3 **db) {
   Serial.print(F("DB Name:"));
   Serial.print(filename);
   Serial.println(F(""));
   if (db != NULL)
    sqlite3_close(*db);
   int rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.print(F("Can't open database: "));
       Serial.print(sqlite3_extended_errcode(*db));
       Serial.print(" ");
       // 打印错误信息
       Serial.println(sqlite3_errmsg(*db));
       return rc;
   } else {
       Serial.printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;

/***
 * 执行数据库操作语句
 */
int db_exec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   // 记录一个开始执行时间
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
    Serial.print(F("SQL error: "));
    Serial.print(sqlite3_extended_errcode(db));
    Serial.print(" ");
    Serial.println(zErrMsg);
    sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   // 计算花费时间
   Serial.print(F("Time taken:"));
   Serial.print(micros()-start);
   Serial.println(F(" us"));
   return rc;
}

void setup() {

   Serial.begin(115200);
   // 创建两个数据库对象
   sqlite3 *db1 = NULL;
   sqlite3 *db2 = NULL;
   int rc;

   // 初始化SPIFFS文件系统
   if (!SPIFFS.begin(FORMAT_SPIFFS_IF_FAILED)) {
       Serial.println("Failed to mount file system");
       return;
   }

   // 获取根目录
   File root = SPIFFS.open("/");
   if (!root) {
       Serial.println("- failed to open directory");
       return;
   }
   // 判断是否是一个文件夹
   if (!root.isDirectory()) {
       Serial.println(" - not a directory");
       return;
   }
   // 接下来打印当前所有文件
   File file = root.openNextFile();
   while (file) {
       if (file.isDirectory()) {
           Serial.print("  DIR : ");
           Serial.println(file.name());
       } else {
           Serial.print("  FILE: ");
           Serial.print(file.name());
           Serial.print("\tSIZE: ");
           Serial.println(file.size());
       }
       file = root.openNextFile();
   }

   // 移除已经存在的db文件
   SPIFFS.remove("/test1.db");
   SPIFFS.remove("/test2.db");

   // 初始化sqlite3
   sqlite3_initialize();

   // 打开数据库
   if (db_open("/spiffs/test1.db", &db1))
       return;
   if (db_open("/spiffs/test2.db", &db2))
       return;

   // 执行数据库db1建表语句
   rc = db_exec(db1, "CREATE TABLE test1 (id INTEGER, content);");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db2建表语句
   rc = db_exec(db2, "CREATE TABLE test2 (id INTEGER, content);");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db1插入语句
   rc = db_exec(db1, "INSERT INTO test1 VALUES (1, 'Hello, World from test1');");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db2插入语句
   rc = db_exec(db2, "INSERT INTO test2 VALUES (1, 'Hello, World from test2');");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db1查询语句
   rc = db_exec(db1, "SELECT * FROM test1");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db2查询语句
   rc = db_exec(db2, "SELECT * FROM test2");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 关闭数据库
   sqlite3_close(db1);
   sqlite3_close(db2);

}

void loop() {
}

ESP32-CAM开发板 使用 sqlite3 数据库存储数据记录-LMLPHP

  • 创建数据表时间花费为 220ms左右
  • 插入一条数据时间花费为130ms左右
  • 查询操作，10ms左右

5.2 利用SD卡运行数据库

/*
  在sd卡里面操作数据库
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <SPI.h>
//#include <FS.h>
#include "SD_MMC.h"

const char* data = "Callback function called";
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   Serial.printf("%s: ", (const char*)data);
   // 打印返回的数据
   for (i = 0; i<argc; i++){
       Serial.printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   Serial.printf("\n");
   return 0;
}

/***
 * 打开数据库
 */
int db_open(const char *filename, sqlite3 **db) {
   Serial.print(F("DB Name:"));
   Serial.print(filename);
   Serial.println(F(""));
   if (db != NULL)
    sqlite3_close(*db);
   int rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.print(F("Can't open database: "));
       Serial.print(sqlite3_extended_errcode(*db));
       Serial.print(" ");
       // 打印错误信息
       Serial.println(sqlite3_errmsg(*db));
       return rc;
   } else {
       Serial.printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;
/***
 * 执行数据库操作语句
 */
int db_exec(sqlite3 *db, const char *sql) {
   Serial.print(F("----------------------- db_exec -----------------------\n"));
   Serial.println(sql);
   Serial.println("");
   // 记录一个开始执行时间
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
    Serial.print(F("SQL error: "));
    Serial.print(sqlite3_extended_errcode(db));
    Serial.print(" ");
    Serial.println(zErrMsg);
    sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   // 计算花费时间
   Serial.print(F("Time taken:"));
   Serial.print(micros()-start);
   Serial.println(F(" us"));
   Serial.println(F("----------------------- db_exec -----------------------\n"));
   return rc;
}

void setup() {
   Serial.begin(115200);
   sqlite3 *db1;
   sqlite3 *db2;
   char *zErrMsg = 0;
   int rc;

   SPI.begin();
   SD_MMC.begin();

   sqlite3_initialize();

   // 打开数据库
   if (db_open("/sdcard/test1.db", &db1))
       return;
   if (db_open("/sdcard/test2.db", &db2))
       return;

   // 执行数据库db删表语句
   rc = db_exec(db1, "DROP TABLE IF EXISTS test1;");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }

   // 执行数据库db删表语句
   rc = db_exec(db2, "DROP TABLE IF EXISTS test2;");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }

   // 执行数据库db1建表语句
   rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS test1 (id INTEGER, content);");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }

   // 执行数据库db2建表语句
   rc = db_exec(db2, "CREATE TABLE IF NOT EXISTS test2 (id INTEGER, content);");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }

   // 执行数据库db1插入语句
   rc = db_exec(db1, "INSERT INTO test1 VALUES (1, 'Hello, World from test1');");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db2插入语句
   rc = db_exec(db2, "INSERT INTO test2 VALUES (1, 'Hello, World from test2');");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db1查询语句
   rc = db_exec(db1, "SELECT * FROM test1");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 执行数据库db2查询语句
   rc = db_exec(db2, "SELECT * FROM test2");
   if (rc != SQLITE_OK) {
       // 执行失败就关闭数据库
       sqlite3_close(db1);
       sqlite3_close(db2);
       return;
   }
   // 关闭数据库
   sqlite3_close(db1);
   sqlite3_close(db2);

}

void loop() {
}

操作test1.db 和 test2.db
ESP32-CAM开发板 使用 sqlite3 数据库存储数据记录-LMLPHP
串口日志内容：

DB Name:/sdcard/test1.db
Opened database successfully
DB Name:/sdcard/test2.db
Opened database successfully
----------------------- db_exec -----------------------
DROP TABLE IF EXISTS test1;

Operation done successfully
Time taken:26229 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
DROP TABLE IF EXISTS test2;

Operation done successfully
Time taken:15856 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
CREATE TABLE IF NOT EXISTS test1 (id INTEGER, content);

Operation done successfully
Time taken:99951 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
CREATE TABLE IF NOT EXISTS test2 (id INTEGER, content);

Operation done successfully
Time taken:71794 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
INSERT INTO test1 VALUES (1, 'Hello, World from test1');

Operation done successfully
Time taken:44203 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
INSERT INTO test2 VALUES (1, 'Hello, World from test2');

Operation done successfully
Time taken:36820 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
SELECT * FROM test1

Callback function called: id = 1
content = Hello, World from test1

Operation done successfully
Time taken:12690 us
----------------------- db_exec -----------------------

----------------------- db_exec -----------------------
SELECT * FROM test2

Callback function called: id = 1
content = Hello, World from test2

Operation done successfully
Time taken:12130 us
----------------------- db_exec -----------------------

6、应用场景

整体下来，个人建议可以先在电脑上创建好db文件（提前创建好），然后利用一个小内存的sd卡（比如几百MB等等），这样就可以存储大量的数据了。

  • 温湿度环境数据
  • 打卡数据
  • 远程环境监测
  • 等等
05-30 22:58
Powered by LMLPHP ©2024 单片机菜鸟哥 0.002831