如何使用 QT 转储 SQLite 数据库的所有表? [英] How to dump all tables of a SQLite DB with QT?

问题描述

我知道在 SQLite 中有一个用于转储所有表(到文件createDBTablesScript.sql")的功能:

i know in SQLite there is a functionality for dumping all Tables (to file "createDBTablesScript.sql") with:

sqlite> .output createDBTablesScript.sql
sqlite> .dump
sqlite> .quit

有没有办法在 QT 中做到这一点?像这样的东西(！这是行不通的！):

Is there a way to do this in QT? Something like this(!THIS IS NOT WORKING!) :

QString  queryString(".output createDBTablesScript.sql .dump");
QSqlQuery query( m_db );
bool checkQueryExcecution = query.exec( queryString );

之后我还想从 QT 运行脚本，例如:

Afterwards i wannt to run the script also from QT, something like:

QString  createDBTablesScriptString("run createDBTablesScript.sql");
QSqlQuery query( m_db );
bool checkQueryExcecution = query.exec( createDBTablesScriptString );

推荐答案

.dump 命令在 sqlite 命令行应用程序中实现，而不是在 SQLite 库本身中实现..dump 使用标准 SQL 查询从数据库中提取所需的一切.您也可以这样做，但不仅仅是 3 行.

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.

它看起来就像这样:

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(", "));
    }
}

一些注意事项:

1. 我是凭自己的想法写的，没有测试过，所以它可能有一些错误，但你已经大致了解了.

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

这不包括 .dump 生成的其他查询，例如 BEGIN; 和 PRAGMA foreign_keys = 0; 在开始，然后 COMMIT; 结束.

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

.dump 可能会在一些我不知道的特殊情况下生成更多查询.我只是尝试在我的测试数据库上运行 .dump，其中有 2 个表，这些都是我发现的所有语句.

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.

这篇关于如何使用 QT 转储 SQLite 数据库的所有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！