I'm completely noob, beginner in Python, first time dealing with database. I didn't get any error running upper SQLite commands, but table didn't change, there is no UNIQUE property for timestamp and there is no collusion, same data is being added, which tells me, database and table are the same as before i ran upper commands. Test3.db is mock database with same schema I mentioned in first post with one record already inserted, to test if I can create unique index). ![]() Sqlite> create unique index table1.timestamp on table1(timestamp) Then I tried directly inside SQLite3 from the terminal. I python I ran these commands, I tried several different variations what you suggested:Ĭ.execute(create unique index table1.timestamp on table1(timestamp)) c.execute(create unique index table1timestamp on table1(timestamp)) c.execute(create unique index table1(timestamp) on table1(timestamp)) c.execute(CREATE UNIQUE index table1 timestamp on table1(timestamp)) I tried your command several times, through Python and directly inside SQLIte environment (never before I have ran SQLite command on it's own, so I probably made a mistake. Can I create it on already existing batabase/table? I have question about creating unique index. From now on, whenever I will create new database, I will make sure I will have UNIQUE set if I will want to prevent duplicate entries. This also explain, why my testing database works when I set UNIQUE to one key. So, my python script is working as intended. ![]() Keith Medcalf, thank you very much for detailed response. So the long and short of it is that before a "conflict resolution method" will be invoked, there must be a conflict which the specified "resolution method" will be capable of resolutioning. The "IGNORE" conflict resolution method means that the fact of the conflict is ignored and the insert is ignored. The "REPLACE" conflict resolution method means that conflicting records (in this case those with the same timestamp value) will be deleted and then the new record inserted. This will require the use of the "conflict resolution method" to handle if you do not want to "see" an error being "bubbled up" to the application. ![]() Then a conflict will arise if you attempt to INSERT a record with a duplicate timestamp into the table1 table. So, if you create a unique index: create unique index table1timestamp on table1(timestamp) ((Note that this may not be the case in all RDBMS implementations, however, it is the case for SQLite3)) (Syntactic sugar means that it does the same thing but is a different (sweeter, probably as in simpler) way of expressing the same result. The former is merely syntactic sugar for the latter. So if you want the column table1.timestamp to be unique you can either declare that column to be unique in the table declaration OR create a unique index on that column. However, you have not specified "a conflict", so without a "conflict" to resolve, the "conflict resolution method" is never invoked. In order for them to have any effect whatsoever, you must firstly have a conflict. "OR REPLACE" and "OR IGNORE" are what are known as "conflict resolution method". # c.execute("INSERT OR IGNORE INTO table1 VALUES (?,?,?)", add_data) 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 = Ĭ.execute("INSERT OR REPLACE INTO table1 VALUES (?,?,?)", add_data) ![]() My problem is, when I created database + table, I didn't know I need to set UNIQUE to one of the keys. Shouldn't "INSERT OR IGNORE" ignore writing duplicate record? (not arguing, just asking, I'm beginner :) ) Shouldn't "INSERT OR REPLACE" delete the record then insert it again, that way avoiding duplicate entry? 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. I'm trying to avoid duplicated entries in case, if I would run code twice or three times within one week for example. 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
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |