sqlite3基本用法

最新推荐文章于 2024-09-27 15:25:36 发布

xxgxgx

最新推荐文章于 2024-09-27 15:25:36 发布

阅读量529

点赞数

分类专栏： sqlite3 文章标签： sqlite3 数据库

本文链接：https://blog.youkuaiyun.com/xxgxgx/article/details/51034788

版权

sqlite3 专栏收录该内容

11 篇文章

订阅专栏

linux系统下调用sqlite3库函数实现基本的数据库创建，查询，更改功能。

#include <sys/types.h>
#include <sys/socket.h>
#include <linux/in.h>
#include <linux/un.h>
#include <string.h>
#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <unistd.h>
#include <sys/ioctl.h>
#include <sys/select.h>
#include <sys/time.h>
#include <unistd.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"
#include <dlfcn.h>
//gcc  -o sqlite_1 sqlite_1.c -L. -lsqlite3 -ldl -lpthread
 static char *conf[]={"12.1","13.1","14.1","15.1","16.1","17.1","18.1","19.1","20.1","21.1"};
 static char *update[]={"22.1","23.1","24.1","25.1","26.1","27.1","28.1","29.1","30.1","31.1"};
 static sqlite3 *db=NULL;
 int i,result;
 char sql[128];
 char *errmsg;
 int ret;

 char **dbResult;
 float argument[10];
 int nRow,nColumn;
 int CurrentIndex;
void  sqlite_test_db_init()
{
  //打开或新建一个数据库
 ret = sqlite3_open("sqlite_test.db",&db);
 if(ret!=SQLITE_OK)
   {
     printf("open error!\n");
     exit(1);
   }
 //数据库中建表
result = sqlite3_exec(db, "create table if not exists base_info_table(ID integer primary key autoincrement,value);", NULL, NULL, &errmsg);
 if(result!=SQLITE_OK)
   { 
     printf("create base_info_table error!\n");
     return ;
   }
result = sqlite3_exec(db, "create table if not exists index_table(ID integer primary key autoincrement,current_index);", NULL, NULL, &errmsg);
 if(result!=SQLITE_OK)
   {   
     printf("create index_table error!\n");
     return ;
   }

//数据库中数据插入
 result = sqlite3_exec(db,"BEGIN",0,0,&errmsg);
 for(i=1;i<11;i++)
   {
    sprintf(sql,"insert into base_info_table values(%d,'%s')",i,conf[i-1]);
    result = sqlite3_exec(db,sql,0,0,&errmsg);
    if(result!=SQLITE_OK)
     {
      printf("insert error\n"); 
      return;
     }
   }
 result = sqlite3_exec(db,"COMMIT",0,0,&errmsg);
//
 result = sqlite3_exec(db,"BEGIN",0,0,&errmsg);

    sprintf(sql,"insert into index_table values(%d,%d)",1,1);
    result = sqlite3_exec(db,sql,0,0,&errmsg);
    if(result!=SQLITE_OK)
     {
      printf("insert error\n"); 
      return;
     }
 result = sqlite3_exec(db,"COMMIT",0,0,&errmsg);
} 
void  get_data_sqlite_test_db()
{
//从数据库中读取数据并转换数据类型
 ret = sqlite3_get_table(db,"select *from base_info_table",&dbResult,&nRow,&nColumn,&errmsg);
 if(ret == SQLITE_OK)
   {
     for(i=0;i<10;i++)//
     {
      argument[i] = atof(dbResult[3+2*i]);
      printf("%f\n",argument[i]);
     }
   }
}
static int GetCurrentIndex()
{
 result = sqlite3_get_table(db,"select *from index_table",&dbResult,&nRow,&nColumn,&errmsg);
 if(result == SQLITE_OK)
 {
  return atoi(dbResult[nColumn+1]);
 }
}

static int PutCurrentIndex(int index)
{
 sprintf(sql,"update index_table set current_index=%d where id=1",index);
 sqlite3_stmt *stmt;
 sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL);
 if(sqlite3_step(stmt)!=SQLITE_DONE)
   {
     sqlite3_finalize(stmt);
     return 0;
   }
}

void update_date_sqlite_test_db()
{
 CurrentIndex=GetCurrentIndex();
 printf("CurrentIndex=%d\n",CurrentIndex);
 ret = sqlite3_exec(db,"BEGIN",0,0,&errmsg);
 for(i=1;i<11;i++)
 {
 sprintf(sql,"update base_info_table set value='%s' where id=%d",update[i-1],i);
 ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
 CurrentIndex++;
 }
 ret = sqlite3_exec(db,"COMMIT",0,0,&errmsg);

  PutCurrentIndex(CurrentIndex);

}

int main(void)
{
 system("rm sqlite_test.db");
 sqlite_test_db_init();
 get_data_sqlite_test_db();
 update_date_sqlite_test_db();
 get_data_sqlite_test_db();
 sqlite3_free_table(dbResult);//释放内存
 sqlite3_close(db);//关闭数据库 防止内存泄漏
 return;
}