`table.upsert/upsert_all` fails to write row when `not_null` is absent and the schema definition includes `not_null` about sqlite-utils

[alexwlchan]

I don't know if this is a clue, but while testing this I noticed that insert() seems to be completely ignoring the not_null parameter if it doesn't have to create the table, whereas upsert() doesn't. Here's another example:

from sqlite_utils import Database

db = Database(":memory:")

db["birds"].create(
    {"id": int, "name": str},
    pk="id",
    not_null={"name"},
)

db["birds"].insert({"id": 1, "name": "flamingo"}, not_null={"wingspan"})
print("insert complete!")

db["birds"].upsert({"id": 2, "name": "goldfinch"}, pk="id", not_null={"wingspan"})
print("upsert complete!")

In this case the insert() completes, but the upsert() fails with sqlite3.OperationalError: table birds has no column named wingspan.

The only way to make the insert() fail is to let it auto-create the table:

from sqlite_utils import Database

db = Database(":memory:")

db["birds"].insert({"id": 1, "name": "flamingo"}, not_null={"wingspan"})
print("insert complete!")

db["birds"].upsert({"id": 2, "name": "goldfinch"}, pk="id", not_null={"wingspan"})
print("upsert complete!")

at which point I get an error AssertionError: not_null set {'wingspan'} includes items not in columns {'id', 'name'}.

[alexwlchan]

A further, potentially interesting observation: this only seems to apply when you're inserting new rows. If your upsert is modifying an existing row, it works without supplying the not_null list.

Another example:

from sqlite_utils import Database

db = Database(":memory:")

db["birds"].create(
    {"id": int, "name": str, "color": str},
    pk="id",
    not_null={"name"},
)

db["birds"].insert({"id": 1, "name": "flamingo"})
print(next(db["birds"].rows))
# initial insert
# {'id': 1, 'name': 'flamingo', 'color': None}

db["birds"].upsert({"id": 1, "name": "goldfinch"}, pk="id")
print(next(db["birds"].rows))
# modifying the existing row
# {'id': 1, 'name': 'goldfinch', 'color': None}

db["birds"].upsert({"id": 1, "color": "blue"}, pk="id")
print(next(db["birds"].rows))
# modifying a column which is allowed to be non-null
# {'id': 1, 'name': 'goldfinch', 'color': 'blue'}

(That second case is how I spotted it – I was upserting into a table with not-null columns, but modifying a nullable column on an existing row.)

[petergaultney]

i can confirm all of the above.

upserts do not work on tables where there are not-null columns, but only the inserts do not work - updates work fine.

i am not sure if there would be a better fix, but one possible fix would be to query sqlite_master if not_null is DEFAULT, and if the table already exists, use its own reckoning of which columns are not_null. I am going to implement this fix in our fork.

One other thing worth noting - the documentation asserts that the pk argument is not necessary for upserts if you are certain that the table has already been created. However, that is not the case - upsert will raise an exception if you do not provide pk. My proposed fix of using sqlite_master to get the actual 'values' for not_null would also work as a way of filling in pk for existing tables, to match the documentation.