【网络编程】Day7 sqlite3

将dict.txt导入到数据库中：单词一列，意思一列

代码dict_sql.c：

#include <myhead.h>

int insert(sqlite3 *db);
int main(int argc, const char *argv[])
{

	//1. 创建或直接打开一个数据库: dict.db
	sqlite3* db = NULL;
	if(sqlite3_open("./dict.db",&db) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open failed:%s,%d, __%d__\n",sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}
	printf("数据库打开成功\n");

	//2.创建一张表格
	char sql[128] = "create table if not exists dict(word char,mean char)";  //数据库中sql语句一样的写法,可修改
	char *errmsg=NULL;   //错误信息
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_exec:%s, __%d__\n",errmsg,__LINE__);
		return -1;
	}
	printf("创建表格 dict 成功\n");

	//3.插入数据到数据库中
	insert(db); 
	 
	//4.关闭数据库
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_close failed:%s,%d, __%d__\n",sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}
	printf("成功关闭数据库\n");
	return 0;
}


int insert(sqlite3 *db)
{
	FILE *fp;
	char word[100];
	char mean[100];
	char buf[300]="";

	char st[300] ="";  
	char *errmsg = NULL; 

	//打开要插入的文件
	if(NULL == (fp=fopen("./dict.txt","r")))
	{
		perror("fopen error");
		return -1;
	}

	//读取每行
	while((fgets(buf,sizeof(buf),fp)) != NULL)
	{
		int i=0;
		char *p = buf;
		memset(word,0,sizeof(word));
		buf[strlen(buf)-1] = '\0';
		while(*p!=' ')  //32
		{
			word[i]=*p;
			p++;
			i++;	
		}

		p++;
        if(*p !=' ')
        {
            word[i] = ' ';
            i++;
		    while(*p!=' ')  //32
		    {
			    word[i]=*p;
			    p++;
			    i++;	
		    }
            word[i]='\0'; 
        }
       	else word[i]='\0'; 

        p++;
        while(*p==' ' && *p != '\0')
        {
            p++;
        } 


//处理'问题 ?
		char *q = p;
		while(*q != '\0')
		{
			if(*q == '\'')
			{
				*q = '.';
			}  
			q++;
		}
		q = word;
		while(*q != '\0')
		{
			if(*q == '\'')
			{
				*q = '.';
			}
			q++;
		}

		strcpy(mean, p);
		sprintf(st,"insert into dict values('%s','%s')",word,mean);
		if(sqlite3_exec(db,st,NULL,NULL,&errmsg) != SQLITE_OK)
		{
			fprintf(stderr,"插入失败,%s,在第%d行\n",errmsg,__LINE__);
			return -1;
		}	
 		memset(buf,0,sizeof(buf));
	}
	fclose(fp);
	printf("单词插入成功\n");
	return 0;
}

运行如下：

ubuntu@ubuntu:day7_sqlite3$ gcc 02_dict_sql.c  -lsqlite3
ubuntu@ubuntu:day7_sqlite3$ ./a.out 
数据库打开成功
创建表格 dict 成功
单词插入成功
成功关闭数据库