I've been struggling for hours with trying to get the '/' page to display the current portfolio. It displays the remaining cash fine but nothing else. On phpliteadmin I can see that the table gets populated when I buy stocks but then it doesn't how what stocks are owned after it redirects to '/'.
My index function is:
@app.route("/")
@login_required
def index():
"""Show portfolio of stocks"""
stocks = db.execute("SELECT symbol, shares FROM portfolio WHERE id=:id", id=session["user_id"])
for stock in stocks:
symbol = stock["symbol"]
shares = stock["shares"]
current = lookup(symbol)
total = shares * current["price"]
db.execute("UPDATE portfolio SET price=:price, total=:total WHERE id=:id AND symbol=:symbol", price=usd(current["price"]), total=usd(total), id=session["user_id"], symbol=symbol)
absolute_total = db.execute("SELECT SUM(total) FROM portfolio WHERE id=:id", id=session["user_id"])
cash = db.execute("SELECT cash FROM users WHERE id=:id", id=session["user_id"])
if absolute_total[0]["SUM(total)"] is None:
updated_total_cash = cash[0]["cash"]
else:
updated_total_cash = cash[0]["cash"] + absolute_total[0]["SUM(total)"]
updated_portfolio = db.execute("SELECT * FROM portfolio WHERE id=:id", id=session["user_id"])
return render_template("index.html", stocks=updated_portfolio, total=usd(updated_total_cash), cash=usd(cash[0]["cash"]))
This really has me stumped as the table exists and the same SQL syntax (albeit for a different table) allows the cash remaining to be posted after the redirect along with my column headers, which shows that Jinja is working.
The following is my insertion of data within the portfolio table from the buy function:
user_share = db.execute("SELECT shares FROM portfolio WHERE id=:id AND symbol=:symbol", id=session["user_id"], symbol=quote["symbol"])
if user_share:
total_shares = shares + user_share[0]["shares"]
db.execute("UPDATE portfolio SET shares = :shares AND total= :shares * :price WHERE id= :id AND symbol= :symbol", shares=total_shares, price=usd(quote["price"]), id=session["user_id"], symbol=quote["symbol"])
flash ("Bought!")
return redirect("/")
else:
db.execute("INSERT INTO portfolio (user_id, symbol, name, shares, price, total) VALUES (:user_id, :symbol, :name, :shares, :price, :total)", user_id=session["user_id"], symbol=quote["symbol"], name=quote["name"], shares=shares, price=usd(quote["price"]), total=usd(total))
portfolio = db.execute("SELECT * FROM portfolio WHERE id=:id", id=session["user_id"])
flash ("Bought!")
return redirect("/")
My table consists of the headers within the code above and an ID primary key. Any pointers as to where this is going wrong would be really appreciated.
Thanks