CODEWITHSUNDEEP
pythonsqlite
Yasser Altamimi
Yasser Altamimi

Reputation: 441

ON DUPLICATE KEY UPDATE cause a run time error

I have a joint primary key between two columns and I want to insert the columns the first time and after that update them with the old value + the new value, I am doing this using python and sqlite3

Here is my code:

db.execute(""" INSERT INTO transactions 
                          (user_id, name, symbol, shares, total)
                          VALUES 
                          (:user_id, :name, :symbol, :shares, :total ) 
                            ON DUPLICATE KEY UPDATE 
                            shares =shares+ :shares,
                            total = total + :total""",
                            user_id=session["user_id"],
                            name=q["name"],
                            symbol = q["symbol"],
                            shares=shares,
                            total=total
                          )

If the same symbol and user_id are inserted I want only to update shares and total as the old value of shares is added to the new value of share and the same for total, it's getting me a run time error near ON ,,, any help or tips is appreciated

update the error is :

RuntimeError: (sqlite3.OperationalError) near "ON": syntax error [SQL: " INSERT INTO transactions \n (user_id, name, symbol, shares, total)\n VALUES \n (16, 'Genpact Limited Common Stock', 'G', 5, 122.25 ) \n ON DUPLICATE KEY UPDATE \n shares = shares + VALUES(shares),\n total = total + VALUES(total)"]

Upvotes: 1

Views: 656

Answers (1)

CL.
CL.

Reputation: 180030

The INSERT statement does not have an ON DUPLICATE clause (in SQLite).

SQLite is an embedded database without client/server communication overhead, so it is not necessary to try to squeeze multiple things into a single SQL statement. Just do the update separately:

c = db.cursor()
c.execute("""UPDATE transactions
             SET shares = shares + :shares,
                 total = total + :total
             WHERE user_id = :user_id
               AND symbol = :symbol""",
          ...)
if c.rowcount == 0:
    c.execute("""INSERT INTO transactions ...""", ...)

Upvotes: 1

Related Questions