Convenient Way to Create Offline/Disconnected data from QtSQL?

Question

I'm trying to figure out how to grab 2D data from a database and stuff it into widgets. This data is quasistatic -- once I grab it there is no need to stay connected to the database. Further, if I leave the connection open, but it times out, it can crash my app. I'd like to know if there is a some kind of offline data container or functionality in QtSql that I am not aware of.

As near as I can figure, Qt only offers QsqlTableModel and QAbstractTableModel as containers for data. I have not found any methods for keeping data in QsqlTableModel if the connection is dropped. And QAbstractTableModel is not even usable by itself; you have to subclass it. It is likely that I will end up going the subclassing route if I cannot find a more simple or elegant solution. There is a subclassing example here.

By way of code samples, the code below fills two combo boxes from a SQL Server database. When the second button is clicked and the second combobox is filled, the first combobox breaks and the application breaks. I'm hoping there is a simple way to grab the data and keep it in a local container that is disconnected from the database.

from PyQt5.QtWidgets import (QApplication, QMainWindow, QComboBox, QPushButton,
                             QTableView, QTableView)
from PyQt5.QtSql import (QSqlQuery, QSqlQueryModel, QSqlDatabase)
import sys


class MainWindow(QMainWindow):
    def __init__(self, parent=None):
        super(MainWindow, self).__init__()

        self.setGeometry(300, 300, 600, 350)

        self.db = QSqlDatabase.addDatabase("QODBC", 'MyDB')
        self.db.setDatabaseName('Driver={SQL Server};Server=MyServer;Database=MyDB;Trusted_Connection=Yes;')

        self.cb1 = QComboBox(parent=self)
        self.cb1.setGeometry(25,25, 250, 50)

        self.cb2 = QComboBox(parent=self)
        self.cb2.setGeometry(300,25, 250, 50)

        self.button1 = QPushButton('^^^ Fill Table 1 ^^^', parent=self)
        self.button1.setGeometry(55,290, 200, 30)
        self.button1.clicked.connect(self.fillTable1)

        self.button2 = QPushButton('^^^ Fill Table 2 ^^^', parent=self)
        self.button2.setGeometry(320, 290, 200, 30)
        self.button2.clicked.connect(self.fillTable2)

    def fillTable1(self):
        print('self.db.open() ', self.db.open())
        sql = 'select * from aaa.car limit 10'
        query = QSqlQuery(self.db)
        print("query.exec_(sql) ", query.exec_(sql))

        self.t1model = QSqlQueryModel(parent = self)
        self.t1model.setQuery(query)
        self.cb1.setModel(self.t1model)
        self.cb1.setModelColumn(0)
        self.cb1.setView(QTableView(self.cb1))

    def fillTable2(self):
        print('self.db.open() ', self.db.open())
        sql = 'select * from aaa.car limit 10'
        query = QSqlQuery(self.db)
        print("query.exec_(sql) ", query.exec_(sql))
        self.t2model = QSqlQueryModel(parent = self)
        self.t2model.setQuery(query)
        self.cb2.setModel(self.t2model)
        self.cb2.setModelColumn(0)
        self.cb2.setView(QTableView(self.cb2))


app = QApplication(sys.argv)
main = MainWindow(None)
main.show()
sys.exit(app.exec_())

Answer 1

Here is a barebones subclassed QAbstractTableModel. You feed it a QSqlQuery in the fillFromQuery method, and it extracts the data from the query result. As written, it does not play nice with QCompleter. Edited. Now works with QCompleter

from PyQt5.QtCore import Qt, QAbstractTableModel
from PyQt5.QtSql import QSqlQueryModel

class OfflineTableModel(QAbstractTableModel):
def __init__(self, parent, inputQuery=None):
    QAbstractTableModel.__init__(self, parent)
    self.mylist = []
    self.header = []
    
    if inputQuery is not None:
        self.fillFromQuery(inputQuery)

def fillFromQuery(self, inputQuery):
    # don't know how to get row/column count except from a QSqlQueryModel
    bogusModel = QSqlQueryModel()
    bogusModel.setQuery(inputQuery)
    rowCount = bogusModel.rowCount()
    colCount = bogusModel.columnCount()

    inputQuery.first()
    
    self.header = []
    for col in range(colCount):
        self.header.append(inputQuery.record().fieldName(col))

    self.mylist = []
    for row in range(rowCount):
        innerList = []
        for col in range(colCount):
            innerList.append(inputQuery.value(col))
        self.mylist.append(tuple(innerList))
        inputQuery.next()

def rowCount(self, parent=None):
    return len(self.mylist)
def columnCount(self, parent=None):
    return len(self.mylist[0])
def data(self, index, role):
    if not index.isValid():
        return None
    elif role not in (Qt.EditRole, Qt.DisplayRole):
        return None
    return self.mylist[index.row()][index.column()]
def dataRowCol(self, row, col):
    return self.mylist[row][col]
def headerData(self, col, orientation, role):
    if orientation == Qt.Horizontal and role not in (Qt.EditRole, Qt.DisplayRole):
        return self.header[col]
    return None

The advantage of this technique over making a local Sqlite database is that this OfflineTableModel will provide offline data to a view widget from any SQL SELECT query.

When this OfflineTable model is applied to the OP, the original QSqlDatabase (db) can be open and closed multiple times without breaking the widgets that are already loaded with offline data. 41 lines of code. Seems like there should be an easier way to accomplish the same thing. . .