Insert into sqlite table with unique column

Question

I am inserting values ​​into my table (from python code) as follows:

cur.execute("insert into t(a, b, c) values (?, ?, ?)", (a, b, c)) 

Column c has unique constraint . What is the general insert method if I want to consider the case where we insert a duplicate value for column c?
I have some ideas

• select all of t in the list and check before inserting if the value is already present.
• try-catch block
• more complex sqlite statement

How would you test it?

Thank you

Answer 1

You can use INSERT OR REPLACE to update rows with a unique constraint, or INSERT OR IGNORE to ignore inserts that conflict with a unique constraint:

 import sqlite3 def insert_or_replace(): # https://sqlite.org/lang_insert.html connection=sqlite3.connect(':memory:') cursor=connection.cursor() cursor.execute('CREATE TABLE foo (bar INTEGER UNIQUE, baz INTEGER)') cursor.execute('INSERT INTO foo (bar,baz) VALUES (?, ?)',(1,2)) cursor.execute('INSERT OR REPLACE INTO foo (bar,baz) VALUES (?, ?)',(1,3)) cursor.execute('SELECT * from foo') data=cursor.fetchall() print(data) # [(1, 3)] def on_conflict(): # https://sqlite.org/lang_insert.html connection=sqlite3.connect(':memory:') cursor=connection.cursor() cursor.execute('CREATE TABLE foo (bar INTEGER UNIQUE, baz INTEGER)') cursor.execute('INSERT INTO foo (bar,baz) VALUES (?, ?)',(1,2)) cursor.execute('INSERT OR IGNORE INTO foo (bar,baz) VALUES (?, ?)',(1,3)) cursor.execute('SELECT * from foo') data=cursor.fetchall() print(data) # [(1, 2)] insert_or_replace() on_conflict() 

These sqlite commands are probably faster than writing Python code to do the same, although you can use the Python timeit module to check the speed of various implementations to verify this. For example, you can run

 python -mtimeit -s'import test' 'test.insert_or_replace()' 

against

 python -mtimeit -s'import test' 'test.filter_nonunique_rows_in_Python()' 

against

 python -mtimeit -s'import test' 'test.insert_with_try_catch_blocks()' 

Answer 2

It depends :)

If there is only one inserter, 1 may be most effective.

If there are several inserts, you need to use 2 as under 1, you can test and seem OK, but another inserter adds the value of C that you have so that it does not work