2022/11/29 单词表导入

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
int do_insert(sqlite3* db);
int do_delete(sqlite3* db);
int do_update(sqlite3* db);
int do_select(sqlite3* db);
int callback(void* arg,int column,char** column_text,char** column_name);
int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db=NULL;
	if(sqlite3_open("./my.db",&db)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(db));
		return -1;
	}
	printf("open success\n");
	//创建一张表格
	char sq[128]="create table if not exists book (word char,translate char);";
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("exec=%s\n",errmsg);
		return -1;
	}
	char c;
	while(1)
	{
		system("clear");
		printf("***********1.增************\n");
		printf("***********2.删************\n");
		printf("***********3.改************\n");
		printf("***********4.查************\n");
		printf("***********5.退出***********\n");
		printf("请输入>>>");
		c=getchar();
		while(getchar()!=10);
		switch(c)
		{
		case '1':
			do_insert(db);
			break;
		case '2':
			do_delete(db);
			break;
		case '3':
			do_update(db);
			break;
		case '4':
			do_select(db);
			break;
		case '5':
			goto END;
		default:
			printf("输入错误，请重新输入\n");
			break;
		}
		printf("请输入任意字符清屏>>>\n");
		while(getchar()!=10);
	}
END:
	//关闭数据库
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		printf("close:%s\n",sqlite3_errmsg(db));
		return -1;
	}	
	return 0;
}
int do_insert(sqlite3* db)
{
	int i=0,j=0;
	char *ptr;
	FILE* fp=fopen("./dict.txt","r");
	if(NULL==fp)
	{
		perror("fopen");
		return -1;
	}
	char word[128]="";
	while(1)
	{
		bzero(word,sizeof(word));
		ptr=fgets(word+1,sizeof(word),fp);
		if(ptr==NULL)
		{
			break;
		}
		word[0]='\"';
		j=0;
		while(word[j]!=' ')
		{
			j++;
		}
		word[j]='\"';

		i=strlen(word)-1;
		word[i]='\"';
		while(word[i]!=' ')
		{
			i--;
		}
		word[i]='\"';
		word[i-1]=',';
		printf("%s\n",word);
		char sq[128]="";
		sprintf(sq,"insert into book values(%s)",word);
		printf("%s\n",sq);
		char *errmsg;
		if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
		{
			printf("zexec:%s\n",errmsg);
			continue;
		}
	}
	return 0;
}
int do_delete(sqlite3* db)
{
	int i;
	printf("请输入要删除的id号\n");
	scanf("%d",&i);
	getchar();
	char sq[128]="";
	sprintf(sq,"%s%d","delete from word where id=",i);
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sexec:%s\n",errmsg);
		return -1;
	}
	return 0;
}
int do_update(sqlite3* db)
{
	int i;
	char word[128]="";
	printf("请输入要修改的id号，修改之后的内容\n");
	scanf("%d,%s",&i,word);
	char sq[128]="";
	sprintf(sq,"%s%c%s%c %s %s%d","update word set word=",'"',word,'"',"where","id=",i);
	printf("%s\n",sq);
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("gexec:%s\n",errmsg);
		return -1;
	}
	return 0;
}
/*int callback(void* arg,int column,char** column_text,char** column_name)
{
	int i=0;
	if(*(int*)arg==0)
	{
		for(i=0;i<column;i++)
		{
			printf("%s\t",column_name[i]);
		}
		printf("\n");
		*(int*)arg=1;
	}
	for(i=0;i<column;i++)
	{
		printf("%s\t",column_text[i]);
	}
	printf("\n");
	return 0;
}*/
int do_select(sqlite3* db)
{
	printf("111111\n");
	int i=0;
	char sq[128]="";
	sprintf(sq,"%s","select * from word;");
	printf("%s\n",sq);
	//int flag=0;
	char **ptr;
	char *errmsg;
	int row,column;
	if(sqlite3_get_table(db,sq,&ptr,&row,&column,&errmsg)!=SQLITE_OK)		
	{
		printf("cexec:%s\n",errmsg);
		return -1;
	}
	for(i=0;i<column*(row+1);i++)
	{
		printf("%s\t",ptr[i]);
		if(i%column==column-1)
		{
			putchar(10);
		}
	}
	return 0;
}