Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

insert into sqlite table with unique column

Tags:

python

sqlite

I'm inserting values into my table (from python code) as follows:

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

There is a unique constraint on column c. What is a common way of insert if I want to cover the case when we're inserting duplicate value for c column?
I have some ideas

  1. select everything from t to list and test before inserting if the value is already present
  2. try-catch block
  3. some more sophisticated sqlite statement

How would you test it?

thank you

like image 303
xralf Avatar asked Jun 04 '11 14:06

xralf


People also ask

How do I create a unique table in SQLite?

CREATE TABLE table_name ( ..., UNIQUE (column_name1,column_name2,...) ); Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation.

How to add columns in SQLite?

Now let’s see how they add column works into the SQLite as follows. The ADD COLUMN syntax is utilized to add another new column into the current table. The new column is constantly added to the furthest limit of the rundown of existing columns. The new column rule characterizes the qualities of the new segment.

What is unique constraint in SQLite?

Introduction to SQLite UNIQUE constraint A UNIQUE constraint ensures all values in a column or a group of columns are distinct from one another or unique. To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level.

How to use the ALTER TABLE statement in SQLite?

The only things you can do with the ALTER TABLE statement in SQLite is rename a table, rename a column, and add a new column to an existing table. Imagine we have the following table: And we now want to add a column called DOB. We could do that using the following code: It’s as simple as that.


2 Answers

You could use INSERT OR REPLACE to update rows with a unique constraint, or INSERT OR IGNORE to ignore inserts which 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 thing, though to test this you could use Python's timeit module to test the speed of various implementations. For example, you could run

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

versus

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

versus

python -mtimeit -s'import test' 'test.insert_with_try_catch_blocks()'
like image 113
unutbu Avatar answered Oct 28 '22 17:10

unutbu


Depends :)

If there is only one inserter the 1 might be the most efficient.

If there are several inserters you have to use 2 as under 1 you could test and seem OK but another inserter adds the C value you have so it fails

like image 22
mmmmmm Avatar answered Oct 28 '22 17:10

mmmmmm