PYQT Add new record using QSqlRelationalTableModel and QtableView

Question

I am trying to add a new row to my QtableView and thus a record to a Postgresql database. The idea is to add the record and permit the user to edit it in place in a form which looks like this:

Adding the row seems to go fine but when I try to edit it, the vertical header shows an exclamation point and the two fields which I have set data for disappear.

The question is, how do I add a row for in place editing? If the row already exists, I have no problem editing it.

from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
from PyQt5.QtWidgets import *
import sys
from datetime import datetime, timedelta, time

#form modules
from main import *


class Main(QMainWindow):

    def __init__(self, parent=None):
        QMainWindow.__init__(self, parent)
        self.ui = Ui_Nextock()
        self.ui.setupUi(self)
        currentdate = QDate.currentDate()
        self.ui.weekending.setDate(currentdate.addDays(6-currentdate.dayOfWeek()))
        #self.ui.weekending.setDate(QDate(2017, 6, 10))

        self.emp_model = QSqlRelationalTableModel(self)
        self.emp_model.setTable('employees')
        self.emp_model.setSort(int(self.emp_model.fieldIndex("empid")), Qt.AscendingOrder)
        self.emp_model.setRelation(self.emp_model.fieldIndex("department"), QSqlRelation('departments', 'department', 'department'))
        self.emp_model.setFilter("termination_date IS NULL")
        self.emp_model.select()
        self.ui.department.setModel(self.emp_model.relationModel(self.emp_model.fieldIndex("department")))
        self.emp_mapper = QDataWidgetMapper(self)
        self.emp_mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
        self.emp_mapper.setModel(self.emp_model)
        self.emp_mapper.addMapping(self.ui.title, self.emp_model.fieldIndex("salutation"))
        self.emp_mapper.addMapping(self.ui.first_name, self.emp_model.fieldIndex("first_name"))
        self.emp_mapper.addMapping(self.ui.last_name, self.emp_model.fieldIndex("surname"))
        self.emp_mapper.addMapping(self.ui.address, self.emp_model.fieldIndex("Address"))
        self.emp_mapper.addMapping(self.ui.email, self.emp_model.fieldIndex('email'))
        self.emp_mapper.addMapping(self.ui.phone, self.emp_model.fieldIndex('phone'))
        self.emp_mapper.addMapping(self.ui.mobile, self.emp_model.fieldIndex('mobile'))
        self.emp_mapper.addMapping(self.ui.birthdate, self.emp_model.fieldIndex('birth_date'))
        self.emp_mapper.addMapping(self.ui.start_date, self.emp_model.fieldIndex('start_date'))
        self.emp_mapper.addMapping(self.ui.contracted_hours, self.emp_model.fieldIndex('contracted_hours'))
        self.emp_mapper.addMapping(self.ui.payrate, self.emp_model.fieldIndex('pay_rate'))
        self.emp_mapper.addMapping(self.ui.username, self.emp_model.fieldIndex('loginid'))
        self.emp_mapper.addMapping(self.ui.password, self.emp_model.fieldIndex('pwd'))
        self.emp_mapper.addMapping(self.ui.department, self.emp_model.fieldIndex('department'))
        self.emp_mapper.setItemDelegate(QSqlRelationalDelegate(self.emp_mapper))
        self.emp_mapper.toFirst()

        self.hours_model = Tc_relational_model(self)
        self.hours_model.setTable('emphours')
        self.hours_model.setSort(self.hours_model.fieldIndex('id'), Qt.AscendingOrder)
        self.hours_model.setRelation(self.hours_model.fieldIndex("department"), QSqlRelation('departments', 'department', 'department'))
        self.hours_model.setRelation(self.hours_model.fieldIndex("paytype"), QSqlRelation('pay_types', 'pay_type', 'pay_type'))
        self.hours_model.select()
        self.hours_view = self.ui.emp_hours
        self.hours_view.setModel(self.hours_model)
        self.hours_view.setItemDelegate(QSqlRelationalDelegate(self.hours_view))
        self.hours_view.setColumnHidden(self.hours_model.fieldIndex('id'), True)
        self.hours_view.setColumnHidden(self.hours_model.fieldIndex('empyear'), True)
        self.hours_view.setColumnHidden(self.hours_model.fieldIndex('empweek'), True)
        self.hours_view.setColumnHidden(self.hours_model.fieldIndex('empmonth'), True)
        self.hours_view.setEditTriggers(QAbstractItemView.AllEditTriggers)
        self.hours_model.dataChanged.connect(self.dta_chng)

        self.ui.weekending.dateChanged.connect(lambda: self.record_changed(self.emp_mapper.currentIndex()))
        self.ui.new_line.clicked.connect(self.new_line)


        self.ui.current_index.setText('Record ' + str(self.emp_mapper.currentIndex()+1) + ' of ' + str(self.emp_model.rowCount()) + ' records')

    def dta_chng(self):
        print(self.hours_model.lastError().text())


    def new_line(self):
        row =  self.hours_model.rowCount()
        self.hours_model.insertRow(row)
        self.idx_empid = QModelIndex(self.hours_model.index(row, self.hours_model.fieldIndex('empid')))
        self.hours_model.setData(self.idx_empid, int(self.ui.emp_id.text()), Qt.EditRole)
        self.idx_weekending = QModelIndex(self.hours_model.index(row, self.hours_model.fieldIndex('weekending')))
        #self.hours_model.setData(self.idx_weekending, self.ui.weekending.date(), Qt.EditRole)
        print('In new_line')


    def record_changed(self, index):
        self.emp_mapper.submit()
        if index >= 0:
            total_hours = timedelta(0)
            the_id = self.emp_model.record(index).value('empid')
            self.hours_model.setFilter("empid = '{0}' and weekending = '{1}'".format(the_id,
                                                                                     self.ui.weekending.date().toString(Qt.ISODate)))
            self.ui.name_label.setText(self.emp_model.record(index).value('surname')
                                       + ", " + self.emp_model.record(index).value('first_name'))
            self.ui.terminationdate.setText(self.emp_model.record(index).value('termination_date').toString("dd/MM/yyyy"))
            self.ui.emp_id.setText(str(self.emp_model.record(index).value('empid')))

            for row in range(self.hours_model.rowCount()):
                #elapsed = self.data(self.index(index.row(), 9)).toPyDateTime() - self.data(self.index(index.row(), 8)).toPyDateTime()
                elapsed = self.hours_model.index(row, 9).data().toPyDateTime() - self.hours_model.index(row, 8).data().toPyDateTime()
                if elapsed.total_seconds() > 0:
                    total_hours += elapsed

            self.ui.total_hours.setText(str(total_hours))

        else:
            self.hours_model.setFilter("empid = -1")


