PyQT5 : SpinBox values are not maintained in qsql

Question

Work on a project and I want to make spinBox values saved in the database .. but the code does not work for me ..

The result I want to get:

For example, when changing the value of spinBox_1 to 5 and the value of spinBox_2 to 10, when the program is closed and restarted, it must keep the values.

I have two buttons .. When changing the spinBox values and then pressing the button 1, these values should be preserved And when you press the button 2, other values appear and can be changed In case the program is restarted in the first, the stored values must appear in the first when the button 1 is pressed When button 2 is pressed, the stored values should be displayed when button 2 is pressed

Please, help me

programme :

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql
from PyQt5.QtSql import *
   
def create_connection(database):
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(database)
    if not db.open():
        print("Cannot open database")
        print(
            "Unable to establish a database connection.\n"
            "This example needs SQLite support. Please read "
            "the Qt SQL driver documentation for information "
            "how to build it.\n\n"
            "Click Cancel to exit."
        )
        return False

    query = QtSql.QSqlQuery()
    if not query.exec_(
        """CREATE TABLE IF NOT EXISTS Devices (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "floor_id" INTEGER,
    "spinBox_1" INTEGER,
    "spinBox_2" INTEGER,
    FOREIGN KEY(floor_id) REFERENCES Floors(id))"""
    ):
        print(query.lastError().text())
        return False
    return True



class Ui_Form(object):
    def setupUi(self, Form):
        Form.setObjectName("Form")
        Form.resize(267, 173)
        self.spinBox_1 = QtWidgets.QSpinBox(Form)
        self.spinBox_1.setGeometry(QtCore.QRect(60, 50, 42, 22))
        self.spinBox_1.setObjectName("spinBox_1")
        self.spinBox_2 = QtWidgets.QSpinBox(Form)
        self.spinBox_2.setGeometry(QtCore.QRect(150, 50, 42, 22))
        self.spinBox_2.setObjectName("spinBox_2")
        self.pushButton_1 = QtWidgets.QPushButton(Form)
        self.pushButton_1.setGeometry(QtCore.QRect(40, 120, 75, 23))
        self.pushButton_1.setObjectName("pushButton_1")
        self.pushButton_2 = QtWidgets.QPushButton(Form)
        self.pushButton_2.setGeometry(QtCore.QRect(140, 120, 75, 23))
        self.pushButton_2.setObjectName("pushButton_2")
        self.retranslateUi(Form)
        QtCore.QMetaObject.connectSlotsByName(Form)
        
        self.model = QtSql.QSqlQueryModel(Form)
        self.record = self.model.record(0)
        self.spinBox_1.setValue(self.record.value("spinBox_1"))
        self.spinBox_2.setValue(self.record.value("spinBox_2"))

        self.enable = True
# ----------------------------------------------------------------------------------------------
#                             if spinBox value changed
# ----------------------------------------------------------------------------------------------
        self.spinBox_1.valueChanged.connect(self.updateSpinBox_1)
        self.spinBox_2.valueChanged.connect(self.updateSpinBox_2)
# ----------------------------------------------------------------------------------------------
#                             if pushButton clicked
# ----------------------------------------------------------------------------------------------
        self.pushButton_1.clicked.connect(self.value_1)
        self.pushButton_2.clicked.connect(self.value_2)
# ----------------------------------------------------------------------------------------------
# ----------------------------------------------------------------------------------------------

    def updateSpinBox_1(self):
        if self.enable :
            self.record = self.model.record(0)
        else :
            self.record = self.model.record(1)
        self.record.setValue("spinBox_1", self.spinBox_1.value())
        self.spinBox_1.setValue(self.record.value("spinBox_1"))

        print(self.record.value("spinBox_1"))
        print(self.enable)

    def updateSpinBox_2(self):
        if self.enable :
            self.record = self.model.record(0)
        else :
            self.record = self.model.record(1)
        self.record.setValue("spinBox_2", self.spinBox_2.value())
        self.spinBox_2.setValue(self.record.value("spinBox_2"))

        print(self.record.value("spinBox_2"))
        print(self.enable)

    def value_1(self):
        self.enable = True
        self.record = self.model.record(0)
        self.spinBox_1.setValue(self.record.value("spinBox_1"))
        self.spinBox_2.setValue(self.record.value("spinBox_2"))
        return self.enable

    def value_2(self):
        self.enable = False
        self.record = self.model.record(1)
        self.spinBox_1.setValue(self.record.value("spinBox_1"))
        self.spinBox_2.setValue(self.record.value("spinBox_2"))
        return self.enable


# ----------------------------------------------------------------------------------------------
# ----------------------------------------------------------------------------------------------  
    def retranslateUi(self, Form):
        _translate = QtCore.QCoreApplication.translate
        Form.setWindowTitle(_translate("Form", "Form"))
        self.pushButton_1.setText(_translate("Form", "PushButton1"))
        self.pushButton_2.setText(_translate("Form", "PushButton2"))


if __name__ == "__main__":
    import sys
    database = "database.db"
    app = QtWidgets.QApplication(sys.argv)
    if not create_connection(database):
        sys.exit(app.exec_())
    Form = QtWidgets.QWidget()
    ui = Ui_Form()
    ui.setupUi(Form)
    Form.show()
    sys.exit(app.exec_())

score 1 · Accepted Answer · answered Oct 30 '20 at 18:09

First of all, you are using a QSqlQueryModel, which is read only. As a side note, you never used setQuery(), so your code actually doesn't work as the result of self.model.record(0) is an empty record.

Then, modifying the values of a QSqlRecord doesn't mean that the database will automatically updated.

While you can use a plain QSqlQuery to update the values using standard database UPDATE commands, you can use QSqlRecord as long as a QSqlTableModel is used, and in order to update the data, call setRecord(). Note that for some reasons related to the way the model is edited and how sqlite works, the modified field(s) generated flag must be set to True.

class Ui_Form(object):
    def setupUi(self, Form):
        # ...
        self.model = QtSql.QSqlTableModel(Form)
        self.model.setTable('Devices')
        self.model.select()
        self.record = self.model.record(0)
        # ...

    def updateSpinBox_1(self):
        row = 0 if self.enable else 1
        self.record = self.model.record(row)
        self.record.setValue("spinBox_1", self.spinBox_1.value())
        self.spinBox_1.setValue(self.record.value("spinBox_1"))
        self.record.setGenerated('spinBox_1', True)
        self.model.setRecord(row, self.record)

_{Note: editing the .py files generated by pyuic to create programs is considered bad practice, as those files should never be modified; you should also not try to mimic their behavior, as their structure only has sense for their internal usage (using ui files from python) and creating UI from code should preferably be done using QWidget subclasses. Read more on the official guidelines about using Designer.}

PyQT5 : SpinBox values are not maintained in qsql

1 Answers1