Is there a way to use Python's variable replacement when updating Sqlite data targeting a known rowid? I am trying to say something like:
cursor.execute('INSERT INTO Words(rowid,f1,f2) VALUES(?,?,?)', [rowid,2,3])
The problem is that this fails with error:
sqlite3.IntegrityError: PRIMARY KEY must be unique
I want to write a list of values into the table's existing record without changing the rowid number. What's the accepted way to update all fields from a list of values?
You use UPDATE instead of INSERT:
cursor.execute('UPDATE Words SET f1=?, f2=? WHERE rowid=?', [2, 3, rowid])
This tells the database to update the f1 and f2 columns with new values, for all rows where rowid matches your specified value.
Alternatively, use the INSERT OR REPLACE syntax; if a uniqueness constraint (such as the primary key not being unique) is violated, an UPDATE is issued instead, but if rowid is not yet present a new row will be inserted:
cursor.execute('INSERT OR REPLACE INTO Words(rowid,f1,f2) VALUES(?,?,?)', [rowid,2,3])
