[SOLVED] How can I dump database tables with Qt?

  • Hi,

    Is there a way to dump tables from an Sqlite database (or the whole database) into a text file using Qt's QSqlDatabase, QSqlQuery, and friends? This file will be used to feed data to another database system.

    Thanks in advance.

  • Hi panosk.
    Maybe you can see how "qsqlitebrowser":http://sqlitebrowser.sourceforge.net/ do it.
    From qsqlitebrowser

    bq. What it is
    SQLite Database Browser is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite.

    • Import and export databases from/to SQL dump files

    Hope it's util.


  • Hi SergioDanielG,

    Thanks for the link, but I need to do this programmatically from within my code. As a last resort, I may try to create a QProcess that will run the sqlite3 client utility and then use ".dump", but I would like to avoid it if there is another way.

  • My idea is: Download source and read the code of qsqlitebrowser and watch how qsqlitebrowser dump tables.
    Or use "QSqlDatabase":http://qt-project.org/doc/qt-5.0/qtsql/qsqldatabase.html , "QSqlQuery":http://qt-project.org/doc/qt-5.0/qtsql/qsqlquery.html , etc and sql commands like
    select * from sqlite_master;

    to search database's tables structure, then filter data of tables and then write to a file.
    Hope it's util.

  • Hi,

    I finally came up with this solution which seems to work for my purposes and the resulting file imports fine into PostgreSQL. Note that I don't need the schema, only the data, but it should be easy to include schemas too. As the code is very fresh, any comments, suggestions, or improvements are most welcome.

    void DbManager::exportTables()
    QHash<QString,QStringList> tablesWithFields; //It holds the table name and its fields
    QStringList tables = sourceDb.tables();
    QSqlQuery query(sourceDb);
    foreach(QString table,tables) {
    query.exec(QString("PRAGMA TABLE_INFO(%1)").arg(table));
    QStringList fields;
    while(query.next()) {
    fields << query.value(1).toString();
    QFile f(QDir::homePath() + "/myDump.sql");
    f.open(QIODevice::Append | QIODevice::Text);
    QTextStream streamer(&f);

    /*If constraints can't be dropped in the target database, some reordering of the INSERT statements may be needed
    QStringList sortedTables = tablesWithFields.keys();

    streamer << "BEGIN;\n";
    foreach(const QString &table,sortedTables) {
        if(table=="sqlite_sequence" /*|| table=="table4", etc*/) continue;
        QString statement = QString("INSERT INTO %1 VALUES('").arg(table);
        QStringList fields = tablesWithFields.value(table);
        QString fieldsString = fields.join(",");
        query.exec&#40;QString("SELECT %1 FROM %2"&#41;.arg(fieldsString).arg(table));
        if(!query.next()) continue;
        while(query.next()) {
            for(int i=0; i < fields.size(); ++i) {
                QString value = query.value(i).toString();
                value.replace("'","''"); //Handle single quotes inside strings
                if(value.isEmpty()) {
                    value = "NULL";
                    statement.chop(1); //NULL should not appear inside quotes
                } else {
            statement.chop(2); //Remove comma and single quote from the end of value group
            statement.append("),('"); //Close the value group and start a new one
        statement.chop(3); //Remove comma, opening parenthesis and single quote from the end
        streamer << statement << ";\n"; //Complete the INSERT statement
    streamer << "COMMIT;";


    And then I batch execute the sql file like this:

    QSqlQuery query(targetDb);
    QFile f(QDir::homePath()+"/myDump.sql");
    f.open(QIODevice::ReadOnly | QIODevice::Text);
    qCritical() << "Can't execute sql file: " << query.lastError().text();

Log in to reply

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.