How do I update SQL database from QTablewidget using only the cells which have been changed

Question

I am populating a QTableWidget from a sql database and would like to specifically update the values which have been added to the database via the tablewidget instead of over-writing the entire database with the table widget data.

Like assuming the data in the 1st two rows were from the database, and a number is added to the 3rd and 4th rows. Is it possible to insert only those values into the database to update it? And how would you go about that?

This is the code which produces the table.

from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5 import QtCore, QtGui, QtWidgets
import mysql.connector

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.setObjectName("gridLayout")
        self.widget = QtWidgets.QWidget(self.centralwidget)
        self.widget.setObjectName("widget")
        self.gridLayout_2 = QtWidgets.QGridLayout(self.widget)
        self.gridLayout_2.setObjectName("gridLayout_2")
        self.tableWidget = QtWidgets.QTableWidget(self.widget)
        self.tableWidget.setRowCount(10)
        self.tableWidget.setColumnCount(6)
        self.tableWidget.setObjectName("tableWidget")
        self.gridLayout_2.addWidget(self.tableWidget, 0, 0, 1, 1)
        self.pushButton = QtWidgets.QPushButton(self.widget)
        self.pushButton.setObjectName("pushButton")
        self.gridLayout_2.addWidget(self.pushButton, 1, 0, 1, 1)
        self.gridLayout.addWidget(self.widget, 0, 0, 1, 1)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.pushButton.clicked.connect(self.load_table)
        
        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pushButton.setText(_translate("MainWindow", "PushButton"))


    def load_table(self):
        
        

        mydb = mysql.connector.connect(host="localhost",
                            user="root",
                            password="########",
                            database="###########")
        mycursor = mydb.cursor()

        # Fecthing Data From mysql to my python progam
        mycursor.execute("select * from table")
        result = mycursor.fetchall()
        
        self.tableWidget.setRowCount(0)
 
        for row_number, row_data in enumerate(result):
                self.tableWidget.insertRow(row_number)
 
                for column_number, data in enumerate(row_data):
                    if data == None:
                        data = ""
                        self.tableWidget.setItem(row_number,
                        column_number, QTableWidgetItem(str(data)))
                    else:
                        
                        item = QTableWidgetItem(str(data))
                        item.setFlags(item.flags() ^ Qt.ItemIsEditable)
                        self.tableWidget.setItem(row_number, column_number, item)
                    

if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

If the data shown in the table is in the same form of the database, then don't use QTableWidget, but a QTableView with a QSqlTableModel. Otherwise, connect to the [`itemChanged`](https://doc.qt.io/qt-5/qtablewidget.html#itemChanged) signal to keep track a list of all changed item, and when you need to submit the data, use that list. If you need more details, please provide a [mre], because we cannot give more detailed answers about this only based on an image. — musicamante, Sep 17 '21 at 00:15
Thanks for the advice on connecting to the item changed signal but would that method still work if the user changed their mind and deleted what was written in the cell? I've also added a minimal reproducible sample. — E. Artemis, Sep 17 '21 at 01:13
@E.Artemis if by "changed their mind" you mean the user pressing Esc while editing, the signal won't be emitted because the value is automatically restored. If you instead want to provide some sort of "undo" so that you can check if the user has restored the original value, then you have to implement it according to your needs. Note that you could just connect that signal to a function that *stores* the changed cells and then add a button or menu item that actually submits the data to the model, and eventually restores them if required. — musicamante, Sep 17 '21 at 18:14

How do I update SQL database from QTablewidget using only the cells which have been changed

0 Answers0