[FIXED] Write single record to SQL database in PySide

Issue

I want to build a record from multiple QT widgets and store it in a database. The following script runs without an error but does not store the record. Part of the problem is that I cannot determine the last ID already in the database, but even if I manually set the ID--nothing is written. I've seen examples online using insertRecord, but the QT documentation suggests using insertRow. Please feel free to correct my approach. I'm new to Python and to Qt.

import sys
from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *


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

        #Make Database
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('C:/Users/dle/Documents/example1.sqlite')
        self.db.open()
        self.db.transaction()
        self.db.exec_(
            'CREATE TABLE t1'
            '(id INTEGER PRIMARY KEY, f1 INTEGER NOT NULL, f2 INTEGER NOT NULL, f3 TEXT NOT NULL)'
        )
        self.db.exec_("INSERT INTO t1 VALUES(1, 10, 20, 'db works fine')")
        self.db.commit()

        #Create User Interface
        self.f1 = QLineEdit()
        self.f2 = QLineEdit()
        self.f3 = QLineEdit()

        self.storeButton = QPushButton("Store")
        self.storeButton.clicked.connect(self.doStore)

        vlayout = QVBoxLayout()
        vlayout.addWidget(self.f1)
        vlayout.addWidget(self.f2)
        vlayout.addWidget(self.f3)
        vlayout.addWidget(self.storeButton)

        widget = QWidget()
        widget.setLayout(vlayout)

        self.setCentralWidget(widget)

    def doStore(self):
        self.dbModel = QSqlTableModel(self)
        self.dbModel.setTable('t1')
        ID = self.dbModel.query().lastInsertId()    #this returns "None" even though last ID
                                                    #in table is 1
        thisRecord = QSqlRecord
        thisRecord = self.dbModel.record()
        thisRecord.setValue(0, ID)                  # Does not write, even if ID is integer
        print ID
        thisRecord.setValue(1, int(self.f1.text()))
        print int(self.f1.text())
        thisRecord.setValue(2, int(self.f2.text()))
        print int(self.f2.text())
        thisRecord.setValue(3, self.f3.text())
        print self.f3.text()
        print thisRecord
        self.dbModel.insertRecord(ID, thisRecord)   # Does not write, even if ID is integer
                                                    # Doesn't record field 0 already have ID?

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

Solution

If you want to insert a row at the end of the table it is not necessary to indicate the index, since according to the docs:

PySide.QtSql.QSqlTableModel.insertRecord(row, record)

Parameters:

row - PySide.QtCore.int

record – PySide.QtSql.QSqlRecord 

Return type:

PySide.QtCore.bool

Inserts the record after row . If row is negative, the record will be appended to the end. Calls PySide.QtSql.QSqlTableModel.insertRows() and PySide.QtSql.QSqlTableModel.setRecord() internally.

Returns true if the row could be inserted, otherwise false.

From the above we can conclude that we should only use as row = -1.

self.dbModel.insertRecord(-1, record)

The best thing is to create the model once and not every time the doStore function is called, we must also use the QSqlRecord of the model with the help of the record () function as it comes loaded with the field names.

class MainWindow(QMainWindow):
    def __init__(self, parent=None):
        super(MainWindow, self).__init__(parent)
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('example1.sqlite')
        self.db.open()
        self.db.transaction()
        self.db.exec_(
            'CREATE TABLE t1'
            '(id INTEGER PRIMARY KEY, f1 INTEGER NOT NULL, f2 INTEGER NOT NULL, f3 TEXT NOT NULL)'
        )

        self.db.commit()
        self.db.exec_("INSERT INTO t1 VALUES(1, 10, 20, 'db works fine')")

        #Create User Interface
        [...]
        self.setCentralWidget(widget)

        self.dbModel = QSqlTableModel(self)
        self.dbModel.setTable('t1')

    def doStore(self):
        record = self.dbModel.record()
        record.setValue(1, int(self.f1.text()))
        record.setValue(2, int(self.f2.text()))
        record.setValue(3, self.f3.text())
        if not self.dbModel.insertRecord(-1, record):
            print(self.db.lastError().text())

Answered By - eyllanesc