class Tc_relational_model(QSqlRelationalTableModel):
    def __init__(self, parent=None):
        super(Tc_relational_model, self).__init__(parent)
        self.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
        self.setTable("emphours")
        self.select()

    def columnCount(self, parent=QtCore.QModelIndex()):
        return super(Tc_relational_model, self).columnCount()+1

    def data(self, index, role=QtCore.Qt.DisplayRole):
        if role == QtCore.Qt.DisplayRole and index.column()==10:
            if self.data(self.index(index.row(), 9)) is not None and self.data(self.index(index.row(), 8)) is not None:
                elapsed = self.data(self.index(index.row(), 9)).toPyDateTime() - self.data(self.index(index.row(), 8)).toPyDateTime()
            else:
                elapsed = timedelta()
            if elapsed.total_seconds() < 0:
                elapsed = ''
            else:
                elapsed = str(elapsed)
            return elapsed

        if index.column() > 10:
            index = self.index(index.row(), index.column()-1)

        return super(Tc_relational_model, self).data(index, role)

    def headerData(self, section, orientation, role=QtCore.Qt.DisplayRole):
        if section==10 and orientation==QtCore.Qt.Horizontal and role==QtCore.Qt.DisplayRole:
            return 'Elapsed time'
        if section > 10 and orientation==QtCore.Qt.Horizontal:
            section -= 1
        return super(Tc_relational_model, self).headerData(section, orientation, role)

    def flags(self, index):
        if index.column()==10:
            return QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled
        return QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsEditable

    def setData(self, index, data, role):
        if index.column() > 10:
            index = self.index(index.row(), index.column()-1)
        return super(Tc_relational_model, self).setData(index, data, role)





if __name__=="__main__":
    app=QApplication(sys.argv)
    pword = 'pword'
    dbase = 'db'
    server = 'server'
    user = 'dave'
    db = QSqlDatabase.addDatabase("QPSQL");
    db.setHostName(server)
    db.setDatabaseName(dbase)
    db.setUserName(user)
    db.setPassword(pword)
    if (db.open()==False):
        QMessageBox.critical(None, "Database Error", db.lastError().text())

    myapp = Main()
    myapp.show()
    sys.exit(app.exec_())

eyllanesc · Accepted Answer · 2018-08-23 15:05:49Z

3

Well, I took an entirely different approach to the problem. Below is the adjustment to the code:

def new_line(self):
    row =  self.hours_model.rowCount()
    record = self.hours_model.record()
    record.setGenerated('id', False)
    record.setValue('empid', self.ui.emp_id.text())
    record.setValue('weekending', self.ui.weekending.date())
    record.setValue('department', self.ui.department.currentText())
    record.setValue('pay_type', 'Regular')
    record.setValue('starttime', QDateTime.currentDateTime())
    record.setValue('endtime', QDateTime.currentDateTime())
    self.hours_model.insertRecord(row, record)
    self.hours_view.edit(QModelIndex(self.hours_model.index(row, self.hours_model.fieldIndex('department'))))

This seems to work. However, it begets more questions than it answers. It seems to me that the approach in my question, although more verbose, is more model/view. So I am still stumped.

edited Aug 23, 2018 at 15:05

eyllanesc

246k19 gold badges205 silver badges282 bronze badges

answered Aug 23, 2018 at 12:47

Dkellygb

9062 gold badges8 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PYQT Add new record using QSqlRelationalTableModel and QtableView

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related