Pset7 sell - list out of range

Question

So I am having trouble implementing the if function to test whether the number of shares the user is requesting to sell is less than the number of shares they have for that stock. Here's what I have right now, which is giving me a "list index out of range" error:

@app.route("/sell", methods=["GET", "POST"])
@login_required
def sell():

    if request.method == "POST":

        if not request.form.get("symbol"):
            return apology("must enter stock symbol")

        shares = int(request.form.get("shares"))

        if not shares:
            return apology("must enter number of shares")

        userdict = db.execute("SELECT SUM(shares) FROM users WHERE id = :id", id = session["user_id"])
        cash = userdict["cash"]

        stock = lookup(request.form.get("symbol"))
        name = stock['name']
        symbol = stock['symbol']
        price = stock['price']

        portfolio = db.execute("SELECT * FROM transactions WHERE stock = :stock GROUP BY stock", stock = stock)

        amount = -1*shares*price
        share_amount = portfolio[0]["shares"]

        if share_amount >= int(request.form.get("shares")):
            transaction = db.execute("INSERT INTO transactions (user, stock, shares, price, amount) VALUES (:username, :symbol, :shares, :price, :amount)", username=userdict["username"], symbol = stock['symbol'], shares = -1*int(request.form.get("shares")), price = stock['price'], amount = -1*shares*price)
            cashupdate = db.execute("UPDATE users SET cash = cash - (:amount) WHERE id = :id", amount = -1*shares*price, id = session["user_id"])

            sold = 'You sold %s shares of %s (%s) for %s.' % (shares, name, symbol, amount)
            return render_template("sold.html", name = sold)

        else:
            return apology("You don't have this many shares to sell.")

    else:
        return render_template("sell.html")

Basically, if I replace share_amount with a fixed integer (lets say 10), and want to sell 5 shares, it allows me to do so. However, I can't seem to figure out how to implement share_amount to represent the number of shares I have in my assets for a particular stock. Can someone help?

Answer 1

Can you explain SELECT SUM(shares) FROM users WHERE id = :id? SUM is an aggregate function, which combines multiple rows, however, I cannot imagine a use for multiple rows for a given user in a "users" table (unless the name is misleading).

Maybe instead of

portfolio = db.execute("SELECT * FROM transactions WHERE stock = :stock GROUP BY stock", stock = stock)
share_amount = portfolio[0]["shares"]

something like

in_depot = db.execute("SELECT SUM(shares) AS share_amount FROM transactions WHERE stock = :symbol AND user = :username", symbol = symbol, username=userdict["username"])
share_amount = in_depot[0]["share_amount"] if in_depot else 0

I'd prefer using the user id instead of the username in those tables.