比较Python列表与SQLite表并获取差异

Question

有没有一种SQL查询的方法，可以把一个Python列表和一个SQLite表进行比较，只找出那些还没有在表里出现的项目呢？

我在SQLite表里存储一些项目，当我的代码运行时，我想只存储那些新的项目，并且想要扩展我的表。

我知道把SQL查询的结果作为列表和我的Python列表进行比较是很简单的，但我不想把查询结果加载到内存中，因为我的表里有很多数据，而且我的代码还有多个实例同时在运行。

Answer 1

是的，你可以这样做……但我觉得你可能不想这么做。你的目标是只插入那些在表里不存在的项目，对吧？所以：

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

现在，在Python中打开这个数据库，然后：

>>> 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')]

你可以看到，它为 'baked beans' 插入了一行新数据，而之前的两行数据没有被动，因为它们已经存在了。

想了解冲突条款是怎么回事，可以查看 ON CONFLICT 的文档。（虽然 OR IGNORE 没有用 ON CONFLICT 来写，但它也是一种冲突条款。）

---

注意，这需要有一个可以触发冲突的约束条件——在我的例子中，就是 dish 列上的 UNIQUE 约束。如果没有这样的约束，你就得手动实现同样的效果（比如，用一个很复杂的子查询）。但几乎总是，正确的做法是添加这个约束。你的问题本身就隐含了这个值要么是一个键，要么是唯一的，否则“在表中不存在的项目”就没有意义了，所以你的数据模型应该反映这一点。