Upsert with compound primary key

[Isaac-Flath]

upsert does not work as I would expect with a compound primary key. The workaround I use is to use insert with replace=True to accomplish what I think upsert should do.

This is a minimal repro of the issue I ran into when developing on the solveit app: https://gist.github.com/Isaac-Flath/cc9b3c5d28219725235b00241ce4082a

FYI @pydanny @audreyfeldroy @jph00

[pydanny]

In fastlite/sqlite-minutils upsert is coded entirely in Python, but you've found a way to get that logic through SQLite dialogue through the use of INSERT OR REPLACE.

Interestingly, Sqlite supports two upsert methods:

1. INSERT OR REPLACE - What @Isaac-Flath discovered
2. ON CONFLICT DO - What AI and search suggests we should do

If my reading of the specs is correct, I think OR REPLACE is closer to what we want implemented. So for me it will be interesting to see how your workaround addresses data quality.

[pydanny]

Apparently the problem with INSERT OR REPLACE is it ignores integrity errors, just sticks in null values because the engine allows that. Exploring what that means.