上位机图像处理和嵌入式模块部署（树莓派4b下使用sqlite3）

嵌入式设备下面，有的时候也要对数据进行处理和保存。如果处理的数据不是很多，一般用json就可以。但是数据如果量比较大，但是还没有达到要用大型数据库的时候，这种情况下选择一个sqlite3这样的数据库，其实就可以了。所以，不管是上位机，还是在linux开发板上面，大家都喜欢用sqlite3来对数据进行处理和保存。今天，正好借助于这样一个机会，学习下sqlite3。

1、安装sqlite3开发库

安装的方法不复杂，直接sudo apt-get安装即可，

sudo apt-get install libsqlite3-dev

2、准备测试代码

准备的测试代码不复杂，主要就是创建一个student.db。创建好了之后，建设一张表。有了这张表，就可以做增、删、改、查的动作了。最后肯定就是关闭数据库。

#include <sqlite3.h>
#include <stdio.h>int main() {sqlite3 *db;char *err_msg = 0;// Open the databaseint rc = sqlite3_open("student.db", &db);if (rc != SQLITE_OK) {fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}// SQL statement to create a tableconst char *create_table_sql = "CREATE TABLE IF NOT EXISTS student_table (id INTEGER PRIMARY KEY, name TEXT)";// Execute the SQL statement to create the tablerc = sqlite3_exec(db, create_table_sql, 0, 0, &err_msg);if (rc != SQLITE_OK) {fprintf(stderr, "SQL error: %s\n", err_msg);sqlite3_free(err_msg);sqlite3_close(db);return 1;}// SQL statement to insert dataconst char *insert_sql = "INSERT INTO student_table (id, name) VALUES (?, ?)";// Prepare the insert statementsqlite3_stmt *stmt;rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, NULL);if (rc != SQLITE_OK) {fprintf(stderr, "Failed to prepare insert statement: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}// Bind parameters and execute the insert statementint id = 1;const char *name = "John";sqlite3_bind_int(stmt, 1, id);sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);rc = sqlite3_step(stmt);if (rc != SQLITE_DONE) {fprintf(stderr, "Error inserting data: %s\n", sqlite3_errmsg(db));sqlite3_finalize(stmt);sqlite3_close(db);return 1;}// Finalize the insert statementsqlite3_finalize(stmt);// SQL query to select dataconst char *select_sql = "SELECT * FROM student_table";// Prepare and execute the queryrc = sqlite3_prepare_v2(db, select_sql, -1, &stmt, NULL);if (rc != SQLITE_OK) {fprintf(stderr, "Failed to execute select statement: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}// Iterate over the resultswhile ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {// Process each rowint id = sqlite3_column_int(stmt, 0);const unsigned char *name = sqlite3_column_text(stmt, 1);// Process the data...printf("ID: %d, Name: %s\n", id, name);}// Check for errors or end of dataif (rc != SQLITE_DONE) {fprintf(stderr, "Error reading data: %s\n", sqlite3_errmsg(db));}// Finalize the query statementsqlite3_finalize(stmt);// SQL statement to update dataconst char *update_sql = "UPDATE student_table SET name = ? WHERE id = ?";// Prepare the update statementrc = sqlite3_prepare_v2(db, update_sql, -1, &stmt, NULL);if (rc != SQLITE_OK) {fprintf(stderr, "Failed to prepare update statement: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}// Bind parameters and execute the update statementconst char *new_name = "Alice";sqlite3_bind_text(stmt, 1, new_name, -1, SQLITE_STATIC);sqlite3_bind_int(stmt, 2, id);rc = sqlite3_step(stmt);if (rc != SQLITE_DONE) {fprintf(stderr, "Error updating data: %s\n", sqlite3_errmsg(db));sqlite3_finalize(stmt);sqlite3_close(db);return 1;}// Finalize the update statementsqlite3_finalize(stmt);// SQL statement to delete dataconst char *delete_sql = "DELETE FROM student_table WHERE id = ?";// Prepare the delete statementrc = sqlite3_prepare_v2(db, delete_sql, -1, &stmt, NULL);if (rc != SQLITE_OK) {fprintf(stderr, "Failed to prepare delete statement: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 1;}// Bind parameter and execute the delete statementsqlite3_bind_int(stmt, 1, id);rc = sqlite3_step(stmt);if (rc != SQLITE_DONE) {fprintf(stderr, "Error deleting data: %s\n", sqlite3_errmsg(db));sqlite3_finalize(stmt);sqlite3_close(db);return 1;}// Finalize the delete statementsqlite3_finalize(stmt);// Close the databasesqlite3_close(db);return 0;
}