How can I query asynchronously via a QSqlQueryMode

Question

I wish to query a SQL database via QSqlQueryModel (PyqQt 5/Qt 5.2) asynchronously, so that the GUI doesn't block. How can this be accomplished? Maybe through multithreading? Please provide code of how to do this. If using QSqlQueryModel asynchronously isn't practical, feel free to provide alternatives (should be usable with QTableView though).

My (synchronous) code currently looks as shown beneath. The main script bin/app.py loads gui/__init__.py and executes its main method. That in turn uses gui.models.Table to load data from the database. The problem is that gui.models.Table queries the database synchronously and locks up the GUI in the meantime.

bin/app.py:

import os.path
import sys

sys.path.insert(0, os.path.abspath(os.path.join(
    os.path.dirname(__file__), "..")))

import gui


if __name__ == "__main__":
    gui.main()

gui/__init__.py:

import sys
import os.path
from PyQt5 import uic
from PyQt5 import QtCore, QtWidgets

from gui import models


class MainWindow(QtWidgets.QMainWindow):
    def __init__(self):
        super(MainWindow, self).__init__()
        uic.loadUi(os.path.join(os.path.dirname(__file__), 'app.ui'), self)
        self.tableView.setModel(models.Table(self))


def main():
    app = QtWidgets.QApplication(sys.argv)
    w = MainWindow()
    w.show()
    app.exec_()

gui/models.py:

import os.path
from PyQt5.QtCore import *
from PyQt5.QtSql import *


class Table(QSqlQueryModel):
    def __init__(self, parent=None):
        super(Table, self).__init__(parent)

        pth = os.path.abspath(os.path.join(os.path.dirname(__file__), "..",
                                           "test.sqlite"))
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(pth)
        if not db.open():
            raise Exception("Couldn't open database '{}'".format(pth))
        try:
            self.setQuery("select * from Test")
        finally:
            db.close()

Answer 1

Unfortunately, a typical database driver that Qt (or anyone else, really) uses is synchronous. Qt views unfortunately don't know how to deal with models in foreign threads.

The solution thus requires a shim proxy model, subclassing QIdentityProxyModel. The first step in the implementation is to shim all of the source model's method calls with blocking QMetaObject::invokeMethod calls. This is needed just to be correct, if not asynchronous just yet. It' just to expose a safe interface to a model that lives in another thread.

The next step is to provide an asynchronous veneer over some of the functionality. Suppose that you want to make the data method asynchronous. What you do is:

1. For each role, have a cache of variant values keyed by the model index.

2. On the dataChanged signal from the source model, cache all the values that were changed, across all roles. The data call needs to be queued in the model's thread - more on that later.

3. In data, if there's a cache hit, return it. Otherwise return a null variant and queue the data call in the model's thread.

Your proxy should have a private method called cacheData that will be called from the queued calls. In another answer, I've detailed how to queue functor calls in another thread. Leveraging that, your data call queuing method can look like:

void ThreadsafeProxyModel::queueDataCall(const QModelIndex & index, int role) {
  int row = index.row();
  int column = index.column();
  void * data = index.internalPointer();
  postMetacall(sourceModel()->thread(), [this, row, column, data, role]{
    QVariant data = sourceModel()->data(createIndex(row, column, data), role);
    QMetaObject::invoke(this, "cacheData", 
                        Q_ARG(QVariant, data), Q_ARG(int, role),
                        Q_ARG(int, row), Q_ARG(int, column), Q_ARG(void*, data));
  });
}

This is just a sketch. It'd be fairly involved, but certainly doable, and still maintaining the semantics of a real model.