How do I update a record in sqlite3 in python

Question

Here, in the update part a new duplicate is getting created instead of updating the existing record. Please help!

def addBooks(title, quantity):
    c.execute('SELECT * FROM books')
    for i, j in c.fetchall():
        if i == title:
            c.execute('UPDATE books SET quantity = ? WHERE title = ?', (j+quantity, title))
            conn.commit()
        else:
            c.execute('INSERT INTO books VALUES (?, ?)', (title, quantity))
            conn.commit()

Did you mean for the `else:` to be attached to `for` rather than `if`? — Barmar, Jun 23 '21 at 04:26
If so, you need to `break` from the loop when you find the matching row. — Barmar, Jun 23 '21 at 04:27
That's why you're getting duplicates. For every row with a different title, you're inserting a new row with the title you're searching for. — Barmar, Jun 23 '21 at 04:29
Are you trying to update if the title is already in the table, and insert if it isn't? — Barmar, Jun 23 '21 at 04:30
If so, see https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Barmar, Jun 23 '21 at 04:30

score 0 · Answer 1 · answered Jun 23 '21 at 04:24

I think you just want an update here:

def addBooks(title, quantity):
    c.execute('UPDATE books SET quantity = quantity + ? WHERE title = ?', (quantity, title,))
    conn.commit()

Your current code has multiple problems. First of all, doing SELECT * in your first (unnecessary) select is semantically incorrect, because you should be explicitly listing out which columns you want. Also, there is no need to iterate the result set from this select to update each record. UPDATE is already a set-based operation which will target every record in the table. There is also no need to be doing an INSERT here if I understand your requirement.

Thank you, I'll try verifying my errors. – Gopal Matcha Jun 23 '21 at 04:33 — Gopal Matcha, Jun 23 '21 at 04:33

How do I update a record in sqlite3 in python

1 Answers1