[FIXED] PyQt5. Row editing

Issue

I made PyQt5 APP which create SQlite3 DB and show data from it in QTableView widget. I have a problem with editing rows in a widget. There are 3 buttons "Add", "Change" and "Delete" that should delete, modify and add new rows to the widget, as well as edit the database itself, but the buttons do not work properly.

1. "Add" button - after clicking add button, when all new data inputted and Enter clicked, all values is gone and "!" symbol is showing. I need press Add button, input new data in cells, click Enter and all data must save in SQL DB and displayed in QTableView widget in live time.

2. "Change" button - when change clicked and Enter pressed after cell editing, all data gone. All data must be save in real time is SQL DB after change button press.

   3)"Delete" button - this button don't delete row. There is no error, no any answer from app.

How to program the buttons correctly ? Do I need to use a delegate? Which programming approach is more preferable when working with a graphical interface and SQL database?

I made a reproducible example. At startup, a database with 1 table and 2 rows will be created.

import sys, os, sqlite3
from datetime import datetime 
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
from PyQt5.QtCore import *

CONFIG_NAME = 'config.ini'
DB_NAME = 'nsi.db'

class MainWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super().__init__() 
        self.window_pref()
        self.show_widgets()

    def window_pref(self):
        self.setWindowTitle('PyQt5 APP')
        self.def_width = 800
        self.def_height = 400
        self.def_size = self.setMinimumSize(self.def_width, self.def_height)

    def show_widgets(self):
        self.createConnection()        
        self.fillDB()       
        self.setupMainWidgets()  

    def createConnection(self):
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(DB_NAME)

        if not db.open():
            QMessageBox.warning(self, 'PyQt5 APP', 
                'Error:{}'.format(db.lastError().text()))
            sys.exit(1)

    def fillDB(self):
        query = QSqlQuery()
        query.exec_("""\
            CREATE TABLE sprav (
                id_nsi INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                nsi_name TEXT UNIQUE NOT NULL,
                file_date TEXT NOT NULL,
                file_name TEXT NOT NULL)
                """)

        query.prepare("""\
            INSERT INTO sprav (nsi_name, file_date, file_name)VALUES (?, ?, ?)
            """)


        sample_list = (('nsi1', 'january', 'file1'), ('nsi2', 'may', 'file2'))

        for i in sample_list:
            query.addBindValue(i[0])           
            query.addBindValue(i[1]) 
            query.addBindValue(i[2])    
            query.exec_()
            
    def setupMainWidgets(self):
        mw_widget = QWidget()               
        main_panel = QHBoxLayout(mw_widget) 

        # SQL Table
        self.modelSql = QSqlTableModel()
        self.modelSql.setTable('sprav') 

        self.modelSql.setQuery(QSqlQuery(
                    'SELECT nsi_name, file_date, file_name FROM sprav'))

        self.modelSql.setHeaderData(self.modelSql.fieldIndex('nsi_name'),
                                Qt.Horizontal, 'Name')
        self.modelSql.setHeaderData(self.modelSql.fieldIndex('file_date'),
                                Qt.Horizontal, 'Date')
        self.modelSql.setHeaderData(self.modelSql.fieldIndex('file_name'),
                                Qt.Horizontal, 'File')
        self.modelSql.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.modelSql.select()

        # QTableView() 
        self.table_view = QTableView()
        self.table_view.setSelectionBehavior(1) 
        self.table_view.setAlternatingRowColors(True) 
        self.table_view.setModel(self.modelSql)     

        self.table_view.horizontalHeader().setSectionResizeMode(0, QHeaderView.Stretch)

        main_panel.addWidget(self.table_view)

        # QVBoxLayout()
        right_panel = QVBoxLayout() 
        line = QFrame()
        line.setFrameShape(QFrame.HLine)

        self.add_record = QPushButton('Add', self)
        self.add_record.clicked.connect(self.addRecord)

        self.change_record = QPushButton('Change', self)
        self.change_record.clicked.connect(self.changeRecord)      
        self.delete_record = QPushButton('Delete', self)
        self.delete_record.clicked.connect(self.delRecord)

        right_panel.addSpacing(20)
        right_panel.addWidget(line)
        right_panel.addWidget(self.add_record)
        right_panel.addWidget(self.change_record)
        right_panel.addWidget(self.delete_record)
        right_panel.addStretch()

        main_panel.addLayout(right_panel) 

        self.setCentralWidget(mw_widget)

    def addRecord(self):
        row = self.modelSql.rowCount()
        self.modelSql.insertRow(row)

        index = self.modelSql.index(row, 0)
        self.table_view.setCurrentIndex(index) 
        self.table_view.edit(index)

    def delRecord(self):
        cur_item = self.table_view.selectedIndexes()
        for index in cur_item:
            self.modelSql.removeRow(index.row())
        self.modelSql.select()

    def changeRecord(self):
        self.table_view.edit(self.table_view.currentIndex())      

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec_())

Solution

There are two problems with your code.

First of all, if you use QSqlTableModel, you should not call setQuery():

This function simply calls QSqlQueryModel::setQuery(query). You should normally not call it on a QSqlTableModel. Instead, use setTable(), setSort(), setFilter(), etc., to set up the query.

This is the main reason for which your row addition/deletion/editing didn't work: the model became partially invalid, and any submission was discarded because columns didn't match the records of the table model, which is very important also because the model requires an incremental key that QSqlTableModel is able to use properly on its own.

Remove the setQuery() from your code, and consider that if you did that only to hide a column, then you just have to hide that column:

    self.table_view.setColumnHidden(0, True)

Obviously, you have to keep in mind that all column indexes you will use, will now start from 1, since the model also includes the id:

def addRecord(self):
    # ...
    index = self.modelSql.index(row, 1)
    self.table_view.setCurrentIndex(index) 
    self.table_view.edit(index)

The other problem was the deletion of rows: even after fixing what described above, the number of rows and its order would have been wrong:

• when you cycle through the selectedIndexes() you're getting the same row for each selected item: since you used the SelectRows selection behavior, the for loop would have called removeRow() three times the same row, for each selected row;
• removal of indexes should always be in reverse, sorted order; consider if you try to remove row 0 and 1: the first iteration would remove row 0, but at that point the previous row 1 would have become the new row 0, so the next iteration would actually delete the row that previously was the third;

The solution is to have a sorted list of unique row numbers, and cycle through them in reversed order:

    def delRecord(self):
        # create a set of unique row numbers
        rows = set([i.row() for i in self.table_view.selectedIndexes()])
        # cycle through them in reversed sorting order
        for row in sorted(rows, reverse=True):
            self.modelSql.removeRow(row)
        self.modelSql.select()

Remember to call select() (I know you did, but better safe than sorry), as explained in the documentation about removeRows():

Deletions are submitted immediately to the database. The model retains a blank row for successfully deleted row until refreshed with select().

^{Unrelated notes: 1. avoid unnecessary and confusing imports: since you're already importing QtWidgets with wildcard, there's no point for from PyQt5 import QtWidgets; you either import the submodule, or its classes; 2. setMinimumSize returns nothing, so self.def_size will be None; if you want to keep a variable for the default size, use self.def_size = QSize(self.def_width, self.def_height) then self.setMinimumSize(self.def_size); 3. do not use sys.exit() inside a Qt app (and from a QWidget class), instead use QApplication.exit(1); 4. use more verbose names that also clarify their type: you have almost identical names for buttons and functions (add_record and addRecord), which is a poor choice in naming (also considering the different writing style): a better choice would be to name the button like add_record_btn, or addRecordBtn to follow the Qt convention;}

Answered By - musicamante