This is a chunk of my code from buy:

if total_cost <= cash_balance[0]["cash"]:
        user_id = session["user_id"]
        cash_remaining = cash_balance[0]["cash"] - total_cost
        #db.execute("INSERT INTO transactions (user_id, stock, price, num_shares, total_cost) \
        #VALUES (:user_id, :stock, :price, :num_shares, :total_cost)",
        #user_id=session["user_id"], stock=stock, price=price, num_shares=num_shares, total_cost=total_cost)
        db.execute("INSERT INTO transactions (user_id, stock, price, shares, total_cost) \
        VALUES (:user_id, :stock, :price, :shares, :total_cost)",
        user_id=session["user_id"], stock=stock["symbol"], price=price, shares=shares, total_cost=total_cost),
        db.execute("UPDATE users SET cash=:new_cash WHERE id=:user_id;", new_cash=cash_remaining, user_id=user_id)
        db.execute("INSERT INTO portfolio (user_id, stock, shares) VALUES (:user_id, :stock, :shares) \

        ON DUPLICATE KEY UPDATE portfolio set shares = shares + newshares WHERE user_id = user_id AND stock = stock", \
        user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares)

I'm sure the last line is my problem, as I get the error "newshares is not defined". I set newshares as a variable earlier in buy, so I know I defined it somewhere, but not in this last statement. I can't figure out how it should be defined in the statement though, because at the end I have newshares = newshares


1 Answer 1


The proximate cause of the error is here set shares = shares + newshares. In this context it will be looking for newshares as a column in portfolio. You want newshares to be a bound variable, thus written :newshares. Pay attention to the WHERE clause also, it too appears to be missing the bindings.

sqlite3 does not have ON DUPLICATE KEY syntax. You might look into INSERT OR REPLACE in the sqlite3 doc. On a different page, it says

The ON CONFLICT clause described here has been a part of SQLite since before version 3.0.0 (2004-06-18).

A recent check of the sqlite3 version on the IDE shows3.8.2.

  • I tried changing newshares in the part you highlighted to :newshares but it still gives me an error. It says theres a syntax error near "ON"
    – Joel Banks
    Jan 2, 2019 at 0:18
  • Sorry, that escaped my notice. there is no ON DUPLICATE KEY in sqlite3. Answer has been emended. Jan 2, 2019 at 0:53
  • I'm confused as to how either or these could do the same thing as the on duplicate key part. How do I use them to check if there is already an existing place in the table with the values selected?
    – Joel Banks
    Jan 3, 2019 at 0:14
  • If it was me, I would test the result of the insert and if no records inserted (from the Hints in the spec: execute would return None) perform the update. Beware, CS50x has been updated to 2019 version. Be sure to read the announcements on the course home page. Jan 4, 2019 at 16:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .