0

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

0

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.

2
  • Ah ok, thank you for the advice. I've changed all these incidences as it is definitely better storing these values as numbers as oppsed to strings. However the problem appears to be deeper than that as when I return my portfolio to index.html through 'updated_portfolio' it appears the list is empty. This is confirmed as when I try to print the list all I see in the console is []. In phpliteadmin however I can see a populated table so I'm not sure why it isn't selecting the values from it. – Matthew Harding Dec 15 '18 at 9:48
  • I feel so stupid! It was my use of an id primary key column that was throwing it all out, as I was then asking it to look through this column for the session["user_id"], which will only occurs by fluke. I re made the table dropping the id column and making the user_id and symbol the primary keys and then it returned a 500, saying column id doesn't exist. This was how I finally realised my mistake. Thank you very much for the advice regarding the use of usd. – Matthew Harding Dec 15 '18 at 11:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .