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