Inverting columns and rows in QTableView using PyQt5

Question

For a PyQT5 widget, I need to display data from an SQL-query to an SQLite database with columns and rows inverted/rotated. Ideally, in a QTableView. (This table will only have 2 columns, one for the previous column names and one for their values. The table is meant to show stats which will be aggregated in the SQL query, which will return only one row. So I want to go from one row with multiple columns, to 2 columns with multiple rows.)

I have come up with a workaround that does the right thing using a QFormLayout instead, but it looks ugly and seems very inelegant. (See the display_data(self) method.)

#!/usr/bin/python3

from PyQt5 import QtSql
from PyQt5.QtWidgets import (QFormLayout, QWidget, 
                             QLabel, QLineEdit, QApplication)

import sys

class InvertedTable(QWidget):
    def __init__(self, company):
        super().__init__()
        self.db_file = "test.db"
        self.company = company
        self.create_connection()
        self.fill_table()
        self.init_UI()
        self.display_data()

    def create_connection(self):
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName(self.db_file)
        if not self.db.open():
            print("Cannot establish a database connection to {}!".format(self.db_file))
            return False

    def fill_table(self):
        self.db.transaction()
        q = QtSql.QSqlQuery()
        q.exec_("DROP TABLE IF EXISTS Cars;")
        q.exec_("""CREATE TABLE Cars (Company TEXT, Model TEXT, Cars TEXT)""") 
        q.exec_("INSERT INTO Cars VALUES ('Honda', 'Civic', 5)") 
        q.exec_("INSERT INTO Cars VALUES ('Volkswagen', 'Golf', 3)")
        self.db.commit()

    def init_UI(self):
        self.resize(300,100)
        self.layout = QFormLayout()
        self.setLayout(self.layout)

    def display_data(self):
        query = "select * from cars where company = '{}'".format(self.company)
        q = QtSql.QSqlQuery()
        q.exec_(query)
        self.check_error(q)
        record = q.record()
        columns = record.count()
        q.next()
        for i in range(columns):
            column_name = record.field(i).name()
            col_field = QLabel(column_name, self)
            value = q.value(i)
            value_field = QLineEdit(self)
            value_field.setText(value)
            self.layout.addRow(col_field, value_field)

    def closeEvent(self, e):
        if (self.db.open()):
            self.db.close()

    def check_error(self, q):
        lasterr = q.lastError()
        if lasterr.isValid():
            print(lasterr.text())
            self.db.close()
            exit(1)


def main():
    app = QApplication(sys.argv)
    ex = InvertedTable("Honda")
    ex.show()

    result = app.exec_()
    sys.exit(result)


if __name__ == '__main__':
    main()      

What is the proper way to accomplish this using QTableView?

Answer 1

The proper way to work with QTableView would be to have a QTableModel.

As luck would have it, there is a QSqlTableModel that allows you to build a table model against a SQL table. Funt answered a similar question by pointing to QIdentityProxyModel that can be used "on top of that" to change the representation of a data model by redefining mapToSource and mapFromSource methods.

There are also ways to transpose the result of a SQL request directly from the SQL command. See here.

Also worth reading : Model-View Programming with Qt. It's the C++ version but PyQt follows the same principles (and the classes have the same name).

Hope that helps.

Answer 2

After some more searching and reading up on the helpful pointers left by @PlikPlok, I found a solution here:

Apparently, this functionality is not provided by any Qt-classes out of the box, so you have to subclass both QAbstractProxyModel and QSqlRelationalDelegate, and then use these on your table:

#!/usr/bin/python3

import sys
from PyQt5 import QtSql
from PyQt5.QtWidgets import (QWidget, QApplication,
                             QGridLayout, QTableView)
from PyQt5.Qt import (QModelIndex, QAbstractProxyModel, QSqlRelationalDelegate)
from PyQt5.QtCore import Qt

class FlippedProxyModel(QAbstractProxyModel):
    def __init__(self, parent=None):
        super().__init__(parent)

    def mapFromSource(self, index):
        return self.createIndex(index.column(), index.row())

    def mapToSource(self, index):
        return self.sourceModel().index(index.column(), index.row(), QModelIndex())

    def columnCount(self, parent):
        return self.sourceModel().rowCount(QModelIndex())

    def rowCount(self, parent):
        return self.sourceModel().columnCount(QModelIndex())

    def index(self, row, column, parent):
        return self.createIndex(row, column)

    def parent(self, index):
        return QModelIndex()

    def data(self, index, role):
        return self.sourceModel().data(self.mapToSource(index), role)

    def headerData(self, section, orientation, role):
        if orientation == Qt.Horizontal:
            return self.sourceModel().headerData(section, Qt.Vertical, role)
        if orientation == Qt.Vertical:
            return self.sourceModel().headerData(section, Qt.Horizontal, role)


class FlippedProxyDelegate(QSqlRelationalDelegate):
    def createEditor(self, parent, option, index):
        proxy = index.model()
        base_index = proxy.mapToSource(index)
        return super().createEditor(parent, option, base_index)

    def setEditorData(self, editor, index):
        proxy = index.model()
        base_index = proxy.mapToSource(index)
        return super().setEditorData(editor, base_index)

    def setModelData(self, editor, model, index):
        base_model = model.sourceModel()
        base_index = model.mapToSource(index)
        return super().setModelData(editor, base_model, base_index)


class InvertedTable(QWidget):
    def __init__(self, company):
        super().__init__()
        self.db_file = "test.db"
        self.company = company
        self.create_connection()
        self.fill_table()
        self.create_model()
        self.init_UI()

    def create_connection(self):
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName(self.db_file)
        if not self.db.open():
            print("Cannot establish a database connection to {}!".format(self.db_file))
            return False

    def fill_table(self):
        self.db.transaction()
        q = QtSql.QSqlQuery()
        q.exec_("DROP TABLE IF EXISTS Cars;")
        q.exec_("""CREATE TABLE Cars (Company TEXT, Model TEXT, Cars TEXT)""") 
        q.exec_("INSERT INTO Cars VALUES ('Honda', 'Civic', 5)") 
        q.exec_("INSERT INTO Cars VALUES ('Volkswagen', 'Golf', 3)")
        self.db.commit()

    def create_model(self):
        self.model = QtSql.QSqlTableModel()
        q = QtSql.QSqlQuery()
        query = """SELECT * from cars where company = 'Honda'
         """
        q.exec_(query)
        self.model.setQuery(q)
        self.proxy = FlippedProxyModel() # use flipped proxy model
        self.proxy.setSourceModel(self.model)

    def init_UI(self):
        self.grid = QGridLayout()
        self.setLayout(self.grid)
        self.table = QTableView()
        self.table.setModel(self.proxy)
        self.table.setItemDelegate(FlippedProxyDelegate(self.table)) # use flipped proxy delegate
        self.table.horizontalHeader().hide()

        self.grid.addWidget(self.table, 0, 0)

    def closeEvent(self, e):
        if (self.db.open()):
            self.db.close()

    def check_error(self, q):
        lasterr = q.lastError()
        if lasterr.isValid():
            print(lasterr.text())
            self.db.close()
            exit(1)


def main():
    app = QApplication(sys.argv)
    ex = InvertedTable("Honda")
    ex.show()

    result = app.exec_()
    sys.exit(result)


if __name__ == '__main__':
    main()