Write single record to SQL database in PySide

Question

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_())

Do you have to `commit()` after the insert? I don't know `Qt` either but you do commit here: `self.db.commit()` after creating a table. — roganjosh, Sep 22 '17 at 21:01
The `lastInsertId()` call returns `None` because the model itself did not do any inserts. — ekhumoro, Sep 22 '17 at 22:09
@roganjosh, thanks for looking. I use raw SQL when creating the database instead of a Qt model. I believe the commit() there is necessary to end the transaction and write the data. — davideps, Sep 23 '17 at 07:38
@ekhumoro, thanks for helping again. I misunderstood what lastInsertId() does. — davideps, Sep 23 '17 at 07:38

eyllanesc · Accepted Answer · 2017-09-22T23:21:27.257

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())

thank you! I had overlooked the -1 in the insertRecord. That solved the issue. — davideps, Sep 23 '17 at 07:47

Write single record to SQL database in PySide

1 Answers1