1

I am trying to insert a row into a PostgreSQL database using a QSqlRelationalTableModel. The connection works and I can edit the data in the tableview. However, I am stuck with trying to insert a new row. The error is because of NULL values for the relation fields. Seems I cannot just push integers to them. So what is the proper way for doing this as cannot find an answer to this?

class Accounts(QDialog):
  def __init__(self, parent=None):
    super().__init__(parent)
    # Set up the ui
    self.ui = Ui_account()
    self.ui.setupUi(self)
    self.ui.addbutton.clicked.connect(self.onadd)
    self.ui.closebutton.clicked.connect(self.onclose)
    self.userid = parent.userid
    # Set up the model
    self.model = QSqlRelationalTableModel(self)
    self.model.setTable("accounts")        
    self.model.setEditStrategy(QSqlRelationalTableModel.OnRowChange)
    self.model.setHeaderData(0, Qt.Horizontal, "id")
    self.model.setHeaderData(1, Qt.Horizontal, "name")
    self.model.setHeaderData(2, Qt.Horizontal, "currencyid")
    self.model.setHeaderData(3, Qt.Horizontal, "created")
    self.model.setHeaderData(4, Qt.Horizontal, "createdby")
    self.model.setHeaderData(5, Qt.Horizontal, "closed")
    self.model.setRelation(2,QSqlRelation("currency","id","type"))
    self.model.setRelation(4,QSqlRelation("employees","id","name"))
    self.model.select()
    # Set up the view
    self.ui.accountview.setModel(self.model)
    self.ui.accountview.setColumnHidden(0,True)
    self.ui.accountview.horizontalHeader().setSectionResizeMode(1, QHeaderView.Stretch)
    self.ui.accountview.horizontalHeader().setSectionResizeMode(2, QHeaderView.ResizeToContents)
    self.ui.accountview.horizontalHeader().setSectionResizeMode(3, QHeaderView.ResizeToContents)
    self.ui.accountview.horizontalHeader().setSectionResizeMode(4, QHeaderView.ResizeToContents)
    self.ui.accountview.horizontalHeader().setSectionResizeMode(5, QHeaderView.ResizeToContents)
    self.ui.accountview.setItemDelegate(QSqlRelationalDelegate(self.ui.accountview))

  def onadd(self):
    # Insert a new record
    r = self.model.record()
    # Auto generated in the PSQL database
    r.setGenerated("id",False)
    r.setGenerated("created",False)
    r.setGenerated("closed",False)
    r.setValue("name",self.ui.accname.text())
    # Stored in db as integers of the primary key of the other tables    
    r.setValue("currencyid",self.ui.currency.currentIndex())
    r.setValue("createdby",self.userid)
    self.model.insertRecord(-1,r)
    # Fails at this point with NULL values for the foreign keys 
    self.model.select()`
eyllanesc
  • 235,170
  • 19
  • 170
  • 241
Mark
  • 11
  • 2

1 Answers1

0

Finally found an old comment from 2014 when searching on just QT and not specifically PyQT. Seems the issue only happens for relation fields. The fix is to not call it by its name, but by its index only. Therefore the above 2 lines should be changed to:

    r.setValue(2,self.ui.currency.currentIndex())
    r.setValue(4,self.userid)

And now all works.

Mark
  • 11
  • 2