I'm adding once a week simple timeseries data like this: (each week will be new date so record will be different)
timestamp,number1,number2,number3
2020-08-05,111,222,333
2020-08-12,444,555,666
I'm trying to avoid duplicated entries in case, if I would run code twice or three times within one week for example. I tried commands "INSERT OR REPLACE INTO" or "INSERT OR IGNORE INTO" and both commands add duplicates at every run, unless I have set table1, key: timestamp as UNIQUE. Is this how it should work?
Shouldn't "INSERT OR REPLACE" delete the record then insert it again, that way avoiding duplicate entry?
Shouldn't "INSERT OR IGNORE" ignore writing duplicate record? (not arguing, just asking, I'm beginner :) )
My problem is, when I created database + table, I didn't know I need to set UNIQUE to one of the keys. Can I edit my database/table now, afterward, and set UNIQUE key, after table is already populated with data? If not, what are my options? Bellow is simplified code, I'm using:
add_data = [timestamp, number1, number2, number3]
conn = sqlite3.connect('./data.db')
c = conn.cursor()
c.execute("INSERT OR REPLACE INTO table1 VALUES (?,?,?)", add_data)
# c.execute("INSERT OR IGNORE INTO table1 VALUES (?,?,?)", add_data)
conn.commit()
conn.close()