Question

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

Answer 1

Things to try:

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

and see if it helps...

Answer 2

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 ..

友情链接