QSqlTableModel.insertRecord() not inserting all fields

I have an SQLite database with several tables. I’m trying to insert a new record into one of those tables (Colors) which has 3 fields which are ‘NOT NULL’ in the schema. I’m using an QSqlTableModel with an edit strategy of OnManualSubmit. I obtain an QSqlRecord and iterate over the fields to populate them with setValue() and I use insertRecord() to insert the record. The insertRecord() says it succeeds. However, when I call submitAll() it fails with “NOT NULL constraint failed: Colors.color_name Unable to fetch row” where ‘color_name’ is the first ‘NOT NULL’ field. If I remove the ‘NOT NULL’ constraints from the table the submit succeeds but the fields are empty which tell me the insertRecord() did not insert the values. I have added extensive print statements and have verified that setValue() is indeed setting a value, it’s just not making it to the database.

        record = window.model.record()
        print('before record=', record)
        for name,value in self.updated_color_properties.items():
            print('--------------------------------------------------------')
            column = window.COLOR_TABLE_COLUMN_NAMES_TO_NUMBERS[name]

            print('name=', name, 'column=', column, 'field name=', record.fieldName(column))
            print('name=', name, 'column=', column, 'field=', record.field(name))
            assert(name == record.fieldName(column))

            if self.ColumnIsMandatory(name):
                print('mandatory: name=', name, 'column=', column, 'value=', value)
                assert(value is not None and value != '')
            if value is None or value == '':
                print('setNull: name=', name, 'column=', column, 'value=', value)
                record.setNull(name)
                print('actual value: column=', column, 'value=', record.value(column))
                print('actual value: name=', name, 'value=', record.value(name))
            elif self.ColumnIsText(name):
                print('setValue text: name=', name, 'column=', column, 'value=', str(value))
                record.setValue(name, str(value))
                print('actual value: column=', column, 'value=', record.value(column))
                print('actual value: name=', name, 'value=', record.value(name))
            else:
                print('setValue int: name=', name, 'column=', column, 'value=', int(value))
                record.setValue(name, int(value))
                print('actual value: column=', column, 'value=', record.value(column))
                print('actual value: name=', name, 'value=', record.value(name))
        print('--------------------------------------------------------')
        print('after record=', record)
        if not window.model.insertRecord(-1, record):
            print('!!INSERT ERROR!!')
            print(window.model.lastError().text())
        else:
            print('Insert success')
        if not window.model.submitAll():
            print('!!SUBMIT ERROR!!')
            print(window.model.lastError().text())
        else:
            print('submit success')

before record= <PySide6.QtSql.QSqlRecord(16)

0: QSqlField(“color_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: true, readOnly: false) “0”

1: QSqlField(“color_name”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

2: QSqlField(“bricklink_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

3: QSqlField(“bricklink_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

4: QSqlField(“rebrickable_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

5: QSqlField(“rebrickable_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

6: QSqlField(“ldraw_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

7: QSqlField(“ldraw_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

8: QSqlField(“category”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

9: QSqlField(“brickowl_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

10: QSqlField(“rgb”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

11: QSqlField(“cymk”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

12: QSqlField(“first_year”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

13: QSqlField(“last_year”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

14: QSqlField(“notes”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

15: QSqlField(“built_in”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0” at 0x102db47c0>


name= color_id column= 0 field name= color_id

name= color_id column= 0 field= <PySide6.QtSql.QSqlField(“color_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: true, readOnly: false) at 0x102e7d8c0>

mandatory: name= color_id column= 0 value= 777777

setValue int: name= color_id column= 0 value= 777777

actual value: column= 0 value= 777777

actual value: name= color_id value= 777777


name= color_name column= 1 field name= color_name

name= color_name column= 1 field= <PySide6.QtSql.QSqlField(“color_name”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) at 0x102e7d8c0>

mandatory: name= color_name column= 1 value= sevens

setValue text: name= color_name column= 1 value= sevens

actual value: column= 1 value= sevens

actual value: name= color_name value= sevens


other fields elided for brevity

name= built_in column= 15 field name= built_in

name= built_in column= 15 field= <PySide6.QtSql.QSqlField(“built_in”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) at 0x102e7d8c0>

setValue int: name= built_in column= 15 value= 0

actual value: column= 15 value= 0

actual value: name= built_in value= 0


after record= <PySide6.QtSql.QSqlRecord(16)

0: QSqlField(“color_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: true, readOnly: false) “777777”

1: QSqlField(“color_name”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “sevens”

2: QSqlField(“bricklink_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

3: QSqlField(“bricklink_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

4: QSqlField(“rebrickable_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

5: QSqlField(“rebrickable_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

6: QSqlField(“ldraw_id”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0”

7: QSqlField(“ldraw_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

8: QSqlField(“category”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “Solid”

9: QSqlField(“brickowl_name”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

10: QSqlField(“rgb”, QString, tableName: “Colors”, required: yes, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “777777”

11: QSqlField(“cymk”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

12: QSqlField(“first_year”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

13: QSqlField(“last_year”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

14: QSqlField(“notes”, QString, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “”

15: QSqlField(“built_in”, int, tableName: “Colors”, required: no, generated: yes, defaultValue: “QVariant(QString, “”)”, autoValue: false, readOnly: false) “0” at 0x102db47c0>

Insert success

!!SUBMIT ERROR!!

NOT NULL constraint failed: Colors.color_name Unable to fetch row

Upgrading to PySide6 6.9.1 makes no difference.

Solved it.

In general I don’t want the values in the model to be changed in the table view so the flags() method is overridden to prevent editing of most fields in the view. Apparently, insertRecord() goes through those the same methods to write the data as the table view does. I just have to allow every field to be writable when an insertion is happening.