基于sqlite的c语言数据读取(c为主)

创建数据库：

create table pmTable (

DATA CHAR(30),

pmValueINTEGER

);

<pre name="code" class="cpp">time_t timep;
struct tm *p;
time(&timep);
char buff[30] = {0};
p = localtime(&timep);	//Get local time
sprintf(buff,"%d/%d/%d %d:00:00",(1900+p->tm_year),(1+p->tm_mon),p->tm_mday,p->tm_hour);
sql = sqlite3_mprintf("INSERT INTO pmTable VALUES("%s",39);",buff);

创建后的数据库为

2014/11/6 7:00:00|32

2014/11/6 8:00:00|32

2014/11/7 8:00:00|43

2014/11/8 1:00:00|12

2014/11/8 2:00:00|14

2014/11/9 3:00:00|19

2014/11/10 5:00:00|20

2014/11/11 10:00:00|39

下面就是怎么读取特定时间的数据和对数据进行操作：

1.读取当前月每天的1小时内的数据，并以

{"day":[{"hour":"00","value":"25"},{"hour":"01","value":"36"},{"hour":"02","value":"60"},{"hour":"03","value","23"}...]}这样的数据格式返回

sql = sqlite3_mprintf("select pmValue from pmTable where(DATA like "%d/%d/%d%c");",p->tm_mday-1,'%');
	sqlite3_prepare_v2(db,sql,-1,&ppStmt,NULL);
	memset(column,256);
	
	char tmp[50] = {0};
	memset(sql,sizeof(sql));
	sql = sqlite3_mprintf("select DATA from pmTable where(DATA like "%d/%d/%d%c");",'%');
	sqlite3_stmt *ppStmt2 = NULL;
	sqlite3_prepare_v2(db,&ppStmt2,NULL);
	char time[3] = {0};
	sprintf(column,"{"day":[");
	while(sqlite3_step(ppStmt) == SQLITE_ROW)
	{
		char ss[220] = {0};
		if(sqlite3_step(ppStmt2) == SQLITE_ROW)
		{
			sprintf(ss,"%sn",sqlite3_column_text(ppStmt2,0));
			int i = 0;
			int k= 0;
			for(; i < strlen(ss); i++)
			{
				if(strncmp(ss+i," ",1) == 0)
				{
					k = 1;
					continue;
				}
				if(k == 1 && strncmp(ss+i,":",1) != 0)
				{
					strncat(time,ss+i,1);
				}
				else if(strncmp(ss+i,1) == 0)
					break;
			}
		}
		sprintf(tmp,"{"hour":"%s","value":"%s"},",time,sqlite3_column_text(ppStmt,0));
		strcat(column,tmp);
		memset(tmp,sizeof(tmp));
		memset(time,sizeof(time));
	}
	column[strlen(column) - 1] = '';
	strcat(column,"]}");
	printf("column = %sn",column);

2.获取当前月每天的pmValue的最大值，并以

{"month":[{"day":"2014/01/01","max_value":"90","min_value":"23"},{"day":"2014/01/02","max_value":"77","min_value":"20"},{"day":"2014/01/03","max_value":"66","min_value":"18"},{"day":"2014/01/04","max_value":"155","min_value":"45"},{"day":"2014/01/05","max_value":"200","min_value":"100"}....]}这样的数据格式返回

sql = sqlite3_mprintf("select DATA from pmTable where(DATA like "%d/%d/%c");",'%');
sqlite3_prepare_v2(db,NULL);
memset(column,sizeof(column));
char tmp[50] = {0};
memset(sql,sizeof(sql));
	
char time[30] = {0};
char ttmp[30] = {0};
sprintf(column,"{"month":[");
while(sqlite3_step(ppStmt) == SQLITE_ROW)
{
<span style="white-space:pre">	</span>char ss[220] = {0};
	sprintf(ss,"%s",0));
		
	int i = 0;
	for(; i < strlen(ss); i++)
	{
		if(strncmp(ss+i,1) == 0)
		{
			break;
		}
		strncat(time,1);
	}
	if(strcmp(ttmp,time) == 0)
	{
		memset(time,sizeof(time));
		continue;
	}
	memset(ttmp,sizeof(ttmp));
	sql = sqlite3_mprintf("select max(pmValue) from pmTable where(DATA like "%s%c");",NULL);
	if(sqlite3_step(ppStmt2) == SQLITE_ROW)
	{
		sprintf(tmp,"{"day":"%s","max_value":"%s"},tmp);
	}
	strcpy(ttmp,time);
	memset(time,sizeof(time));
	memset(tmp,sizeof(tmp));
}
column[strlen(column) - 1] = '';
strcat(column,"]}");
printf("column = %sn",column);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!