QSqlTableModel.insertRecord() is very slow

I am using PyQt to insert records into a MySQL database. the code basically looks like

self.table = QSqlTableModel()
self.table.setTable('mytable')
while True:
  rec = self.table.record()
  values = getValueDictionary()
  for k,v in values.items():
    rec.setValue(k,QVariant(v))
  self.table.insertRecord(-1,rec)

The table currently has ~ 50,000 rows in it. I have timed each line and found that the insertRecord function is taking ~5 seconds to execute, which is unacceptably slow. Everything else开发者_开发问答 is fast.

For comparison, I also made a version of the code that uses

QSqlQuery.prepare("INSERT INTO mytable (f1,f2,...) VALUES (:f1, :f2,...)")
query.bindValue(":f1",blah)
query.exec_()

In this case, the whole thing takes only ~ 20 milliseconds, so the delay is not in the database connection as far as I can tell.

I'd really prefer to use the QtSql stuff instead of the awkward MySQL commands. Any ideas on how to add a bunch of rows to a MySQL database with QtSql instead of raw comands and with reasonable speed?

Thanks, G

Things to try:

• set your EditStrategy to QSqlTableModel.OnManualSubmit
• mass insert rows with insertRows

and see if it helps...

you should use begin before and commit after the loop, or turn off the autocommit feature from MySQL .. this will give you usually a performance increase of 50% or more ..