Compare a Python list to SQLite table and get difference

Question

Is there an SQL query based solution to compare a Python list to an SQLite table and get those items only which are not present in the table yet?

I store items in my SQLite table and as my code runs I would like to store only those items which are new and want to extend my table.

I know that it would be easy to compare the SQL result as a list with my Python list but I don't want to load the query result to memory since my table contains lots of data, furthermore I have more instances of my code running at the same time.

Answer 1

Yes, you can do this… but I don't think you want to. Your goal is to only insert items that are not present in the table, right? So:

CREATE TABLE Breakfast (id INTEGER PRIMARY KEY AUTOINCREMENT, dish UNIQUE)
INSERT INTO Breakfast (dish) VALUES ('spam')
INSERT INTO Breakfast (dish) VALUES ('eggs')

Now, in Python, open this database, then:

>>> breakfast = ['spam', 'eggs', 'baked beans']
>>> db.execute('SELECT * FROM Breakfast').fetchall()
[(1, 'spam'), (2, 'eggs')]
>>> db.executemany('INSERT OR IGNORE INTO Breakfast (dish) VALUES(?)',
                   [[dish] for dish in breakfast])
>>> db.execute('SELECT * FROM Breakfast').fetchall()
[(1, 'spam'), (2, 'eggs'), (5, 'baked beans')]

As you can see, it inserted a new row for 'baked beans', while leaving the two existing rows untouched because they were already there.

See the ON CONFLICT documentation for how conflict clauses work. (Even though OR IGNORE isn't spelled with ON CONFLICT, it's a conflict clause.)

---

Notice that this requires having a constraint that can trigger the conflict in the first place—in my example, it's that UNIQUE on the dish column. If you don't have such a constraint, you have to reproduce the same effects manually (e.g., with a horrible, ugly sub-SELECT). But almost always, the right answer is to add the constraint. Your problem statement implicitly assumes that the value is either a key or otherwise unique, or "items that are not present in the table" really wouldn't make any sense, so your data model should reflect that.