sqlite3 简单操作
程序员文章站
2022-11-30 16:40:37
#include #include #include "sqlite3.h"/*sqlite3_exec 执行sql对应的功能命令，然后将结果传递给回调函数pv:由sqlite3_exec传递的初始化参数 argc:表头的列数 col:表头的名字数组指针 argv:表头的数据数组指针*/static int callback(void *pv, int argc, char **argv, char *....
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
/*
sqlite3_exec 执行sql对应的功能命令，然后将结果传递给回调函数
pv:由sqlite3_exec传递的初始化参数
argc:表头的列数
col:表头的名字数组指针
argv:表头的数据数组指针
*/
static int callback(void *pv, int argc, char **argv, char **col)
{
int i;
printf("%s\n", (const char*)pv);
for(i=0; i< argc; i++){
printf("%s = %s\n", col[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char**argv)
{
sqlite3 *db=NULL;
int len;
int i=0;
int nrow=0;
int ncolumn = 0;
char *zErrMsg =NULL;
char **azResult=NULL; //二维数组存放结果
/* 打开数据库 */
int ret = sqlite3_open("/test.db",&db);
if(ret != SQLITE_OK)
{
/* fprintf函数格式化输出错误信息到指定的stderr文件流中 */
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));//sqlite3_errmsg(db)用以获得数据库打开错误码的英文描述。
sqlite3_close(db);
exit(1);
}
else
{
printf("You have opened a sqlite3 database named test successfully!\n");
}
/*定义一个毫秒数，当未到达该毫秒数时，sqlite会sleep并重试当前操作
如果超过ms毫秒，仍然申请不到需要的锁，当前操作返回sqlite_BUSY
当ms<=0时，清除busy handle，申请不到锁直接返回
*/
ret = sqlite3_busy_timeout(db,1000);
if(ret != SQLITE_OK)
{
printf("sqlite3_busy_timeout:%s\n",zErrMsg);
sqlite3_close(db);
return 0;
}
/* 创建表 */
char *sql = " CREATE TABLE test_table(\
ID INTEDER PRIMARY KEY,\
SensorID INTEGER,\
siteNum INTEGER,\
Time VARCHAR(12),\
SensorParameter REAL\
);" ;
ret = sqlite3_exec(db, sql, callback, NULL,&zErrMsg);
if(ret != SQLITE_OK)
{
printf("sqlite3_exec :%s\n",zErrMsg);
sqlite3_free(zErrMsg);
}
/*插入数据 */
char*sql1 ="INSERT INTO 'test_table'VALUES(NULL,1,2,201430506201,13.5);";
sqlite3_exec(db,sql1,callback,sql1,&zErrMsg);
char*sql2 ="INSERT INTO 'test_table'VALUES(NULL,3,4,201530506302,14.5);";
sqlite3_exec(db,sql2,callback,sql2,&zErrMsg);
char*sql3 ="INSERT INTO 'test_table'VALUES(NULL,5,6,201630506413,18.6);";
sqlite3_exec(db,sql3,callback,sql3,&zErrMsg);
/* 查询数据 可以用sqlite3_get_table函数，也可以sqlite3_exec函数调用回调函数*/
sql="select *from test_table";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf("nrow=%d ncolumn=%d\n",nrow,ncolumn);
printf("the result is:\n");
for(i=0;i<(nrow+1)*ncolumn;i++)
{
printf("azResult[%d]=%s\n",i,azResult[i]);
}
/* 删除某个特定的数据 */
sql="delete from test_table where SensorID = 1 ;";
sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );
printf("zErrMsg = %s \n", zErrMsg);
sqlite3_free(zErrMsg);
/* 查询删除后的数据 */
sql = "SELECT * FROM test_table ";
/*sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
printf( "row:%d column=%d\n " , nrow , ncolumn );
printf( "After deleting , the result is : \n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
{
printf( "azResult[%d] = %s\n", i , azResult[i] );
}*/
sqlite3_exec(db, sql, callback, sql,&zErrMsg);
sqlite3_free_table(azResult);
printf("zErrMsg = %s \n", zErrMsg);
sqlite3_free(zErrMsg);
sqlite3_close(db);
return 0;
}
编译命令
gcc sqlite_test.c -o sqlite_test -lsqlite3
执行结果
./sqlite_test
You have opened a sqlite3 database named test successfully!
sqlite3_exec :table test_table already exists
nrow=9 ncolumn=5
the result is:
azResult[0]=ID
azResult[1]=SensorID
azResult[2]=siteNum
azResult[3]=Time
azResult[4]=SensorParameter
azResult[5]=(null)
azResult[6]=3
azResult[7]=4
azResult[8]=201530506302
azResult[9]=14.5
azResult[10]=(null)
azResult[11]=5
azResult[12]=6
azResult[13]=201630506413
azResult[14]=18.6
azResult[15]=(null)
azResult[16]=3
azResult[17]=4
azResult[18]=201530506302
azResult[19]=14.5
azResult[20]=(null)
azResult[21]=5
azResult[22]=6
azResult[23]=201630506413
azResult[24]=18.6
azResult[25]=(null)
azResult[26]=3
azResult[27]=4
azResult[28]=201530506302
azResult[29]=14.5
azResult[30]=(null)
azResult[31]=5
azResult[32]=6
azResult[33]=201630506413
azResult[34]=18.6
azResult[35]=(null)
azResult[36]=1
azResult[37]=2
azResult[38]=201430506201
azResult[39]=13.5
azResult[40]=(null)
azResult[41]=3
azResult[42]=4
azResult[43]=201530506302
azResult[44]=14.5
azResult[45]=(null)
azResult[46]=5
azResult[47]=6
azResult[48]=201630506413
azResult[49]=18.6
zErrMsg = (null)
SELECT * FROM test_table
ID = NULL
SensorID = 3
siteNum = 4
Time = 201530506302
SensorParameter = 14.5
SELECT * FROM test_table
ID = NULL
SensorID = 5
siteNum = 6
Time = 201630506413
SensorParameter = 18.6
SELECT * FROM test_table
ID = NULL
SensorID = 3
siteNum = 4
Time = 201530506302
SensorParameter = 14.5
SELECT * FROM test_table
ID = NULL
SensorID = 5
siteNum = 6
Time = 201630506413
SensorParameter = 18.6
SELECT * FROM test_table
ID = NULL
SensorID = 3
siteNum = 4
Time = 201530506302
SensorParameter = 14.5
SELECT * FROM test_table
ID = NULL
SensorID = 5
siteNum = 6
Time = 201630506413
SensorParameter = 18.6
SELECT * FROM test_table
ID = NULL
SensorID = 3
siteNum = 4
Time = 201530506302
SensorParameter = 14.5
SELECT * FROM test_table
ID = NULL
SensorID = 5
siteNum = 6
Time = 201630506413
SensorParameter = 18.6
zErrMsg = (null)
[root@test sqlite]#
基本操作命令
[root@test sqlite]# sqlite3 /test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main /test.db
sqlite> .tables
test_table
sqlite> select *from test_table
...> ;
|3|4|201530506302|14.5
|5|6|201630506413|18.6
|3|4|201530506302|14.5
|5|6|201630506413|18.6
|3|4|201530506302|14.5
|5|6|201630506413|18.6
|3|4|201530506302|14.5
|5|6|201630506413|18.6
sqlite> quit
...> ;
Error: near "quit": syntax error
sqlite> exit
...> ;
Error: near "exit": syntax error
sqlite> .exit
[root@test sqlite]#
本文地址：https://blog.csdn.net/u014608280/article/details/107317009