Portfolio not updating in SQLite, Problem Set 7, Buy

Question

I've been having an issue in PSET7 buy where I am prompted with an error whenever I attempt to update my portfolio. Cash is deducted from the users total, but there is no new table containing the users portfolio. Here is my code for buy:

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
"""Buy shares of stock"""

if request.method == "POST":

    ## Ensure stock symbol was submitted and valid
    symbol = request.form.get("symbol")
    if not symbol:
        return apology("Must provide a valid symbol")

    ## Ensure positive number of shares
    shares = int(request.form.get("shares"))
    if shares < 0:
        return apology("Shares must be a positive integer")

    ## Check current price of stock
    result = lookup(symbol)

    ## Variable to access rows in database
    row = db.execute("SELECT * FROM users WHERE id = :id", id = session.get("user_id"))

    ## Check users current cash
    balance = row[0]['cash']

    ## Check total required to buy shares against users cash
    total = result['price'] * shares

    if balance < total:
        return apology("Insufficient funds")
    else:
        ## Update users cash
        db.execute("UPDATE users SET cash = :cash WHERE id = :id", cash = balance - total, id = session.get("user_id"))

        ## Update Portfolio
        db.execute("INSERT INTO portfolio (id, stock, price, quantity, total) VALUES (:id, :stock, :price, :quantity, :total)", id = session.get("user_id"), stock = result['symbol'], price = result['price'], quantity = shares, total = total)

        ## Buying more of same stock
        db.execute("UPDATE portfolio SET shares = shares + :shares WHERE id = :id AND symbol = :symbol", id = session.get("user_id"), symbol = result["symbol"], shares = shares)


        ## Insert Purchases Into History
        db.execute("INSERT INTO history (username, stock, price, quantity, timestamp) VALUES (:username, :stock, :price, :quantity, :timestamp)", \
                    username = row[0]['username'], stock = result['symbol'], price = float(result['price']), quantity = shares, timestamp = datetime('now'))


    return render_template("login.html")


else:
    return render_template("buy.html")

The issue appears to be stemming from the update portfolio portion, as I am prompted with this error in the command window while running flask, with the third line being coloured red:

DEBUG:cs50:SELECT * FROM users WHERE id = 1
DEBUG:cs50:UPDATE users SET cash = 6269.5 WHERE id = 1
DEBUG:cs50:INSERT INTO portfolio (username, stock, price, quantity, total) VALUES ('Tom', 'AAPL', 207.25, 1, 207.25)
[2018-08-09 02:53:54,186] ERROR in app: Exception on /buy [POST]

Any help with this issue would be much appreciated. Thankyou.

Answer 1

The error is caused by the SQL command to INSERT failing because the table 'portfolio' doesn't exist.

Create the table and then it should work. :)