插入具有唯一列的sqlite表 [英] insert into sqlite table with unique column

问题描述

我将值插入到我的表中(来自 python 代码)，如下所示:

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

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

在 c 列上有一个 唯一约束.如果我想涵盖我们为 c 列插入重复值的情况，insert 的常用方法是什么?
我有一些想法

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. 选择从 t 到 list 的所有内容并在插入之前测试该值是否已经存在
2. try-catch 块
3. 一些更复杂的sqlite语句

你会如何测试它?

谢谢

推荐答案

你可以使用 INSERT OR REPLACE更新具有唯一约束的行，或 INSERT OR IGNORE 忽略与唯一约束冲突的插入:

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

这些 sqlite 命令可能比编写 Python 代码来做同样的事情要快，但为了测试这一点，您可以使用 Python 的 timeit 模块来测试各种实现的速度.例如，您可以运行

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()'

对比

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

对比

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

这篇关于插入具有唯一列的sqlite表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！