For pset7, I have 3 tables (users, transactions, portfolio), in portfolio I want to go in and update the number of shares a user holds in a specific stock if they already own it, or add a new row if they don't.

Right now, I'm able to add rows for new purchases, but when I try to update a row to change the share amount, the query runs and debug50 throws no bugs, but the value doesn't change in the db.

#       check if user has this stock in portfolio
        update = db.execute("SELECT * FROM portfolio WHERE user_id = :id AND symbol = :symbol", id= session['user_id'], symbol = (request.form.get("symbol")).upper())
        if not update:
            # If not, insert it.
            db.execute("INSERT INTO portfolio(user_id, company_name, symbol, shares) VALUES (:user_id, :company_name, :symbol, :shares)", user_id=session["user_id"], company_name=stock['name'], symbol=stock['symbol'], shares=request.form.get("shares"))
             # If the do, update it.
            cur_shares = update[0]["shares"]
            new_shares = shares+cur_shares
            db.execute("UPDATE portfolio SET shares=:shares WHERE user_id=:user_id AND symbol=:symbol", shares=new_shares, user_id=session["user_id"], symbol=request.form.get("symbol"))
            # Update user table

I saw other posts with similar issues, but the syntax was different / didn't address the issue here. Schema for the portfolio table below.



The difference in the way symbol is being set in the two execute calls would explain the problem. If it tests if SYMBOL is held, then it should insert and/or update SYMBOL.

  • !!! Perfect, thank you.
    – Jake
    Aug 10 '18 at 20:29

You must log in to answer this question.

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