[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
      bq.

    Hope it's util.

    Regards.



  • 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.
    Regards.



  • 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();
    }
    tablesWithFields.insert(table,fields);
    }
    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();
    sortedTables.move(sorted.indexOf("table1"),0);
    ...

    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;
        query.previous();
        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
                    statement.append(value+",'");
                } else {
                statement.append(value+"','");
                }
            }
            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;";
    f.close();
    

    }
    @

    And then I batch execute the sql file like this:

    @
    ...
    exportTables();
    QSqlQuery query(targetDb);
    QFile f(QDir::homePath()+"/myDump.sql");
    f.open(QIODevice::ReadOnly | QIODevice::Text);
    if(!query.exec(f.readAll()))
    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.