CODEWITHSUNDEEP
sqlitesql-insertnotnullconstraint-handling-rules
Kipyneter
Kipyneter

Reputation: 3

SQLite Insert or replace AND On Conflict Do Nothing

I am working on a database that will be populated using a Python script that reads data from a Excel file and then executes SQLite Queries. The data in the Excel file will be updated regularly and in addition to that will also contain incomplete data. So I am trying to find a SQLite statement that will allow data to be inserted if the data is 'correct and new' or updated if the data is changed. I do not want to add NULL data to the database tables, because that would defeat the purpose of moving the data from a excel file to the DB. Also I do not want to filter the data in Python because I have a cool database that should be able to handle that. (If I can figure out how to tell it to actually do it)

First I am making a Table (I am doing this once on creation of the project)

CREATE TABLE IF NOT EXISTS 'ImportantTable'
(ID INTEGER NOT NULL,
Foo TEXT NOT NULL,
PRIMARY KEY (ID))

So far so good. Next step is to fill the table with data. Since I am using Python I wrote a function that dynamically creates a query that can work for multiple tables. Which works alright. The Query that does work looks like

INSERT OR IGNORE INTO 'ImportantTable'
VALUES 
   (0, 'Bar'),
   (1, 'Spam'),
   (2, 'MoreSpam'),
   (3, NULL)

The ID number will match with ab ID on the original Excel file, so it is alright if that skips.

The main issue I have is that with this method I cannot update the rows since it will ignore them if they are already existing. No problem I thought, I will use INSERT OR REPLACE. But then the Query aborts due to Not Null constraint errors.

I have tried adding ON CONFLICT DO NOTHING to the end of the query, but this gives me syntax errors. Code:

INSERT OR REPLACE INTO 'ImportantTable'
VALUES 
   (0, 'Bar'),
   (1, 'Spam'),
   (2, 'MoreSpam'),
   (3, NULL)
ON CONFLICT DO NOTHING

I have tried following the documentation Upsert-Clause but probably I am misunderstanding some things.

Some other variations of the ON CONFLICT I have tried are:

So, I am at a loss here on how to achieve this.

Upvotes: 0

Views: 1746

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131706

It seems "conflict" refers to different things in INSERT OR IGNORE and ON CONFLICT DO. You can both ignore nullability violations and update existing rows if you use :

INSERT OR IGNORE INTO 'ImportantTable'
VALUES 
   (0, 'Bar'),
   (1, 'Spam'),
   (2, 'Banana'),
   (3, NULL)
   
ON CONFLICT DO Update SET Foo=excluded.Foo;

As this Fiddle shows, the contents now are :

ID  Foo
0   Bar
1   Spam
2   Banana

The Conflict resolution page in SQLite actually warns about this (emphasis mine):

The ON CONFLICT clause described here has been a part of SQLite since before version 3.0.0 (2004-06-18). The phrase "ON CONFLICT" is also part of UPSERT, which is an extension to INSERT added in version 3.24.0 (2018-06-04). Do not confuse these two separate uses of the "ON CONFLICT" phrase.

Upvotes: 0

Kipyneter
Kipyneter

Reputation: 3

I did not find an answer to my specific question but I did find a good work around. I added DELETE FROM "TableName" before I import the data that I want to write. This is a bit of a Brute Force method. But it does the thing.

Upvotes: 0

Related Questions