Display data from joined SQLite tables using PyQt5.QtSql?

Question

I'm trying to build a GUI using PyQT5 (under Python3) which, among other things, should display and allow interaction with data from joined tables from an SQLite database.

I think this needs the QSqlRelationalTableModel class. I have found documentation how to replace keys with human-readable stuff from another table.

But how can I display data from joined SQLite tables using PyQt5.QtSql?( Since QSqlRelationalTableModel seems to have a JoinMode, I think this must be possible, but I cannot get it to work.)

Here's a mini-example (the problematic part is the createModel() method):

#!/usr/bin/python3
# -*- coding: utf-8 -*-

from PyQt5 import QtSql
from PyQt5.QtWidgets import (QMainWindow, QTableView, QApplication, QAbstractItemView)
from PyQt5.QtCore import Qt
import sys

class Example(QMainWindow):
    def __init__(self):
        super().__init__()
        self.resize(500, 150)

        self.createConnection()
        self.fillTable()
        self.createModel()
        self.initUI()

    def createConnection(self):
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("test.db")
        if not self.db.open():
            print("Cannot establish a database connection")
            return False

    def fillTable(self):
        self.db.transaction()
        q = QtSql.QSqlQuery()
        q.exec_("DROP TABLE IF EXISTS Manufacturers;")
        q.exec_("CREATE TABLE Manufacturers (CompanyId INT PRIMARY KEY, Name TEXT, Country TEXT);")
        q.exec_("INSERT INTO Manufacturers VALUES (1, 'VW', 'Germany');")
        q.exec_("INSERT INTO Manufacturers VALUES (2, 'Honda' , 'Japan');")

        q.exec_("DROP TABLE IF EXISTS Cars;")
        q.exec_("CREATE TABLE Cars (Model TEXT, Year INT, Company INT);")
        q.exec_("INSERT INTO Cars VALUES ('Civic', 2009, 2);")
        q.exec_("INSERT INTO Cars VALUES ('Golf', 2013, 1);")
        q.exec_("INSERT INTO Cars VALUES ('Polo', 1999, 1);")

        self.db.commit()

    def createModel(self):
        self.model = QtSql.QSqlRelationalTableModel()
        self.model.setTable("Cars")

        self.model.setHeaderData(0, Qt.Horizontal, "Model")
        self.model.setHeaderData(1, Qt.Horizontal, "Year")
        self.model.setHeaderData(2, Qt.Horizontal, "Company")
        self.model.setHeaderData(3, Qt.Horizontal, "Country") #FIXME

        self.model.setRelation(2, QtSql.QSqlRelation("Manufacturers", 
            "CompanyId", "Name"))

        self.model.select()


    def initUI(self):
        self.view = QTableView()
        self.view.setModel(self.model)

        mode = QAbstractItemView.SingleSelection
        self.view.setSelectionMode(mode)

        self.setCentralWidget(self.view) 

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


def main():
    app = QApplication([])
    ex = Example()
    ex.show()
    sys.exit(app.exec_())


if __name__ == '__main__':
    main()

Running this shows the Cars table, with the CompanyId nicely replaced with the Company name from the Manufacturers table. But what must I do to also show the corresponding entry of the Country column of the Manufacturers table? (I.e., I'd like both tables joined on the CompanyId columns, and then display data from the joined table in the GUI.)

Answer 1

you can add further fieldnames from the manufacturers table to the third expression of setRelation:

self.model.setRelation(2, QtSql.QSqlRelation("Manufacturers", "CompanyId", "Name, Country"))