pherthyl
15th October 2008, 19:40
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:
db.transaction()
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]
query.prepare('INSERT INTO songs VALUES(:guid, :title, :artist, :album)')
query.bindValue(':guid', QVariant(QUuid.createUuid().toString()))
query.bindValue(':title', QVariant(title))
query.bindValue(':artist', QVariant(artist))
query.bindValue(':album', QVariant(album))
query.exec_()
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
db.commit()
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:
songsModel =QSqlTableModel()
songsModel.setTable("songs")
songsModel.setEditStrategy(QSqlTableModel.OnManual Submit)
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]
row = songsModel.rowCount()
songsModel.insertRow(row)
songsModel.setData(songsModel.index(row, 0), QVariant(QUuid.createUuid().toString()))
songsModel.setData(songsModel.index(row, 1), QVariant(title))
songsModel.setData(songsModel.index(row, 2), QVariant(artist))
songsModel.setData(songsModel.index(row, 3), QVariant(album))
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
songsModel.submitAll()
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.
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:
db.transaction()
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]
query.prepare('INSERT INTO songs VALUES(:guid, :title, :artist, :album)')
query.bindValue(':guid', QVariant(QUuid.createUuid().toString()))
query.bindValue(':title', QVariant(title))
query.bindValue(':artist', QVariant(artist))
query.bindValue(':album', QVariant(album))
query.exec_()
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
db.commit()
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:
songsModel =QSqlTableModel()
songsModel.setTable("songs")
songsModel.setEditStrategy(QSqlTableModel.OnManual Submit)
it = QDirIterator(self.rootFolder, QDirIterator.Subdirectories);
while it.hasNext():
try:
path = it.next()
if path.endsWith('.mp3', Qt.CaseInsensitive):
items += 1
try:
audio = EasyID3(path)
artist = audio['artist'][0]
title = audio['title'][0]
album = audio['album'][0]
row = songsModel.rowCount()
songsModel.insertRow(row)
songsModel.setData(songsModel.index(row, 0), QVariant(QUuid.createUuid().toString()))
songsModel.setData(songsModel.index(row, 1), QVariant(title))
songsModel.setData(songsModel.index(row, 2), QVariant(artist))
songsModel.setData(songsModel.index(row, 3), QVariant(album))
except ID3NoHeaderError:
noHeaders += 1
except UnicodeDecodeError:
decodeError += 1
except IOError:
notFound += 1
except KeyError:
noTags += 1
except UnicodeEncodeError:
encodeError += 1
songsModel.submitAll()
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.