Editable QTableView of complex SQL query

Question

How can I make an editable QTableView displaying data from a complex SQLite query?

I need to fill a QTableView with data from several SQLite tables. This needs to be editable by the user.

As the queries are a bit complex (including JOINs and CASE WHEN etc.), I'm doing this via a QSqlTableModel and a QSqlQuery. I have been told, however, that this is not how QSqlTableModels should be used. So, can someone please show me how to get a result like the one shown here via the proper way?

Also, while my QTableViews are editable, the results don't seem to get stored in the SQLite database. (When I comment out fill_tables, I still get the original results after restarting the GUI. Changing the EditStrategy to OnFieldChange doesn't help.) Is that because I'm handling QSqlTableModel wrong?

#!/usr/bin/python3

from PyQt5.QtSql import (QSqlDatabase, QSqlQuery, QSqlTableModel, 
                         QSqlRelationalTableModel, QSqlRelation)
from PyQt5.QtWidgets import QTableView, QApplication
from PyQt5.Qt import QSortFilterProxyModel
import sys

db_file = "test.db"

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


def fill_tables():
    q = QSqlQuery()
    q.exec_("DROP TABLE IF EXISTS Manufacturers;")
    q.exec_("CREATE TABLE Manufacturers (Company TEXT, Country TEXT);")
    q.exec_("INSERT INTO Manufacturers VALUES ('VW', 'Germany');")
    q.exec_("INSERT INTO Manufacturers VALUES ('Honda' , 'Japan');")

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


class CarTable(QTableView):
    def __init__(self):
        super().__init__()
        self.init_UI()
        self.create_model()

    def create_model(self):
        query = """
        SELECT (comp.company || " " || cars.model) as Car,
                comp.Country,
                (CASE WHEN cars.Year > 2000 THEN 'yes' ELSE 'no' END) as this_century
        from manufacturers comp left join cars
            on comp.company = cars.company
        """
        raw_model = QSqlTableModel()
        q = QSqlQuery()
        q.exec_(query)
        self.check_error(q)
        raw_model.setQuery(q)

        self.model = QSortFilterProxyModel()
        self.model.setSourceModel(raw_model)
        self.setModel(self.model)

        # filtering:
        self.model.setFilterKeyColumn(0)
        self.model.setFilterFixedString('VW')

    def init_UI(self):
        self.resize(500,300)

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


def main():
    mydb = create_connection(db_file)
    if not mydb:
        sys.exit(-1)
    fill_tables()
    app = QApplication(sys.argv)
    ex = CarTable()
    ex.show()
    result = app.exec_()

    if (mydb.open()):
        mydb.close()

    sys.exit(result)


if __name__ == '__main__':
    main()

I have tried using a QSqlRelationalTableModel instead, but I can't get the same complexity of queries done, and it also doesn't save the changes, just like the code above. This is as far as I got on that attempt:

     def create_model_alternative(self):
        self.model = QSqlRelationalTableModel()
        self.model.setTable("Cars")
        self.model.setRelation(0, QSqlRelation("Manufacturers", "Company",
                                               "Company, Country"))  
        self.setModel(self.model)
        self.model.select()

        # filtering:
        self.model.setFilter("cars.Company = 'VW'")

---

To answer incoming questions:

Editability:

In this example, the only column that is definitely required to be editable (in such a way that changes arrive in the database) is the Country column (and changes there should affect all other rows that share the same content; e.g., if you change 'Germany' to 'France' for either VW-car, both should then list 'France' as country).

If you know a way to get the first one editable too, so that the respective columns in the database are updated, that would be really nifty to see, but it's not a requirement. (In my real tables, I use such 'column-joins' for uneditable fields.) In this specific case, I would expect a change of 'VW Polo' to 'Marco Polo' to also update 'VW Golf' to 'Marco Golf', as the column used in the column-join is the manufacturers.company and not cars.company. (In reality, one would probably use cars.company for the join though, in which case 'VW Golf' would remain unchanged. But let's assume the query as given above.)

The third column is meant as an example of a calculated stat result, and these are generally only for reading (editing them would not make sense).

Column Order:

I would highly appreciate being able to choose the order in which the columns are displayed, even across joined tables (like I could do with the Query).

Answer 1

QSqlTableModel is a class that inherits from QSqlQueryModel, so it can be said that QSqlTableModel is a specialized QSqlQueryModel to edit a table, so it can be limited or oversized.

For this special case I am proposing a QSqlQueryModel Editable, for this I have made the following changes:

• I have enabled the flag Qt.ItemIsEditable for the second column.

• I have overwritten the setData() method to update the Manufacturers table.

• I have added a column that represents the company, this will be hidden but it is useful to obtain the rows that have to be changed in the previous change.

• I have implemented the setFilter() method to make filters.

---

import sys

from PyQt5.QtCore import Qt
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
from PyQt5.QtWidgets import QApplication, QTableView

