网络编程基础DAY6sqlite

最新推荐文章于 2024-09-05 10:00:22 发布

qq_49846847

最新推荐文章于 2024-09-05 10:00:22 发布

阅读量50

点赞数

文章标签：数据库 sqlite

本文链接：https://blog.csdn.net/qq_49846847/article/details/129673433

版权

该程序演示了如何用C语言连接SQLite数据库，创建表格dict，并从dict.txt读取数据插入到数据库中。用户可以选择执行插入或查询操作，查询功能通过sqlite3_get_table实现，显示所有单词和含义。

摘要由CSDN通过智能技术生成

将dict.text中的数据存储在数据库中·

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>


int do_insert(sqlite3* db);
int do_select(sqlite3* db);

int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./siji.db", &db) != SQLITE_OK)
	{
		fprintf(stderr, "__%d__ sqlite3_open: %d | %s\n", \
				__LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success __%d__\n", __LINE__);


	//创建表格 
	//数据库中怎么写，代码中就怎么写
	char sql[128] = "create table if not exists dict (num int,word char, meaning char);";
	char *errmsg = NULL;
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "__%d__ sqlite3_exec: %s\n", __LINE__, errmsg);
		return -1;

	}
	printf("table dict create success __%d__\n", __LINE__);

	char choose = 0;
	while(1)
	{
		system("clear");

		printf("------------------------\n");
		printf("--------1. 增加---------\n");
		printf("--------2. 查询---------\n");
		printf("--------3. 退出---------\n");
		printf("------------------------\n");

		printf("请输入>>> ");
		choose = getchar();
		while(getchar()!=10);

		switch(choose)
		{
		case '1':
			do_insert(db);
			break;
		case '2':
			do_select(db);
			break;
		case '3':
			goto END;
			break;
		default:
			printf("输入错误，请重新输入\n");
		}

		printf("请输入任意字符清屏>>> ");
		while(getchar()!=10);
	}

END:
	//关闭数据库，释放内存空间
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr, "__%d__ sqlite3_close: %d | %s\n", \
				__LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_close success __%d__\n", __LINE__);

	return 0;
}

int do_select(sqlite3* db)
{
	char sql[128] = "select word, meaning from dict";

	char **pres = NULL;
	int row, column;
	char* errmsg = NULL;

	if(sqlite3_get_table(db, sql, &pres, &row, &column, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "__%d__ sqlite3_get_table: %s\n", __LINE__, errmsg);
		return -1;
	}
	printf("sqlite3_get_table success __%d__\n", __LINE__);

	printf("row=%d column=%d\n", row, column);

	//row中只有记录行数，没有表头
	//而pres中包含表头的那一行，所以打印的时候需要将表头的那一行加上

	for(int i=0; i<(row+1)*column; i++)
	{
		printf("%s\t", pres[i]);
		if(i%column == column-1)
			putchar(10);
	}

	printf("__________________________\n");

	int index = 0;
	for(int i=0; i<row+1; i++)
	{
		for(int j=0; j<column; j++)
		{
			printf("%s\t", pres[index++]);
		}
		putchar(10);
	}


	//释放查询到的结果
	sqlite3_free_table(pres);
	pres = NULL;

	return 0;
}


//查询相关的回调函数
int callBack(void* arg, int ncloumn, char **column_text, char **column_name) //void* arg = &flag;
{
	//printf("ncloumn = %d\n", ncloumn);


	if(0 == *(int*)arg)
	{
		for(int i = 0;i<ncloumn; i++)
		{
			printf("%s\t", column_name[i]);
		}
		putchar(10);
		*(int*)arg = 1;
	}

	for(int i=0; i<ncloumn; i++)
	{
		printf("%s\t", column_text[i]);
	}
	putchar(10);



	return 0; 	//成功，必须返回0，否则会倒置sqlite3_exec函数运行失败
}

int do_insert(sqlite3* db)
{
	int i = 1;
	int fd = open("./dict.txt",O_RDONLY);
	if(fd < 0)
	{
		perror("open");
		return -1;
	}
	char sql[128] = "";	
	char word[128] = "";
	char meaning[128] = "";
	ssize_t res = 0;
	char buf[1];
	int flag = 0;
	char *errmsg = NULL;
	while(1)
	{
		res = read(fd,buf,1);
		if(res < 0)
		{
			perror("read");
			return -1;
		}
		if(0 == res)
		{
			break;
		}

		if(0 == flag)
		{
			if(strcmp(buf," ") != 0)
			{
				strcat(word,buf);
			}
			else
			{
				flag = 1;
				read(fd,buf,1);
				if(strcmp(buf," ") != 0)
				{
					strcat(word," ");
					strcat(word,buf);
					flag = 0;
				}

			}
		}
		if(1 == flag)
		{
			if(strcmp(buf," ")==0)
			{
				continue;
			}
			else if(strcmp(buf,"\n") != 0)
			{
				strcat(meaning,buf);

			}
			else if(strcmp(buf,"\n") == 0)
			{	
				sprintf(sql, "insert into dict values (%d,\"%s\", '%s')",i, word, meaning);
				if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
				{
					fprintf(stderr, "__%d__ sqlite3_exec: %s\n", __LINE__, errmsg);
					return -1;
				}
				i++;

				bzero(word,sizeof(word));
				bzero(meaning,sizeof(meaning));
				bzero(sql,sizeof(sql));
				flag = 0;
			}

		}
	}
	printf("insert into dict success __%d__\n", __LINE__);

	close(fd);
	return 0;
}

结果：