c - 从 C 程序将 sqlite3 表导出到文本文件

Question

我在 Sqlite3 中有一个数据库。我通过在 Linux C(API:sqlite3_exec)中编写应用程序来完成所有操作。目前我正在尝试将数据库导出到文本文件。任何人都可以建议相同的查询语句。

Answer 1

这不是您要找的，但非常接近。这会将您的查询以 JSON 格式输出到 outbuf，我没有在代码段中包含 b64encode()。

 int sqlite3_json(sqlite3_stmt *s, char *outbuf, size_t blen, size_t *used)
{
    int nuse=0;
    int res,i=0,ncolumn=sqlite3_column_count(s);

    nuse+=snprintf(outbuf+nuse,blen-nuse,"[\n");
    while(SQLITE_ROW==(res=sqlite3_step(s))) {
        nuse+=snprintf(outbuf+nuse,blen-nuse, "\t{\n");
        for(i=0;i<ncolumn;++i) {
            nuse+=snprintf(outbuf+nuse,blen-nuse,
                    "\t\t\"%s\" : ", sqlite3_column_name(s,i));
            switch (sqlite3_column_type(s,i)) {
                case SQLITE_INTEGER:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "%d,\n", sqlite3_column_int(s,i));
                    break;
                case SQLITE_FLOAT:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "%e,\n", sqlite3_column_double(s,i));
                    break;
                case SQLITE_TEXT:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,
                            "\"%s\",\n", sqlite3_column_text(s,i));
                    break;
                case SQLITE_BLOB:
                    nuse+=snprintf(outbuf+nuse,blen-nuse,"\"");
                    nuse+=b64encode((char*) sqlite3_column_blob(s,i),
                            sqlite3_column_bytes(s,i),
                            outbuf+nuse,blen-nuse);
                    nuse+=snprintf(outbuf+nuse,blen-nuse,"\",\n");
            }
        }
        nuse-=2; // trailing ',\n'
        nuse+=snprintf(outbuf+nuse,blen-nuse, "\n\t},\n");
        if( blen < nuse ) {
            printf("buffer overflow\n");
            break;
        }
    }
    if(i&&res==SQLITE_DONE) nuse-=2; //trailing ',\n'
    nuse+=snprintf(outbuf+nuse,blen-nuse,"\n]\n");
    if(used) *used=nuse;
    return res;
}