Using a progress handler in Python SQLite executemany query

Question

I've got a Python application with a long running SQLite query that I want to display progress for on a PyQT form.

A stripped down version of my code:

def run_long_query(self,)

    self.con = lite.connect(self.dbfname)
    self.con.set_progress_handler(self.progress_handler, 1)
    cur = self.con.cursor()
    vals = list(self.hash_iter(dirname, pattern))
    query = "INSERT OR REPLACE INTO hashval2fname (hashvalue, fname) VALUES(?, ?)"
    cur.executemany(query, vals)
    self.con.commit()
    self.con.close()

def progress_handler(self):
    print self.counter
    self.counter += 1
    self.pbar.setValue((self.pbar.value() + self.prog_step / 2))
    time.sleep(0.1)
    return 0  #  returning non-zero aborts

Everything works great but I get a rather arbitrary number of progress updates. For example let's say vals returns a list lenght 10. I set my progress bar to have an upper limit of 10, I'd expect the query to be run 10 times, and my progress_handler function to be called 10 times, during each of which it would update my progress bar one step.

Instead what I'm seeing is the number of calls into my progress handler (1 in the set_progress_handler call) is quite a bit more than 1 per query execute. For example in a case when my vals list has one item in it I'm see some number of call into progress_handler between 13-23 depending on what my specific query is. I wouldn't normally care but I don't know how to make the progress bar track meaningfully without knowing how many iterations to expect.

Answer 1

According to the documentation, the second parameter of set_progress_handler is the number of virtual machine instructions between successive invocations of the callback.

The instructions are generated by the query optimizer; the details are highly variable, and depend not only on the database structure, but also on the data itself, and the SQLite version. It is not possible to predict how many total instructions there will be, or even how many per row. (You can use EXPLAIN to show the instructions for a query, but that output is not very helpful for predicting how often the instructions are executed.)

You cannot show a percentage in the form; the best you can do is an animated waiting circle.