INSERT or REPLACE in sqlite3 WHERE column =

Question

I am using sqlite3 with python. My table stores datetime, and temperature. I would like to update the row as new data comes in, by updating rows with the same datetime, and insert new rows when no row with the specific datetime exists.

So, for example, if a row with datetime equal to '12 Mar 2014' exists, my code should update it or create a new record. But the following code, unfortunately always creates a new record regardless:

c.execute("INSERT or REPLACE INTO weather (datetime, temp) VALUES ('12 Mar 2014', 20)")

Any ideas appreciated! P.S. I know similar questions were asked before, but I am afraid I cannot make sense of how to apply the answers there to my own problem (being a rookie certainly does not help!)

Answer 1

To make INSERT OR REPLACE work, you need some constraint on the table, for example

datetime TEXT UNIQUE

Only when the insert would produce a constraint violation are the conflicting rows first deleted and then the new row inserted. Without the constraint there's no rows to be replaced.