Query makes table with QTableView and QSqlTableModel uneditable

Question

I need to perform a query to limit the rows displayed in a table. I chose QTableView/QSqlTableModel for the read-write functionality. Unfortunately, my query makes the table uneditable.

Edit: This is not my real program. I will be using a few tables and a relational division query to determine the rows to be displayed. I do need to use a query.

How do I execute a query and keep the read-write functionality?

import sys
from PyQt5.QtWidgets import QApplication, QTableView
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery

def createDb():
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("temp.db")
    if not db.open():
        print("Cannot establish a database connection.")
        return False
    query = QSqlQuery()
    query.exec_("DROP TABLE IF EXISTS customers")
    query.exec_("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY NOT NULL, "
                "customer CHAR NOT NULL)")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 1')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 2')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 3')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 4')")

    return True

class MainForm(QTableView):
    def __init__(self):
        super().__init__()

        self.model = QSqlTableModel(self)
        self.model.setTable("customers")

        # This query results in a non-editable table.
        self.query = QSqlQuery("SELECT customer FROM customers WHERE customer_id = 2")
        self.model.setQuery(self.query)

        self.view = QTableView(self)
        self.view.setModel(self.model)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    createDb()
    w = MainForm()
    w.show()
    sys.exit(app.exec_())

Answer 1

@eyllanesc Thank you for leading me towards using setFilter.

I've created something that seems to work. I converted the query results to a string. I then used that string to construct a SQL argument to be used with setFilter.

Elegant? Pythonic? I'm not sure.

import sys
from PyQt5.QtWidgets import QApplication, QTableView
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery

def createDb():
    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("temp.db")
    if not db.open():
        print("Cannot establish a database connection.")
        return False
    query = QSqlQuery()
    query.exec_("DROP TABLE IF EXISTS customers")
    query.exec_("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY NOT NULL, "
                "customer CHAR NOT NULL)")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 1')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 2')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 3')")
    query.exec_("INSERT INTO customers (customer) VALUES ('Customer 4')")

    return True

class MainForm(QTableView):
    def __init__(self):
        super().__init__()

        self.model = QSqlTableModel(self)
        self.model.setTable("customers")

        self.query = QSqlQuery()
        self.query.exec("SELECT customer_id FROM customers WHERE customer_id IN (2,4)")

        # Convert query results to string.
        self.ls = list()
        while self.query.next():
            self.ls.append(self.query.value(0))
        self.ls_string = ','.join(map(str, self.ls))

        # Create setFiler argument.
        self.filter_criteria = "customer_id IN " + "(" + self.ls_string + ")"

        self.model.setFilter(self.filter_criteria)
        self.model.select()

        self.view = QTableView(self)
        self.view.setModel(self.model)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    createDb()
    w = MainForm()
    w.show()
    sys.exit(app.exec_())