WHERE NOT EXISTS SQL statement in sqlite3

Question

My code is this:

import sqlite3
def connect():
    conn = sqlite3.connect("books.db")
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer)")
    conn.commit()
    conn.close()
def insert(title, author, year):
    conn = sqlite3.connect("books.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO book VALUES (NULL, ?, ?, ?)", (title, author, year))
    conn.commit()
    conn.close()
connect()
insert("Title", "Author", 1950)

I would like to use the WHERE NOT EXISTS SQL statement in order to avoid duplicate inputs. I tried different ways of writing in the WHERE NOT EXISTS along the cur.execute() but keep getting:

sqlite3.OperationalError: near "WHERE": syntax error

score 1 · Answer 1 · answered Aug 20 '17 at 15:20

1

To avoid duplicate inserts, use a unique index or constraint:

create unique index unq_book_title_author_year on book(title, author, year);

This is better than not exists because the database enforces the uniqueness. Do note that the insert will be attempted and then fail, if duplicate insertions are attempted.

answered Aug 20 '17 at 15:20

Gordon Linoff

1,242,037
58
646
786

As @GordonLinoff suggests, a unique index will cause attempts to duplicate index entries to raise an exception. The key here is to ask yourself _what needs doing_ in the situation you wish to detect. Presumably the user should be made aware that the attempted entry already exists. – holdenweb Aug 20 '17 at 15:28
I see, unfortunately I don't seem to know how to implement the unique index for my code (keep getting a syntax error). Just out of curiosity, I would want as well to see the WHERE NOT EXISTS SQL statement at work in my code. – cidetto Aug 20 '17 at 15:37
@DanielC.Tremură . . . https://sqlite.org/lang_createindex.html – Gordon Linoff Aug 20 '17 at 16:49
Unfortunately the SQL `INSERT` statement is just that - insertion of a row into a table, with no conditions permissible. The syntax is described [here](https://sqlite.org/lang_insert.html). You might want to consider `INSERT OR REPLACE`, but this still won't pick duplicates up without the index. – holdenweb Aug 21 '17 at 08:03

WHERE NOT EXISTS SQL statement in sqlite3

1 Answers1