QSqltable canFetchMore advice

mike2750 | 2020-07-23 03:53:32 UTC | #1

I noticed this odd issue a few weeks ago and only recently found out the why and wondering if anyone has any pyqt5 snippets for qsortfilter model with canfetchmore.

References i could find for regular QT https://stackoverflow.com/questions/27372078/qtableview-export-to-csv-number-of-rows-fetched-is-limited-to-only-256/27372480#27372480 https://stackoverflow.com/questions/50181951/qtableview-not-showing-all-of-the-data-from-an-sqlite-database

Basically unless i load a further section in the DB first and then try again my search which has stuff in it its missing results. Was super frustrating as it took awhile to find out how to trigger the whole DB but i'd like to properly implement it so it shows all the stuff.

Excerpt of my current bits for QSqltable and its using sqlite driver for the DB

In Main

python
        self.commandcategorydropdown.setCurrentText('All')

        db.open()
        self.model = QSqlTableModel()
        self.initializedModel()
        #
        # self.commands.tableView = QTableView()
        # self.commands.tableView.setModel(self.model)
        #  self.tableView = QTableView(self.commandsbox)
        self.tableView.verticalHeader().setVisible(False)
        # self.tableView.resizeColumnsToContents()
        # self.tableView.resizeRowsToContents()
        # self.tableView.horizontalHeader().setVisible(False)
        # self.tableView.horizontalHeader().setDefaultAlignment(Qt.AlignLeft)
        self.tableView.horizontalHeader().setStretchLastSection(True)
        self.source_model = self.model
        # self.initializedModel()
        self.proxy_model = QSortFilterProxyModel(self.source_model)
        self.proxy_model.setSourceModel(self.source_model)
        self.tableView.setModel(self.proxy_model)
        self.tableView.hideColumn(0)
        self.tableView.hideColumn(1)
        self.tableView.hideColumn(3)
        self.tableView.hideColumn(4)
        self.tableView.hideColumn(5)
        self.tableView.hideColumn(6)
        self.tableView.hideColumn(7)
        # self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
        #                                         QRegExp.FixedString))

        # self.proxy_model.setFilterKeyColumn(-1)
        self.tableView.setSortingEnabled(True)
        self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
        # self.tableView.setWordWrap(True)
        # self.command_description = QLabel()
        # self.command_description.setWordWrap(True)
        # self.command_requires_label = QLabel('')
        # self.command_requires_label.setWordWrap(True)
        # self.verticalLayout_8.addWidget(self.commandsbox)
        # self.verticalLayout_8.addWidget(self.tableView)
        # self.verticalLayout_8.addWidget(self.searchcommands)
        # self.verticalLayout_8.addWidget(self.command_requires_label)
        # self.verticalLayout_8.addWidget(self.command_description)
        self.commandcategorydropdown.activated.connect(self.current_activated_category)

    def on_searchcommands_update(self):
        self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
                                                 QRegExp.FixedString))
        self.proxy_model.setFilterKeyColumn(-1)


    def current_activated_category(self):
        global command_category
        command_category = str(self.commandcategorydropdown.currentText())
        print(str(self.commandcategorydropdown.currentText()))
        # db.open()
        # self.commandslist.projectModel.setQuery(
        #     QtSql.QSqlQuery("SELECT command_alias, command FROM commands WHERE category = '%s'" % command_category))
        # self.commandslist.projectView = QListView(self.commandslist)
        # self.commandslist.projectView.setModel(self.commandslist.projectModel)
        # db.close()
        self.command_description.setText('')
        self.command_requires_label.setText('')
        self.searchcommands.setText('')

        if command_category == 'All':
            self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
                                                     QRegExp.FixedString))
            self.proxy_model.setFilterKeyColumn(-1)
        else:
            self.proxy_model.setFilterRegExp(QRegExp(self.commandcategorydropdown.currentText(), Qt.CaseSensitive,
                                                     QRegExp.FixedString))
            self.proxy_model.setFilterKeyColumn(1)
        # db.close()

    def on_searchcommands_update(self):
        self.proxy_model.setFilterRegExp(QRegExp(self.searchcommands.text(), Qt.CaseInsensitive,
                                                 QRegExp.FixedString))
        self.proxy_model.setFilterKeyColumn(-1)


    def initializedModel(self):
        self.model.setTable("commands")
        # self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()
        self.model.setHeaderData(0, Qt.Horizontal, "ID")
        self.model.setHeaderData(1, Qt.Horizontal, "Category")
        self.model.setHeaderData(2, Qt.Horizontal, "command_alias")
        self.model.setHeaderData(3, Qt.Horizontal, "command")
        self.model.setHeaderData(4, Qt.Horizontal, "requires")
        self.model.setHeaderData(5, Qt.Horizontal, "description")
        self.model.setHeaderData(6, Qt.Horizontal, "controlpanel")
        self.model.setHeaderData(7, Qt.Horizontal, "verification")

    def onAddRow(self):
        self.model.insertRows(self.model.rowCount(), 1)
        self.model.submit()

    def onDeleteRow(self):
        self.model.removeRow(self.tableView.currentIndex().row())
        self.model.submit()
        self.model.select()

    def closeEvent(self, event):
        db.close()

Any advice or guidance would be greatly appreciated. The 'current_activated_category' and 'on_searchcommands_update' function is what i use to filter results by qcombobox and lineedit so would need to ideally work when these are called.

Packaging Python Applications with PyInstaller by Martin Fitzpatrick — This step-by-step guide walks you through packaging your own Python applications from simple examples to complete installers and signed executables.

More info Get the book


mike2750 | 2020-07-24 13:40:35 UTC | #2

Ended up getting this problem sorted by using the below snippet at the end of the "initializedModel" function. Now loads all into and works on start without having to trigger manual stuff in the UI

PyQt/PySide 1:1 Coaching with Martin Fitzpatrick — Save yourself time and frustration. Get one on one help with your Python GUI projects. Working together with you I'll identify issues and suggest fixes, from bugs and usability to architecture and maintainability.

Book Now 60 mins ($195)

python
def initializedModel(self):
python
    while self.model.canFetchMore():
        self.model.fetchMore()
    self.model.rowCount()

leaving this here in case anyone else runs into the same issue.


Over 15,000 developers have bought Create GUI Applications with Python & Qt!
Create GUI Applications with Python & Qt6
Take a look

Downloadable ebook (PDF, ePub) & Complete Source code

Also available from Leanpub and Amazon Paperback

Mark As Complete
Martin Fitzpatrick

QSqltable canFetchMore advice was written by Martin Fitzpatrick .

Martin Fitzpatrick has been developing Python/Qt apps for 8 years. Building desktop applications to make data-analysis tools more user-friendly, Python was the obvious choice. Starting with Tk, later moving to wxWidgets and finally adopting PyQt. Martin founded PythonGUIs to provide easy to follow GUI programming tutorials to the Python community. He has written a number of popular Python books on the subject.