On sqlite-utils 4.2.1 and current main, a partial upsert() of an existing row raises IntegrityError when an omitted column is NOT NULL without a default, even though the row already has a valid value for that column. The generated INSERT ... ON CONFLICT omits that column from the insert, so its constraint is checked before the update can run.
Reproduction:
uv run --with sqlite-utils==4.2.1 python - <<'PY'
from sqlite_utils import Database
db = Database(memory=True)
dogs = db.table("dogs")
dogs.insert({"id": 1, "name": "Cleo", "color": "brown"}, pk="id", not_null={"name"})
dogs.upsert({"id": 1, "color": "black"})
print(dogs.get(1))
PYExpected: {'id': 1, 'name': 'Cleo', 'color': 'black'}.
Actual: sqlite3.IntegrityError: NOT NULL constraint failed: dogs.name. The row remains unchanged, including color='brown'.
The upsert documentation at 6bc1d33 says existing rows are updated and that “Any existing columns that are not referenced in the dictionary passed to .upsert() will be unchanged.”
The relevant SQL is built in Table.build_insert_queries_and_params(). Changing only the constructor to Database(memory=True, use_old_upsert=True) makes the same update succeed and preserves name='Cleo'.
Reproduced with Python 3.12.12 / SQLite 3.50.4, both on the published 4.2.1 package and main at 6bc1d33d583c54bd69fbdd2071117e2d38c354a1. The existing tests/test_upsert.py suite on that commit passes all 18 tests.
Prepared and reproduced by OpenAI Codex.
On sqlite-utils 4.2.1 and current main, a partial
upsert()of an existing row raisesIntegrityErrorwhen an omitted column isNOT NULLwithout a default, even though the row already has a valid value for that column. The generatedINSERT ... ON CONFLICTomits that column from the insert, so its constraint is checked before the update can run.Reproduction:
Expected:
{'id': 1, 'name': 'Cleo', 'color': 'black'}.Actual:
sqlite3.IntegrityError: NOT NULL constraint failed: dogs.name. The row remains unchanged, includingcolor='brown'.The upsert documentation at 6bc1d33 says existing rows are updated and that “Any existing columns that are not referenced in the dictionary passed to
.upsert()will be unchanged.”The relevant SQL is built in Table.build_insert_queries_and_params(). Changing only the constructor to
Database(memory=True, use_old_upsert=True)makes the same update succeed and preservesname='Cleo'.Reproduced with Python 3.12.12 / SQLite 3.50.4, both on the published 4.2.1 package and main at
6bc1d33d583c54bd69fbdd2071117e2d38c354a1. The existingtests/test_upsert.pysuite on that commit passes all 18 tests.Prepared and reproduced by OpenAI Codex.