Commit QSqlRelationalTableModel model changes to database

Question

I've modified the relational table model at https://doc.qt.io/qt-5/qtsql-relationaltablemodel-relationaltablemodel-cpp.html

I want to be able to save my changes to my database, but it only saves the first two columns, not the primary key lookup row. Also, I can't delete the rows. I've searched but haven't found any information about how to do it. What am I doing wrong?

Sample data I use is:

CREATE TABLE `category` ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `DrinkType` TEXT NOT NULL);
INSERT INTO `category` VALUES (1,'Non-alcoholic');
INSERT INTO `category` VALUES (2,'Alcoholic');
CREATE TABLE "drinks" ( `Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `Name` TEXT NOT NULL, `Type` INTEGER );
INSERT INTO `drinks` VALUES (1,'Coca Cola',1);
INSERT INTO `drinks` VALUES (2,'Pilsner',2);

My code:

def addRecord():
    model.insertRow(model.rowCount())

def delRecord():
    model.removeRow(view.currentIndex().row())
    model.select()

app = QtWidgets.QApplication(sys.argv)

window = QtWidgets.QWidget()
window.setWindowTitle("QRelationalSqlTableModel")

con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName('drinks.db')
con.open()

model = QtSql.QSqlRelationalTableModel(parent = window)
model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
model. setTable('drinks')
model.setRelation(2, QtSql.QSqlRelation('category', 'id', 'drinktype'))
model.select()

model.setHeaderData(1, QtCore.Qt.Horizontal, 'Name')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Type of drink')

vbox = QtWidgets.QVBoxLayout()

view = QtWidgets.QTableView()
view.setModel(model)
view.setItemDelegate(QtSql.QSqlRelationalDelegate(view))
view.hideColumn(0)
view.setColumnWidth(1, 150)
view.setColumnWidth(2, 150)

vbox.addWidget(view)
btnAdd = QtWidgets.QPushButton("&Add record")
btnAdd.clicked.connect(addRecord)
vbox.addWidget(btnAdd)
btnDel = QtWidgets.QPushButton("&Delete record")
btnDel.clicked.connect(delRecord)
vbox.addWidget(btnDel)

window.setLayout(vbox)
window.resize(430, 250)
window.show()
sys.exit(app.exec_())

Answer 1

One of the problems is because you are using an inappropriate strategy, if you use OnManualSubmit you must call the method submitAll() so that the changes are recorded in the database, if you do not want to do it it is better to use OnRowChange, with this strategy the changes will be made when after adding the pressures values ​​enter or select another row.

If you want to delete a row from the database you must use deleteRowFromTable(), you are using removeRow() but this only removes the row in the view but not in the database so an instant later the data is refreshed showing itself new the row.

def addRecord():
    model.insertRow(model.rowCount())
    view.scrollToBottom()

def delRecord():
    model.deleteRowFromTable(view.currentIndex().row())
    model.select()

app = QtWidgets.QApplication(sys.argv)

window = QtWidgets.QWidget()
window.setWindowTitle("QRelationalSqlTableModel")

con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName('drinks.db')
con.open()

model = QtSql.QSqlRelationalTableModel(parent = window)
model.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
model. setTable('drinks')
model.setRelation(2, QtSql.QSqlRelation('category', 'id', 'drinktype'))
model.select()

model.setHeaderData(1, QtCore.Qt.Horizontal, 'Name')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Type of drink')

vbox = QtWidgets.QVBoxLayout()

view = QtWidgets.QTableView()
view.setModel(model)
view.setItemDelegate(QtSql.QSqlRelationalDelegate(view))
view.hideColumn(0)
view.setColumnWidth(1, 150)
view.setColumnWidth(2, 150)

vbox.addWidget(view)
btnAdd = QtWidgets.QPushButton("&Add record")
btnAdd.clicked.connect(addRecord)
vbox.addWidget(btnAdd)
btnDel = QtWidgets.QPushButton("&Delete record")
btnDel.clicked.connect(delRecord)
vbox.addWidget(btnDel)

window.setLayout(vbox)
window.resize(430, 250)
window.show()
sys.exit(app.exec_())