Pset7 Finance Buy Function Internal Server Error

Question

When I try to insert the portfolio values into the SQL database I receive an internal server error, please can someone tell me what I'm doing wrong.

if request.method == "POST":

    # ensure symbol field isn't blank
    if not request.form.get("symbol"):
        return apology("must provide symbol", 403)

    # ensure shares field isn't blank
    elif not request.form.get("shares"):
        return apology("must provide amount", 403)

    # check validity of amount
    try:
        amount = int(request.form.get("shares"))
    except:
        return apology("please provide integer")

    # check validity of symbol
    if lookup(request.form.get("symbol")) == None:
        return apology("invalid symbol")
    else:
        quote = lookup(request.form.get("symbol"))

    # calculate total price
    total = quote["price"]*amount
    cash = db.execute("SELECT cash FROM users WHERE id = :id", session["user_id"])

    # check if user has sufficient cash
    if(cash >= total):
        db.execute("INSERT INTO portfolio (id, price, number, symbol, when) VALUES (:id, :price, :number, :symbol, datetime('now'))", session["user_id"], quote["price"], amount, quote["symbol"])
    else:
        return apology("insufficient cash for chosen stock")

    return render_template("buy.html")

else:
    return render_template("buy.html")

Answer 1

Would have been great if you had mentioned the exact location and message of the error.

db.execute expects you to use named parameters. They work like

db.execute("QUERY STRING :var MORE STRING", var=value)

The name left of the = has to correspond to the : name in the query string, the right side is the value to replace that with.

So the line would be something like

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

and

db.execute("INSERT INTO portfolio (id, price, number, symbol, when) VALUES (:id, :price, :number, :symbol, datetime('now'))", id=session["user_id"], price=quote["price"], number=amount, symbol=quote["symbol"])

Random thoughts, probably those are related to the early stage of your code:

You could have the datetime('now') as an autovalue in the database, removes it from the query.

You don't test for the number of shares being positive, so one can buy negative amounts to make loads of money :-D

But that doesn't work yet, as you don't subtract the money from the account :-/

The returned page does not show any sign the action was actually successful.

403 is HTTP code for "Forbidden", not sure if that's right here.

You lookup the same twice, do that once before the if (then without the else). Lookups are actually a bit expensive (both in request time and the number of API calls allowed per time unit for free)