I'm trying to wrap my head around model/view classes especially with respect to SQL. So I've got a simple function that scans through a directory, reads ID3 tags, and inserts one row into a table for each song.

Using just QSqlQuery and doing a db.transaction(), db.commit() around the entire scanning (so one giant transaction) works just fine and processes about 500 files/s. It looks something like this:

Qt Code: 
  1. db.transaction()
  2. it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
  3. 			while it.hasNext():
  4. 				try:
  5. 					path = it.next()
  6. 					if path.endsWith('.mp3',  Qt.CaseInsensitive):
  7. 						items += 1
  8. 						try:
  9. 							audio = EasyID3(path)
  10. 							artist = audio['artist'][0]
  11. 							title = audio['title'][0]
  12. 							album = audio['album'][0]
  13.  
  14. 							query.prepare('INSERT INTO songs VALUES(:guid, :title, :artist, :album)')
  15. 							query.bindValue(':guid', QVariant(QUuid.createUuid().toString()))
  16. 							query.bindValue(':title', QVariant(title))
  17. 							query.bindValue(':artist', QVariant(artist))
  18. 							query.bindValue(':album', QVariant(album))
  19. 							query.exec_()
  20. 						except ID3NoHeaderError:
  21. 							noHeaders += 1
  22. 						except UnicodeDecodeError:
  23. 							decodeError += 1
  24. 						except IOError:
  25. 							notFound += 1
  26. 						except KeyError:
  27. 							noTags += 1
  28. 				except UnicodeEncodeError:
  29. 					encodeError += 1
  30. 			db.commit()
To copy to clipboard, switch view to plain text mode 

Now I want to use QSqlTableModel to do the same thing (I know it doesn't make much sense, just trying to learn this stuff). Then my code looks like this:

Qt Code: 
  1. songsModel =QSqlTableModel()
  2. 			songsModel.setTable("songs")
  3. 			songsModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
  4. 			it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
  5. 			while it.hasNext():
  6. 				try:
  7. 					path = it.next()
  8. 					if path.endsWith('.mp3',  Qt.CaseInsensitive):
  9. 						items += 1
  10. 						try:
  11. 							audio = EasyID3(path)
  12. 							artist = audio['artist'][0]
  13. 							title = audio['title'][0]
  14. 							album = audio['album'][0]
  15.  
  16. 							row = songsModel.rowCount()
  17. 							songsModel.insertRow(row)
  18. 							songsModel.setData(songsModel.index(row, 0), QVariant(QUuid.createUuid().toString()))
  19. 							songsModel.setData(songsModel.index(row, 1), QVariant(title))
  20. 							songsModel.setData(songsModel.index(row, 2), QVariant(artist))
  21. 							songsModel.setData(songsModel.index(row, 3), QVariant(album))
  22. 						except ID3NoHeaderError:
  23. 							noHeaders += 1
  24. 						except UnicodeDecodeError:
  25. 							decodeError += 1
  26. 						except IOError:
  27. 							notFound += 1
  28. 						except KeyError:
  29. 							noTags += 1
  30. 				except UnicodeEncodeError:
  31. 					encodeError += 1
  32. songsModel.submitAll()
To copy to clipboard, switch view to plain text mode 

Since I set it to manual submit changes, it should do one giant transaction just as before. However now the scanning performance degrades to about 100 items/s, which is about the speed I get when I don't use transactions in the first case..

So what am I not understanding? Is QSqlTableModel just not meant for mass inserts? Any help would be greatly appreciated.