SQLite3 - 防止将重复条目输入数据库

问题描述

首先，在我问的问题中提前道歉太初级了 - 我是一个 SQLite 新手，刚刚开始尝试使用 Python 和 SQLite3 完成一个简单的任务.

First off, apologies in advance in the question I ask is too elementary - I am very much a SQLite neophyte just getting his feet wet and trying to accomplish a simple task with Python and SQLite3.

我有一个带有两个文本字段的 SQLite 数据库文件 (notify_users):language_code 和 username.我正在使用此执行代码将信息输入数据库，例如:cursor.execute("INSERT INTO notify_users VALUES ('de', 'jane_doe')"):

I have a SQLite database file (notify_users) with two text fields: language_code and username. I'm using this execute code to enter information into the database, e.g.: cursor.execute("INSERT INTO notify_users VALUES ('de', 'jane_doe')"):

可视化，然后，数据库看起来像这样:

Visualized, then, the database looks like this:

| language_code | username      |
---------------------------------
| de            | jane_doe      |
| sv            | jane_doe      |
| de            | tom_petty     |

等等.虽然一个用户名可以与多个 language_codes 相关联，但我想防止重复输入相同的 language_code 和用户名.

and so on. While it's okay for one username to be associated with multiple language_codes, I'd like to prevent a duplicate entry with both the same language_code and username.

我应该如何更改 INSERT INTO 代码，以便它不会创建一个新条目，如果它在两个字段中与已经存在的字段完全相同?

How should I change the INSERT INTO code so that it will not make a new entry if it's going to be exactly the same in both fields as one that already exists?

| language_code | username      |
---------------------------------
| de            | jane_doe      |
| de            | jane_doe      | X # Don't want this duplicate entry to be entered.

谢谢，如果我能做些什么来更准确或更具体地解决这个问题，请告诉我.

Thanks, and please let me know if there's anything I can do to be more precise or specific about this question.

推荐答案

让数据库来做这项工作.创建唯一索引.这将防止这种重复:

Let the database do the work. Create a unique index. This will prevent such duplication:

create unique index unq_notify_users_2 on notify_users(language_code, username);

它通过产生错误来防止重复.尽管最好在数据库中进行检查，但您可以通过执行以下操作来避免错误:

It prevents duplication by generating an error. Although it is best to do the check in the database, you can avoid the error by doing something like this:

insert into notify_users(language_code, username)
    select language_code, username
    from (select $language_code as language_code, $username as username) lu
    where not exists (select 1
                      from notify_users nu
                      where nu.language_code = lu.language_code and
                            nu.username = lu.username
                     );

这篇关于SQLite3 - 防止将重复条目输入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！