When I go to '/' page for Finance it only shows the cash I have available

Question

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

Answer 1

A pointer as to where this is going wrong: the usd function returns a string, and datatypes in SQLite3.

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

Commands such as this

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) 

are effectively storing price and total as strings (TEXT). Anything that does arithmetic on price or total will evaluate to 0. EG: "SELECT SUM(total)

Suggest numbers be stored as numbers in the database and transformed to usd before being sent to the html.