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