Cannot insert record 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()

#!/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()

To copy to clipboard, switch view to plain text mode 

[Ginsengelf]

Hi, just a wild guess: in line 19 you use setRelation to map the "id" from the "people" table to the "city" table. But when you call createFakeData() you populate the "people" table first. Maybe that causes problems, because the "city" table is still emtpy, and the mapping cannot take place?
Does it work if you first INSERT INTO the "city" table and then into the "people" table?

Ginsengelf