Weird behavior with SQLite insert or replace

Question

I am trying to increment the count of a row in an SQLite database if the row exists, or add a new row if it doesn't exist the way it is done in this SO post. However I'm getting some weird behavior when I try to execute this SQL proc many times in quick succession. As an example I tried running this code:

db = connect_to_db()
c = db.cursor()
for i in range(10):
    c.execute("INSERT OR REPLACE INTO subject_words (subject, word, count) VALUES ('subject', 'word', COALESCE((SELECT count + 1 FROM subject_words WHERE subject = 'subject' AND word = 'word'), 1));")
    db.commit()
db.close()

And it inserted the following into the database

sqlite> select * from subject_words;
subject|word|1
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2
subject|word|2

Which totals to 19 entries of the word 'word' with subject 'subject'. Can anyone explain this weird behavior?

No, that's 10 entries, one for every iteration of your loop. What were you expecting? — Daniel Roseman, Apr 11 '15 at 17:20
Do you have any constraints that would be violated by this insertion, so that the replace clause is triggered? — Daniel Roseman, Apr 11 '15 at 18:11

score 1 · Accepted Answer · answered Apr 11 '15 at 18:54

I don't think you've understood what INSERT OR REPLACE actually does. The REPLACE clause would only come into play if it was not possible to do the insertion, because a unique constraint was violated. An example might be if your subject column was the primary key.

However, without any primary keys or other constraints, there's nothing being violated by inserting multiple rows with the same subject; so there's no reason to invoke the REPLACE clause.

score 1 · Answer 2 · answered Apr 12 '15 at 05:42

That operation is much easier to write and understand if you do it with two SQL statements:

c.execute("""UPDATE subject_words SET count = count + 1
             WHERE subject = ? AND WORD = ?""",
          ['subject', 'word'])
if c.rowcount == 0:
    c.execute("INSERT INTO subject_words (subject, word, count) VALUES (?,?,?)",
              ['subject', 'word', 1])

This does not require a UNIQUE constraint on the columns you want to check, and is not any less efficient.

Weird behavior with SQLite insert or replace

2 Answers2

Linked