munna
26th October 2006, 11:04
Hi,
I have some text data in a CSV file which needs to be imported into a SQLite database. Following are the steps that can be used to import
1. File->import.
2. Select the CSV file that you want to import
3. This data is now shown in a QTableWidget which can be edited.
4. User can also select the column name which will correspond to some table in the database.
5. When user is done with editing, he can click on the import button.
Now, the problem.
The SQLite database has 2 tables, let us call them name_table and address_table. As the name suggests name_table stores the names and the address_table stores the addresses. Also, these two are related by an id. name_table has a field called id and address_table has a field called nameid which will always be equal for a particular pair of name and address.
While importing, I first insert into name_table, then get the id of the inserted row and then insert into address_table. I use QSqlTableModel to do this. Something like this:
QSqlTableModel tableModel;
QSqlRecord rec;
tableModel.setTable("name_table");
tableModel.select();
int rc = tableModel.rowCount();
tableModel.insertRow(rc);
rec = tableModel.record(rc);
rec.setValue(QString("prefix"),QVariant(nameDetails.prefix()));
rec.setValue(QString("firstname"),QVariant(nameDetails.firstName()));
rec.setValue(QString("middlename"),QVariant(nameDetails.middleName()));
rec.setValue(QString("lastname"),QVariant(nameDetails.lastName()));
rec.setValue(QString("suffix"),QVariant(nameDetails.suffix()));
rec.setValue(QString("nickname"),QVariant(nameDetails.nickName()));
tableModel.setRecord(rc,rec);
tableModel.submitAll();
tableModel.select();
rec = tableModel.record(tableModel.rowCount()-1);
currentNameId = rec.value("id").toInt();
//using currentNameId now insert into address_table in similar way
I call the above code in a loop for every row in the table. This process is too slow and can insert not more than 100 records per minute. I know this is a completely wrong way but cannot think of anything better.
Can someone please suggest the best way to do this so that the process of importing becomes fast ?
Thanks a lot for your time.
I have some text data in a CSV file which needs to be imported into a SQLite database. Following are the steps that can be used to import
1. File->import.
2. Select the CSV file that you want to import
3. This data is now shown in a QTableWidget which can be edited.
4. User can also select the column name which will correspond to some table in the database.
5. When user is done with editing, he can click on the import button.
Now, the problem.
The SQLite database has 2 tables, let us call them name_table and address_table. As the name suggests name_table stores the names and the address_table stores the addresses. Also, these two are related by an id. name_table has a field called id and address_table has a field called nameid which will always be equal for a particular pair of name and address.
While importing, I first insert into name_table, then get the id of the inserted row and then insert into address_table. I use QSqlTableModel to do this. Something like this:
QSqlTableModel tableModel;
QSqlRecord rec;
tableModel.setTable("name_table");
tableModel.select();
int rc = tableModel.rowCount();
tableModel.insertRow(rc);
rec = tableModel.record(rc);
rec.setValue(QString("prefix"),QVariant(nameDetails.prefix()));
rec.setValue(QString("firstname"),QVariant(nameDetails.firstName()));
rec.setValue(QString("middlename"),QVariant(nameDetails.middleName()));
rec.setValue(QString("lastname"),QVariant(nameDetails.lastName()));
rec.setValue(QString("suffix"),QVariant(nameDetails.suffix()));
rec.setValue(QString("nickname"),QVariant(nameDetails.nickName()));
tableModel.setRecord(rc,rec);
tableModel.submitAll();
tableModel.select();
rec = tableModel.record(tableModel.rowCount()-1);
currentNameId = rec.value("id").toInt();
//using currentNameId now insert into address_table in similar way
I call the above code in a loop for every row in the table. This process is too slow and can insert not more than 100 records per minute. I know this is a completely wrong way but cannot think of anything better.
Can someone please suggest the best way to do this so that the process of importing becomes fast ?
Thanks a lot for your time.