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).
