0

When I logged into the webpage and bought a stock, the price of the stock was deducted from the cash balance. However, the details of all the other fields are missing as shown in the picture below. I would appreciate it if someone could help me with where I have went wrong. Thank you. enter image description here

This is my code for index in application.py:

@app.route("/")
@login_required
def index():
"""Show portfolio of stocks"""
# Get data from portfolio database
stocks = db.execute('SELECT symbol, quantity FROM portfolio WHERE id= :id', id=session["user_id"])

#Initialisation of total worth (cash + share)
totalCash = 0

# Update the headers
for stock in stocks:
    symbol = stock["symbol"]
    quantity = stock["quantity"]
    quote = lookup(symbol)
    price = quote["price"]
    val = quantity*quote["price"]
    totalCash += val
    # Update portfolio table with latest data from lookup(symbol)
    # This must be within the scope of the *for loop*
    db.execute("UPDATE portfolio SET price=:price, \
                val=:val, WHERE id=:id AND symbol=:symbol", \
                price=quote["price"], \
                val=val, id=session["user_id"], symbol=symbol)


# Select user's current(updated) cash balance
newCash = db.execute("SELECT cash FROM users WHERE id = :id", id = session["user_id"])
# Update grand total (stocks' total value + cash)
totalCash += newCash[0]["cash"]

# Display updated table in index page
newPortfolio = db.execute("SELECT symbol, quantity, price, val FROM portfolio WHERE id=:id \
GROUP BY symbol ORDER BY symbol", id=session["user_id"])

return render_template("index.html", stocks=newPortfolio, cash=newCash[0]["cash"], total=totalCash)

This is my code in index.html:

{% extends "layout.html" %}

{% block title %}
    Index
{% endblock %}

{% block main %}
    <table style="width:100%">
        <tr>
            <th>Stock</th>
            <th>Number of shares</th>
            <th>Price</th>
            <th>Value of each holding</th>
            <th>Cash Balance</th>
            <th>Grand Total</th>
        </tr>
        {% for stock in stocks %}
            <tr>
                <td>{{ stock.symbol }}</td>
                <td>{{ stock.quantity }}</td>
                <td>{{ stock.price | usd }}</td>
                <td>{{ stock.val | usd }}</td>
                <td></td>
                <td></td>
            </tr>
        {% endfor %}
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>{{ cash | usd }}</td>
            <td></td>
        </tr>
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>{{ total | usd }}</td>
        </tr>


{% endblock %}
2
  • No idea what's happening (maybe in buy?), but your <table> is missing a closing tag. And this is at least weird - do you expect portfolio to contain multiple lines per symbol/user_id combination? If so, you would need aggregate functions, and your UPDATE would be wrong. But if there are no multiple lines per combination, GROUP BY makes no sense. And not sure why one would place the somewhat volatile current value in the database, and not just get it for displaying. The dicts returned by SELECT are mutable, you can add fields to them before passing to the template. – Blauelf May 23 '18 at 12:27
  • The problem has to be in the value returned from your query into newPortfolio. I'm trying to understand why you have a slash in the middle of your query string in db.execute() Did you add this just for the question or is this in your code? If you added print(newPortfolio) what shows in the console? – wlh May 24 '18 at 20:34
0

Here's the problem: UPDATE portfolio... The UPDATE command will only change existing rows that meet the conditions. If a row doesn't exist already, nothing happens.

The code needs to either insert a row, or if one exists already that meets the conditions specified, update the existing row. You should investigate and understand the following SQL commands: INSERT, ON DUPLICATE UPDATE, unique keys on two columns.

That should be enough hints. ;-)

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

5
  • Hey, thanks for the answer. I still have some doubts. Why is it that I can't use the UPDATE statement (at the location where I used) since the rows that meet the conditions already exist? I also don't understand if there is any difference between UPDATE and INSERT ON DUPLICATE KEY UPDATE - don't both statements do the same thing? – Prav Elan May 27 '18 at 4:41
  • First, I believe the UPDATE query probably is working, but just not doing what you think. IF the row with the userid and the stock symbol already exist, then it should add (or subtract) the quantity of shares to the existing number. I don't see any changes being made to the quantity field/column in the db table by the query. – Cliff B May 27 '18 at 5:33
  • Second, the difference is this. A straight UPDATE command will only update existing rows. If a row doesn't exist, i.e., if the stock hasn't been purchased yet, then nothing will happen. No row will be inserted. An INSERT / ON DUPLICATE KEY UPDATE will do both. If the row doesn't exist, it will add a row. If it does exist, it will update the row. Note that it depends on a key based on the combination of the userid field and the stock symbol field, a multi-column key, which is part of the design/setup of the table in the database. – Cliff B May 27 '18 at 5:35
  • Hmmm, I think I'm getting a hang of what's going on here. I replaced the whole UPDATE statement with the following statement but the same problem persists. Is it the syntax of my code or did I go wrong somewhere else? – Prav Elan May 31 '18 at 9:48
  • Here's the statement: db.execute("INSERT INTO portfolio(id, price, val, quantity, symbol) VALUES(:id, :price, :val, :quantity, :symbol) ON DUPLICATE KEY UPDATE price=:price, val=:val, quantity=:quantity", id=session["user_id"], price=price, val=val, quantity=quantity, symbol=symbol) – Prav Elan May 31 '18 at 9:48

You must log in to answer this question.

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