CODEWITHSUNDEEP
pythondatabasepython-3.xsqlite
John Smith
John Smith

Reputation: 135

SQLite3 - Preventing duplicate entries from being entered into a database

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.

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     |

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.

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.

Upvotes: 1

Views: 2411

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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
                     );

Upvotes: 3

Related Questions