sqlite3, IntegrityError: UNIQUE constraint failed when inserting a value

Question

In order to prevent my database from growing too large I want sqlite only to insert values that has not yet been inserted. I've done some searching and figured the best way to do so was to use a UNIQUE constraint. It seems to me that that sqlite crashes when inserting a value that is not UNIQUE, how do I circumvent this error and continue with the next submission?

Below is some relevant code.

sql = sqlite3.connect('submissions.db')
cur = sql.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS some_table(id TEXT UNIQUE)')
sql.commit()

for thing in things:
    try:
        # Do some stuff
    except AttributeError:
        pass

    cur.execute('INSERT INTO some_table VALUES(?)', [thing])
    sql.commit()

Here is the traceback:

Traceback (most recent call last):
  File "D:\Directory\Python\Projects\Oddshotcrawler for Reddit, globaloffensive\oddshotcrawler.py", line 62, in <module>
    oddshotcrawler()
  File "D:\Directory\Python\Projects\Oddshotcrawler for Reddit, globaloffensive\oddshotcrawler.py", line 54, in oddshotcrawler
    cur.execute('INSERT INTO oldposts VALUES(?)', [thing])
sqlite3.IntegrityError: UNIQUE constraint failed: some_table.id
[Finished in 7.1s with exit code 1]

stackoverflow.com/a/45299979/6665568 . This answer fully fixed the issue for me . Handles cases if u directly wanna replace if row already present. — Natesh bhat
– Natesh bhat, Commented Mar 18, 2019 at 13:05

Colonel Thirty Two · Accepted Answer · 2016-04-09 15:58:05Z

38

Change INSERT to INSERT OR IGNORE:

cur.execute('INSERT OR IGNORE INTO oldposts VALUES(?)', [submID])

answered Apr 9, 2016 at 15:58

Colonel Thirty Two

27k9 gold badges52 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

the-citto Over a year ago

+1 for my needs I very much prefer this than EAFP (easier to ask for forgiveness than permission) with python

Sharon Zak · Accepted Answer · 2018-02-26 20:31:03Z

12

Change "INSERT" to "INSERT OR REPLACE" it's better...

Update_Table = """INSERT OR REPLACE INTO station_statusDB VALUES(?, ?, ?, ?);"""
with sql.connect(station_statusDB) as Conn:
    print("Connected to:", station_statusDB)
    Conn.execute(New_Station_Table)
    while Client_Flag != 0:
        print("Updating Station Database...")
        Conn.execute(Update_Table, (ClientID, Client_date, Client_Alarm, Client_Water))
        print("Done!, Saving...")
        Conn.commit()

answered Feb 26, 2018 at 20:31

Sharon Zak

1211 silver badge2 bronze badges

Collectives™ on Stack Overflow

sqlite3, IntegrityError: UNIQUE constraint failed when inserting a value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related