insertRecord failure with QSqlRelationalTableModel

[peterloron]

I have an application (posted below) which has a QSqlRelationalTableModel connected to a simple Sqlite db with two tables. The data from the db shows up in the QTableView as expected, which the referenced data properly filled in. However, when I try to insert a record, I get the following error:

NOT NULL constraint failed: people.city Unable to fetch row

It seems like the value I set in the record for the people.city field isn't making it into the insert? Any help would be appreciated. Thanks.

#!/usr/bin/env python

import sys
from PyQt6.QtSql import *
from PyQt6.QtCore import Qt
from PyQt6.QtWidgets import (QApplication, QMainWindow, QTableView)

class MainWindow(QMainWindow):
  def __init__(self):
    super().__init__()

    # set up main window
    self.resize(1024,700)

    self.model = QSqlRelationalTableModel()
    self.model.setTable("people")
    self.model.setEditStrategy(QSqlTableModel.EditStrategy.OnFieldChange)
    self.model.setHeaderData(2, Qt.Orientation.Horizontal, "city")
    self.model.setRelation(2, QSqlRelation("city", "id", "name"))

    self.view = QTableView()
    self.view.setModel(self.model)
    self.view.setItemDelegate(QSqlRelationalDelegate(self.view))
    self.view.setSortingEnabled(True)
    self.view.sortByColumn(1, Qt.SortOrder.AscendingOrder)
    self.view.verticalHeader().setVisible(False)
    self.view.doubleClicked.connect(self.onAddRow)
    
    self.setCentralWidget(self.view)
    self.view.resizeColumnsToContents()
    self.model.select()


  def onAddRow(self, s):
    self.model.select()
    new_rec = self.model.record()
    new_rec.setValue("name", 'Eliza')
    new_rec.setValue("city", 2)
    res = self.model.insertRecord(-1, new_rec)
    if res:
      print("insert ok")
    else:
      print("insert failed")
      print(self.model.lastError().text())

    self.model.submitAll()
    self.model.select()


# Establish connection to our database.
def dbConnect():
  con = QSqlDatabase.addDatabase("QSQLITE")
  con.setDatabaseName('db.sqlite')
  if not con.open():
    print("DB connect error")
    sys.exit(1)

def createFakeData():
  query = QSqlQuery()
  query.exec("DROP TABLE people")
  query.exec("DROP TABLE city")

  query.exec("CREATE TABLE people(id INTEGER PRIMARY KEY, name VARCHAR(40) NOT NULL, city INTEGER NOT NULL)")
  query.exec("CREATE TABLE city(id INTEGER PRIMARY KEY, name VARCHAR(40) NOT NULL)")

  query.exec("INSERT INTO people(name, city) VALUES('Helen', 1)")
  query.exec("INSERT INTO people(name, city) VALUES('Juan', 2)")
  query.exec("INSERT INTO city(name) VALUES('Seattle')")
  query.exec("INSERT INTO city(name) VALUES('Tokyo')")


def main():
  app = QApplication(sys.argv)
  dbConnect()
  createFakeData()
  w = MainWindow()
  w.show()
  app.exec()

if __name__ == '__main__':
    main()

[JonB]

@peterloron said in insertRecord failure with QSqlRelationalTableModel:

query.exec("INSERT INTO people(name, city) VALUES('Helen', 1)")

This is the first statement you execute when both tables are empty. At this point there is no row in city which has value 1 in its id column. That is where

NOT NULL constraint failed: people.city Unable to fetch row

comes from as Qt tries to insert the row/fetch it back.

Switch the order in your code so that it puts rows into city table before it puts rows into people.

If the error comes during onAddRow(), make sure city table has a row with value of 2 and has been read into memory.

[SGaist]

Hi and welcome to devnet,

As @JonB suggested, the order of table setup in a database is crucial. You can't use a relation if the target of the relation does not exist. Start by building all the leaf tables and then go back up creating the one that uses then and so on and so forth.

[JonB]

@SGaist
That is what is thought/wrote initially. But the OP wrote "insertRecord failure". If that comes in onAddRow() that is after the population, and he does have self.model.select() there, so I am not sure. He should verify what that select() puts into the the city table.

[SGaist]

@JonB one of the issue is that the database initialization is not done in the correct order and assumes the values of the cities ID.

All in all, it should use a Foreign Key to properly map the city to the user. This would properly structure the relation between the tables.