PSET7 variable not defined for sql statement

Question

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)
        #PDHTEFW7T4KOTDHI
        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

Thanks!

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.