电子词典-导入

将单词表导入sqlite3数据库

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

int do_insert(sqlite3 *db); // 增

int main(int argc, const char *argv[])
{
    // 如果数据库不存在，则创建后打开
    // 如果存在则直接打开
    sqlite3 *db = NULL;
    if (sqlite3_open("./my.db", &db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_open :%s errcode:%d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }
    printf("sqlite3_open success\n");

    // 创建一个表
    // 注意：C代码中编写的SQL语句与在数据库中编写的一直
    char sql[128] = "create table if not exists stu (id int,chainese char, English char)";
    char *errmsg = NULL;

    if (sqlite3_exec(db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
        return -1;
    }
    printf("create table stu success\n");

    char buf[128] = "";
    char c = 0;
    while (1)
    {
        // system("clear");
        printf("---------------------------------------------\n");
        printf("-------------------1. 增---------------------\n");
        printf("-------------------2. 删---------------------\n");
        printf("-------------------3. 改---------------------\n");
        printf("-------------------4. 查---------------------\n");
        printf("-------------------5. 退出-------------------\n");
        printf("---------------------------------------------\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
            break;
        case '5':
            goto END;
        default:
            printf("输入错误，请重新输入\n");
        }

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

END:
    // 关闭数据库
    if (sqlite3_close(db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_close :%s errcode:%d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }
    return 0;
}

// 增
int do_insert(sqlite3 *db)
{
    // 存储sql插入指令
    char sql[128] = "";

    // 创建流指针并打开文件
    int fd = open("./dict.txt", O_RDONLY);
    if (fd < -1)
    {
        perror("open");
        return -1;
    }

    char buf[2] = "";         // read读取单词暂存
    char word[100] = "";      // 存读取的单词
    char translate[100] = ""; // 存读取的词义
    int count = 1;            // 计数空格
    int i = 1;

    // 接read的返回值
    ssize_t res = 0;
    while (1)
    {

        while (buf[0] != '\n' || res == 0)
        {

            res = read(fd, buf, 1);

            while (buf[0] == ' ')
            {
                count++;
                res = read(fd, buf, 1);
            }

            if (count > 3)
            {
                if (buf[0] == '\n')
                {
                    buf[0] = '\0';
                }

                strcat(translate, buf);

                if (buf[0] == '\0')
                {
                    buf[0] = '\n';
                }
            }
            else
            {
                strcat(word, buf);
            }
            if (res == 0)
            {
                return 0;
            }
        }

        printf("word=%s\ttranslate=%s\n", word, translate);
        // 把sql指令存入数组
        sprintf(sql, "insert into stu values (%d, '%s', '%s')", i, word, translate);
        i++;
        // printf("%s\n",sql);
        //  发送并执行sql语句
        if (sqlite3_exec(db, sql, NULL, NULL, NULL) != SQLITE_OK)
        {
            printf("执行SQL插入命令错误\n");
            return -1;
        }
        printf("插入数据成功\n");

        count = 1;
        bzero(buf, sizeof(buf));
        bzero(word, sizeof(word));
        bzero(translate, sizeof(translate));

        printf("res = %ld\n", res);
        if (res == 0)
        {
            printf("导入完毕\n");
            return -1;
        }
    }

    return 0;
}