How to Dump the Data of Some Sqlite3 Tables

creating sqlite3 dump files with all table values

The output of .dump always includes all table rows:

sqlite> create table t(x);
sqlite> insert into t values (null), (0), (''), (x'');
sqlite> select * from t;

0


sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(x);
INSERT INTO "t" VALUES(NULL);
INSERT INTO "t" VALUES(0);
INSERT INTO "t" VALUES('');
INSERT INTO "t" VALUES(X'');
COMMIT;

How can I dump data that I want of some SQLite3 tables in SQL format?

The only way to do it within the sqlite console is to create a temporary table:

CREATE TABLE tmp AS SELECT (field1, field2 ... ) FROM yourTable WHERE ... ;
.dump tmp
DROP TABLE tmp

How can I dump SQLite database tables (that uses BLOB type) using Qt?

This not solved the specific problem, but I gave up on creating the SQL file.
I used the byteArray.toHex() command to generate the SQL file just for test purposes but it generated an SQL file much bigger than my database file (.db).

For example, my database file has 174 mb and the SQL file generated has 331 mb.

Then I gave up on creating the SQL file and I'm exporting a copy of my database file (.db) and then importing it to the system when needed.

To do so, I just create a new connection and copy all the content from this database file exported to my current database file.

As I said, this is not an answer to my question but it is a workaround for my general issue.

Exporting table from sqlite3

You should be able to dump the table like so:

.output filename
.dump tablename

It'll be dumped to the current folder with the filename you specify.

How to dump all tables of a SQLite DB with QT?

The .dump command is implemented in sqlite command line application, not in the SQLite library itself. The .dump uses standard SQL queries to extract everything that is needs from database. You can do it as well, but it's more than just 3 lines.

It would look somehow like that:

QSqlQuery query;
QStringList tables;
query.prepare("SELECT * FROM sqlite_master");
while (query.next())
{
    qDebug() << query.value("sql").toString();
    if (query.value("type").toString() == "table")
        tables << query.value("name");
}

static const QString insert = QStringLiteral("INSERT INTO %1 (%2) VALUES (%3);");
QStringList columns;
QStringList values;
QSqlRecord record;
bool first = true;
foreach (const QString& table, tables)
{
    first = true;
    query.prepare(QString("SELECT * FROM [%1]").arg(table));
    while (query.next())
    {
        record = query.record();
        for (int i = 0; i < record.count(); i++)
        {
            if (first)
                columns << record.fieldName(i);

            values << record.value(i);
        }
        first = false;

        qDebug() << insert.arg(table).arg(columns.join(", ")).arg(values.join(", "));
    }
}

Few notes:

1. I wrote it from my head, didn't test it, so it might have some bugs, but you get the general idea.

2. This doesn't include additional queries that .dump generates, like BEGIN; and PRAGMA foreign_keys = 0; at the beginning, then COMMIT; at the end.

3. The .dump might generate some more queries in some special cases, which I don't know about. I just tried to run .dump on my testing database with 2 tables in it and those were all statements I found as a result.

Dump only part of sqlite database

Instead of dumping to a file, you can directly write a new database:

ATTACH DATABASE New.db AS new;
CREATE TABLE new.stuff AS (SELECT * FROM table WHERE id % 10 = 0);

This should create the table stuff in New.db.

sqlite3: dump schema into .sql file from command line

The shell allows redirection, and sqlite3 can get the command as a parameter:

sqlite3 test.db .schema > schema.sql

---

Related Topics