run time error when using joint primary key

Question

I have a joint primary key between two columns (symbol and user_id) and I want to insert the columns the first time and after that update them with the old value + the new value,

Here is my insert 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 + VALUES(:shares1),
                            total = total + VALUES(:total1)""",
                            user_id=session["user_id"],
                            name=q["name"],
                            symbol = q["symbol"],
                            shares=shares,
                            total=total
                          )

that gives me this error:

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(5),\n total = total + VALUES(122.25)"]

and here is my full function :

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock."""

    # if user reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        #remember symbol if it exists
        if not request.form.get("symbol"):
            return apology("must provide symbol")
        else:
            symbol = request.form.get("symbol")

        #is the symbol valid?
        q = lookup(symbol)
        if q == None:
            return apology("symbol not correct")



        #remember shares if it exists and casting it to an int
        if not request.form.get("shares"):
            return apology("must provide shares")
        else:
            try:
                shares = int(request.form.get("shares"))

            except:
                return apology("invalid shares")

            if shares <= 0 :
                return apology("invalid shares")

            # get cash amount from users
            rows = db.execute("SELECT cash FROM users WHERE id = :id", id=session["user_id"])
            cash = rows[0]["cash"]

            # calculate total amount of purchase
            total = q["price"] * shares

            # not enough cash?:
            if total > cash:
                return apology("no enough mony")

            # update cash in user table
            cash -= total
            db.execute("""UPDATE users
                         SET cash = :cash
                         WHERE id = :id""",
                         cash=cash,
                         id=session["user_id"])

            # passed all these conditions? buy it and add it to the table
            db.execute(""" INSERT INTO transactions 
                          (user_id, name, symbol, shares, total)
                          VALUES 
                          (:user_id, :name, :symbol, :shares, :total ) 
                            ON DUPLICATE KEY UPDATE 
                            shares = shares + VALUES(:shares1),
                            total = total + VALUES(:total1)""",
                            user_id=session["user_id"],
                            name=q["name"],
                            symbol = q["symbol"],
                            shares=shares,
                            total=total
                          )

        # secuess
        return apology("secuess")

    # else if user reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("buy.html")

I don't really know if it's a syntax error or what as I've tried those method without success :

ON DUPLICATE KEY UPDATE shares = shares + :shares

and:

ON DUPLICATE KEY UPDATE shares = shares + VALUES(:shares)

and:

ON DUPLICATE KEY UPDATE 'shares' = 'shares' + VALUES(:shares)

and few others but all result into the same error. Thanks A lot

Answer 1

'ON DUPLICATE KEY UPDATE' is MySQL syntax. But is this really what you need to do? Remember from the spec:

Add one or more new tables to finance.db via which to keep track of the purchase. Store enough information so that you know who bought what at what price and when.

In order to keep the required level of detail, each buy transaction must be one row in the table. A primary key of symbol and user_id won't work if user can purchase more shares of an owned stock. Since you need to know "when" a transaction occurred, you probably need a DATETIME field in the table. That could be part of the primary key. Or better yet, create the table with an autoincrement key, like the user table, and add (non-unique) index on symbol,user_id.

For your reference, here is the sqlite INSERT documentation.

Hint: the syntax for current date and time is datetime('now')