writing txt file by making query SQLITE database than INSERT query result another column than DELETE it



  • Hello,

    I create a SQLITE database like below and add my maillists reading txt files,

    db = QSqlDatabase::addDatabase("QSQLITE", "TxtFileDB");
    db.setDatabaseName(qApp->applicationDirPath() + "/mails.DB");

    i seperated tables for countries (Germany, Switzerland, Spain etc.) and show by tablewidget

    i want to create a maillist txt it takes random mails from the tables how many mail i write the tablewidget next column

    for example

    germany 100
    spain 50
    switzerland 150

    totally 300 mail.

    i made this but i need to delete mail from unsent mail column and add to sent column for all tables

    please give me an advice because i just tried take mails from databese randomly and write txt file but i thought i have to make a query for all cycle in while(query.next())

    maybe there is a way for take my query result paste sent column than delete from unsent column..

    so any idea will be accepted.

    void MainWindow::on_createMailListBtn_clicked()
    {
    QDateTime now = QDateTime::currentDateTime();
    QString timestamp = now.toString(QLatin1String("yyyyMMdd-hhmmsszzz"));
    QString fileName = QString::fromLatin1("mailList_%1.txt").arg(timestamp);
    QFile newMailListFile(fileName);
    newMailListFile.open(QIODevice::WriteOnly | QIODevice::Text);
    QTextStream out(&newMailListFile);

    QStringList tables = db.tables();
    QSqlQuery query(db);
    QString tableName;
    QString mailingCount;
    
    for (int i = 0; i < tables.size(); i++)
    {
        tableName = ui->mailsTable->item(i,0)->text();
        mailingCount = ui->mailsTable->item(i,1)->text();
    
        qDebug() << tableName;
        qDebug() << mailingCount;
    
       query.prepare("SELECT * FROM " + tableName + " ORDER BY RANDOM() LIMIT " + mailingCount );
    
      if(!query.exec())
     {
          qCritical() << query.lastQuery();
          qCritical() << query.lastError().databaseText();
          qCritical() << query.lastError().driverText();
          return;
      }
    
       while(query.next())
      {
          //query.value("mail").toString()
          out << query.value("mail").toString() << "\n";
       }
    
    }
    
    newMailListFile.close();
    

    }


  • Lifetime Qt Champion

    Hi,

    You can keep a list of the ids/primary key of the mails you are going to modify and then execute an update query using them.

    Hope it helps



  • if i take all ids for an array than can i use all of than in a query to take from unsent table to sent table once?

    for example

    QList<int> mailsUpdated;
    mailsUpdated.clear();

        while(query.next())
        {
            
            out << query.value("mail").toString() << "\n";
            mailsUpdated.append(query.value("id").toInt());
        }
    

    would you please give me an example for what you think about?

    Thanks.


  • Lifetime Qt Champion

    Translate the list in a string of comma separated numbers and use it in the update statement


Log in to reply