you need to check in the database user_id , product_id , voice if they coincide with the values ​​in the variable, then we will not add, and if only the voice values ​​do not match then overwrite it

 def add_rating(user_id, product_id, voice): conn = sqlite3.connect('db.sqlite') c = conn.cursor() c.execute("INSERT INTO User (user_id, product_id, voice) VALUES("+ user_id +","+product_id+", "+voice+")") conn.commit() conn.close()

1 answer 1

To update the voice value associated with a given pair (user_id, product_id) or add a new entry if the pair does not already exist in the table, you can use INSERT OR REPLACE as shown in the @gaspard answer to a similar question INSERT IF NOT EXISTS ELSE UPDATE? In the event of a conflict, if an entry with the user_id , product_id pair already exists, it will be deleted and the insertion will occur anyway (therefore, if the entry already exists, then fields not specified in the request can be replaced with the default value, for example, NULL —See a description of the conflict resolution algorithm ( REPLACE ):

 #!/usr/bin/env python import sqlite3 c = sqlite3.connect(':memory:') c.executescript(''' CREATE TABLE User (user_id INTEGER UNIQUE, product_id INTEGER UNIQUE, voice real); CREATE UNIQUE INDEX voice_idx ON User(user_id, product_id); ''') c.executemany('INSERT OR REPLACE INTO User VALUES(?,?,?)', [(1, 2, 3), (2, 3, 3), (1, 2, 5)]) print(list(c.execute("SELECT * FROM User")))

Result

 [(2, 3, 3.0), (1, 2, 5.0)]

It can be seen that the first record (1,2,3) was automatically deleted and replaced by a record (1,2,5) .

If there are other fields in the table that you want to save, you can use the approach from the @Chris Stavropoulos answer to the SQLite question - UPSERT not INSERT or REPLACE :

 #!/usr/bin/env python import sqlite3 c = sqlite3.connect(':memory:') c.executescript(""" CREATE TABLE User (user_id INTEGER UNIQUE, product_id INTEGER UNIQUE, voice real, name text); CREATE UNIQUE INDEX voice_idx ON User(user_id, product_id); INSERT INTO User values(1, 2, 3, 'abc'); """) for values in [(2, 3, 3), (1, 2, 5)]: with c: cur = c.execute('INSERT OR IGNORE INTO User(user_id, product_id, voice) ' 'values(?, ?, ?)', values) if cur.rowcount == 0: # already exists data = dict(zip("user_id product_id voice".split(), values)) cur.execute('update User set voice=:voice ' 'where user_id=:user_id and product_id=:product_id', data) print(list(c.execute("SELECT * FROM User"))) c.close()

Result

 [(1, 2, 5.0, 'abc'), (2, 3, 3.0, None)]

You can see that the name 'abc' value was saved when the voice updated to 5.0 .

UPSERT will probably be embedded in SQLite, starting with version 3.24 as an ON CONFLICT condition in an INSERT query (not tested):

 c.execute('INSERT INTO User VALUES(:user_id, :product_id, :voice)' ' ON CONFLICT(user_id, product_id) DO UPDATE SET voice=:voice', dict(user_id=1, product_id=2, voice=8))