db_file = "test.db"


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


def fill_tables():
    q = QSqlQuery()
    q.exec_("DROP TABLE IF EXISTS Manufacturers;")
    q.exec_("CREATE TABLE Manufacturers (Company TEXT, Country TEXT);")
    q.exec_("INSERT INTO Manufacturers VALUES ('VW', 'Germany');")
    q.exec_("INSERT INTO Manufacturers VALUES ('Honda' , 'Japan');")

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


class SqlQueryModel(QSqlQueryModel):
    def flags(self, index):
        fl = QSqlQueryModel.flags(self, index)
        if index.column() == 1:
            fl |= Qt.ItemIsEditable
        return fl

    def setData(self, index, value, role=Qt.EditRole):
        if index.column() == 1:
            company = self.index(index.row(), 2).data()
            q = QSqlQuery("UPDATE Manufacturers SET Country = '{}' WHERE Company =  '{}'".format(value, company))
            result = q.exec_()
            if result:
                self.query().exec_()
            else:
                print(self.query().lastError().text())
            return result
        return QSqlQueryModel.setData(self, index, value, role)

    def setFilter(self, filter):
        text = (self.query().lastQuery() + " WHERE " + filter)
        self.setQuery(text)


query = '''
        SELECT (comp.company || " " || cars.model) as Car,
                comp.Country,
                cars.company,
                (CASE WHEN cars.Year > 2000 THEN 'yes' ELSE 'no' END) as this_century
        from manufacturers comp left join cars
            on comp.company = cars.company
        '''

if __name__ == '__main__':
    app = QApplication(sys.argv)
    if not create_connection(db_file):
        sys.exit(-1)

    fill_tables()

    view = QTableView()

    model = SqlQueryModel()
    q = QSqlQuery(query)
    model.setQuery(q)
    model.setFilter("cars.Company = 'VW'")
    view.setModel(model)
    view.hideColumn(2)
    view.show()
    sys.exit(app.exec_())

Answer 2

Based on the great specific solution by @eyllanesc, I have made a generalized version of a QSqlQueryModel where it's possible to specify which columns should be editable. It might need adjustments for other people's queries, but I hope it's helpful to someone out there struggling with similar problems:

import sys

from PyQt5.QtCore import Qt
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
from PyQt5.QtWidgets import QApplication, QTableView

db_file = "test.db"


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


def fill_tables():
    q = QSqlQuery()
    q.exec_("DROP TABLE IF EXISTS Manufacturers;")
    q.exec_("CREATE TABLE Manufacturers (Company TEXT, Country TEXT);")
    q.exec_("INSERT INTO Manufacturers VALUES ('VW', 'Germany');")
    q.exec_("INSERT INTO Manufacturers VALUES ('Honda' , 'Japan');")

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


class SqlQueryModel_editable(QSqlQueryModel):
    """a subclass of QSqlQueryModel where individual columns can be defined as editable
    """
    def __init__(self, editables):
        """editables should be a dict of format: 
        {INT editable_column_nr : (STR update query to be performed when changes are made on this column
                                   INT model's column number for the filter-column (used in the where-clause),
                                   )} 
        """
        super().__init__()
        self.editables = editables

    def flags(self, index):
        fl = QSqlQueryModel.flags(self, index)
        if index.column() in self.editables:
            fl |= Qt.ItemIsEditable
        return fl

    def setData(self, index, value, role=Qt.EditRole):
        if role == Qt.EditRole:
            mycolumn = index.column()
            if mycolumn in self.editables:
                (query, filter_col) = self.editables[mycolumn]
                filter_value = self.index(index.row(), filter_col).data()
                q = QSqlQuery(query.format(value, filter_value))
                result = q.exec_()
                if result:
                    self.query().exec_()
                else:
                    print(self.query().lastError().text())
                return result
        return QSqlQueryModel.setData(self, index, value, role)

    def setFilter(self, myfilter):
        text = (self.query().lastQuery() + " WHERE " + myfilter)
        self.setQuery(text)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    if not create_connection(db_file):
        sys.exit(-1)

    fill_tables()

    view = QTableView()

    editables = {1 : ("UPDATE Manufacturers SET Country = '{}' WHERE Company = '{}'", 2)}
    model = SqlQueryModel_editable(editables)
    query = '''
        SELECT (comp.company || " " || cars.model) as Car,
                comp.Country,
                cars.company,
                (CASE WHEN cars.Year > 2000 THEN 'yes' ELSE 'no' END) as this_century
        from manufacturers comp left join cars
            on comp.company = cars.company
        '''
    q = QSqlQuery(query)
    model.setQuery(q)
    model.setFilter("cars.Company = 'VW'")
    view.setModel(model)
    view.hideColumn(2)
    view.show()
    sys.exit(app.exec_())

To make joined columns editable would require more work and a different format for editables, but this should work on any columns that don't hold joined/calculated/aggregated data (like 'Country' in this example).