windows 在插入 SQLite 时检查重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3281800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking duplicate while inserting in SQLite
提问by ukanth
I am trying to insert a data into SQLite database using Python.
我正在尝试使用 Python 将数据插入 SQLite 数据库。
INSERT INTO DATA_TABLE(UID,LABEL) VALUES (NULL, "UK")
WHERE "UK" NOT EXISTS IN (SELECT LABEL FROM DATA_TABLE);
This query is dynamically generated from Python and I am checking whether the date is already exist in the table before inserting and its not working in SQLite database.
Getting this near "WHERE": syntax errorerror.
这个查询是从 Python 动态生成的，我在插入之前检查日期是否已经存在于表中，并且它在 SQLite 数据库中不起作用。得到这个near "WHERE": syntax error错误。
Am I doing something wrong ?
难道我做错了什么 ？
Thanks for your help.
谢谢你的帮助。
采纳答案by Piskvor left the building
I'm pretty sure that INSERTdoesn't have a WHEREclause (the documentation doesn't mention any). What you can do:
我很确定INSERT没有WHERE子句（文档没有提到任何）。你可以做什么：
- create a unique index on
LABEL - use
INSERT OR FAIL - if that triggers an error, the row already exists.
- 在上创建唯一索引
LABEL - 用
INSERT OR FAIL - 如果触发错误，则该行已存在。
回答by msw
It is giving you a syntax error because it is not allowed syntax. From your example I presume the schema is probably:
它给你一个语法错误，因为它是不允许的语法。从您的示例中，我认为架构可能是：
create table data_table (uid integer primary key autoincrement.
label string);
in which case primary keyimplies unique. But, since you allow auto-generation of uidthen you don't care what it's value is, you just don't want duplicate labels in which case you actually care that labelbe unique so tell it so:
在这种情况下primary key意味着unique. 但是，既然你允许自动生成，uid那么你并不关心它的价值是什么，你只是不想要重复的labels 在这种情况下你实际上关心它label是唯一的所以告诉它：
create table data_table (uid integer primary key autoincrement,
label string unique on conflict fail);
which then works as expected:
然后按预期工作：
sqlite> insert into data_table (label) values ("uk");
sqlite> insert into data_table (label) values ("uk");
Error: column label is not unique
sqlite> select * from data_table;
1|uk
Incidentally, if the names data_table, uid, and labelaren't example names for the purposes of this question then you should use more meaningful names as these are horribly uninformative.
顺便说一句，如果名称data_table、uid和label不是用于此问题的示例名称，那么您应该使用更有意义的名称，因为这些名称非常缺乏信息。
回答by user396159
INSERT INTO DATA_TABLE(UID,LABEL) VALUES (NULL, "UK")
WHERE NOT EXISTS(SELECT 1 FROM DATA_TABLE WHERE LABEL="UK");
you can use this instead of INSERT OR FAIL.
您可以使用它代替 INSERT OR FAIL。
