c语言sqlite插入变量,C语言操作sqlite数据库(增删改查)

1 sqlite3 * db=NULL;2 int rc = 0;3 char * sql = new char[800];4 char * zErrMsg =NULL;5

6 //for test

7 string id1 = "aaa";8 std::vector feature1{0.12, 0.23, 0.34, 0.45, 0.56, 0.67};//用来模拟人脸特征值

9

10 string id2 = "bbb";11 std::vector feature2{1.12, 1.23, 1.34, 1.45, 1.56, 1.67};//用来模拟人脸特征值12

13 //先把人脸特征值的float数组转成json，然后保存到数据库中。cjson的源码和例程在你自己的github上保存了

14 cJSON *root1, *js_feature1;15 root1 =cJSON_CreateObject();16 cJSON_AddItemToObject(root1, "face_feature1", js_feature1 =cJSON_CreateArray());17 for(int i = 0; i < feature1.size(); i++)18 {19 cJSON_AddItemToArray(js_feature1, cJSON_CreateNumber(feature1.at(i)));20 }21 char *s1 =cJSON_PrintUnformatted(root1);22 printf("s1:%s\n", s1);23

24

25 cJSON *root2, *js_feature2;26 root2 =cJSON_CreateObject();27 cJSON_AddItemToObject(root2, "face_feature2", js_feature2 =cJSON_CreateArray());28 for(int i = 0; i < feature2.size(); i++)29 {30 cJSON_AddItemToArray(js_feature2, cJSON_CreateNumber(feature2.at(i)));31 }32 char *s2 =cJSON_PrintUnformatted(root2);33 printf("s2:%s\n", s2);34

35

36 sqlite3_initialize();37 rc = sqlite3_open_v2("featureList.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);//这时候登录嵌入式设备，你会看见创建了一个featureList.db的文件。

38 if(rc !=SQLITE_OK)39 {40 sqlite3_close_v2(db);41 printf("open featureList sql fail\n");42 }43 else

44 {45 printf("open featureList sql success\n");46 }47

48

49 sprintf(sql, "CREATE TABLE featureList(ID INTEGER PRIMARY KEY AUTOINCREMENT, uuid VARCHAR(10),feature VARCHAR(10));"); //这里的table名字featureList为前面open函数时的名字featureList

50 sqlite3_exec(db, sql, 0, 0, &zErrMsg);51

52

53 //插入数据

54 sprintf(sql, "INSERT INTO featureList VALUES(NULL, '%s','%s');", id1.c_str(), s1);//ID传入NULL，那么会自动递增，这里的%s一定要用单引号括起来，

55 sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );56

57 sprintf(sql, "INSERT INTO featureList VALUES(NULL, '%s','%s');", id2.c_str(), s2);//ID传入NULL，那么会自动递增，这里的%s一定要用单引号括起来，

58 sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );59

60

61 //查询数据

62 int nrow = 0, ncolumn = 0;63 char **azResult; //二维数组存放结果

64

65 sprintf(sql, "SELECT * FROM featureList");66 sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );67 printf("row:%d, column:%d\n", nrow, ncolumn);68

69 for(int i = 0; i < (nrow + 1)*ncolumn; i++)70 {71 printf("azResult[%d] = %s\n", i, azResult[i]);72 }73

74 int deleteId = 1;75 sprintf(sql, "DELETE FROM featureList WHERE ID = %d", deleteId);76 printf("delete sql is:%s\n", sql);77 rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);78 if(rc ==SQLITE_OK)79 {80 //作用是重新构建数据库文件，回收空白空间，减小数据库文件的大小。

81 printf("delete success\n");82 }83 else

84 {85 printf("delete fail!\n");86 }87

88

89 sprintf(sql, "UPDATE featureList SET ID=ID-1 WHERE ID>1");//如果本来的ID是1 2 3 4，我们把3删除，那么ID变为1 2 4，UPDATE语句的作用是把ID重新排序成1 2 3.

90 rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);91

92 sprintf(sql, "SELECT * FROM featureList");93 sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );94 printf("row:%d, column:%d\n", nrow, ncolumn);95 for(int i = 0; i < (nrow + 1)*ncolumn; i++)96 {97 printf("azResult[%d] = %s\n", i, azResult[i]);98 }99

100 delete[] sql;101 sqlite3_free_table(azResult);102 sqlite3_close(db);