How to insert values that don't exist without syntax error?-CodePudding

I'm trying to add only values by ID that do not exist and I don't need to overwrite the existing ones. Maybe someone could tell how to fix this?

c.execute("INSERT INTO coins VALUES (:id, :symbol, :current_price, :market_cap,) SELECT * FROM coins WHERE 'id' != :id",
          {'id': ids["id"], 'symbol': ids["symbol"], 'current_price': ids["current_price"], 'market_cap': ids["market_cap"]})

CodePudding user response：

You could use an exists insert query:

INSERT INTO coins (id, symbol, current_price, market_cap)
SELECT ?, ?, ?, ?
WHERE NOT EXISTS (SELECT 1 FROM coins WHERE id = ?);

The Python code for this would be:

sql = """
INSERT INTO coins (id, symbol, current_price, market_cap)
SELECT :id, :symbol, :current_price, :market_cap
WHERE NOT EXISTS (SELECT 1 FROM coins WHERE id = :id)"""
c.execute(sql, {'id': ids["id"], 'symbol': ids["symbol"], 'current_price': ids["current_price"], 'market_cap': ids["market_cap"]})

Note that you might not even need to do this assuming that id already be a primary key or have a unique index on it. In that case, any insert attempting to use a duplicate id would fail at the database level.

CodePudding user response：

Something as simple as this:

INSERT OR IGNORE INTO coins VALUES (5, 'symbol', 'current_price', 'market_cap');

id just requires a corresponding unique constraint or primary key constraint.

Here's a test case:

Working test case