CS50 Finance - How to correctly display looped values in Index

new programmer here taking CS50x. Working on the Finance problem set wherein I am creating a website where a user can buy and sell stocks and view their transaction history. Using flask, Jinja and Python.

For the transaction history I am grabbing data from two places: 1) a database I have set up, 2) via a lookup function using an API.

My problem is that the values coming from the API are correct, but the values for the last row are overwriting the values for all other rows. I suspect this is a looping problem but cannot figure out the solution. I have tried reading Jinja documentation as well as changing 'portfolio' to a list of dictionaries, changing my Jinja code from 'portfolio.price' to 'price' and 'row.price' etc. And I've looked for answers on other stack overflow queries.

Here is what my page currently looks like: Stocks Portfolio index. And here is my Python code:

@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""

# Access portfolio info from database
rows = db.execute("SELECT name, symbol, SUM(shares) AS shares FROM purchases WHERE users_id = ? GROUP BY name, symbol", session["user_id"])

# Access cash balance for user
user_info = db.execute("SELECT * FROM users WHERE id = ?", session["user_id"])
cash = user_info[0]["cash"]

if not rows or not cash:
    return render_template("empty_portfolio.html")

else:
    # Declare a dictionary to hold database info
    portfolio = {}
    sum_total_value = 0

    # Iterate over database info and add it to portfolio
    for row in rows:
        portfolio.update(row)

        name = row["name"]
        symbol = row["symbol"]
        shares = row["shares"]

        stock = lookup(symbol)
        price = stock["price"]
        total_value = shares*price
        portfolio["price"] = price
        portfolio["total_value"] = total_value
        print("PORTFOLIO VALUES INSIDE LOOP:", portfolio)

    print("PORTFOLIO VALUES OUTSIDE LOOP:", portfolio)
    sum_total_value += total_value
    grand_total = cash + sum_total_value

    # deliver the iterations and dictionary info to the html page
    return render_template("index.html", rows=rows, portfolio=portfolio, price=price total_value=total_value, cash=cash, grand_total=grand_total)

And html:

<table>
    <thead>
        <tr>
            <th>Stock</th>
            <th>Symbol</th>
            <th>Shares</th>
            <th>Price</th>
            <th>Total Value</th>
        </tr>
    </thead>
    <tbody>
        {% for row in rows %}
            <tr>
                <td>{{ row.name }}</td>
                <td>{{ row.symbol.upper() }}</td>
                <td>{{ row.shares }}</td>
                <td>{{ portfolio.price | usd }}</td>
                <td>{{ portfolio.total_value | usd}}</td>
            </tr>
        {% endfor %}
            <tr>
                <td>Cash Balance: {{ cash | usd }}</td>
            </tr>
            <tr>
                <td>GRAND TOTAL: {{ grand_total | usd }}</td>
            </tr>
    </tbody>
</table>
{% endblock %}

I also attempted debugging. Here is my terminal. Please note I am aware it is a bad idea to share API keys. This is a student project - never being taken out of the development sphere. I have not learned how to hide this key in my terminal yet.

DEBUG: Starting new HTTPS connection (1): cloud.iexapis.com:443 DEBUG: https://cloud.iexapis.com:443 "GET /stable/stock/aapl/quote?token=pk_05c43fc7cb604812a604a863e7a0153c HTTP/1.1" 200 None PORTFOLIO VALUES INSIDE LOOP: {'name': 'Apple Inc', 'symbol': 'aapl', 'shares': 4, 'price': 131.81, 'total_value': 527.24} DEBUG: Starting new HTTPS connection (1): cloud.iexapis.com:443 DEBUG: https://cloud.iexapis.com:443 "GET /stable/stock/nflx/quote?token=pk_05c43fc7cb604812a604a863e7a0153c HTTP/1.1" 200 None PORTFOLIO VALUES INSIDE LOOP: {'name': 'NetFlix Inc', 'symbol': 'nflx', 'shares': 10, 'price': 553.885, 'total_value': 5538.85} DEBUG: Starting new HTTPS connection (1): cloud.iexapis.com:443 DEBUG: https://cloud.iexapis.com:443 "GET /stable/stock/orcl/quote?token=pk_05c43fc7cb604812a604a863e7a0153c HTTP/1.1" 200 None PORTFOLIO VALUES INSIDE LOOP: {'name': 'Oracle Corp.', 'symbol': 'orcl', 'shares': 2, 'price': 76.005, 'total_value': 152.01} PORTFOLIO VALUES OUTSIDE LOOP: {'name': 'Oracle Corp.', 'symbol': 'orcl', 'shares': 2, 'price': 76.005, 'total_value': 152.01} INFO: 192.168.40.90 - - [12/Apr/2021 17:42:48] "GET / HTTP/1.0" 200 - INFO: 192.168.40.90 - - [12/Apr/2021 17:42:48] "GET /static/styles.css HTTP/1.0" 200 -

Solution

It's not really a looping problem per se. There is exactly one key price in portfolio, so naturally it gets the value of the last thing it is set to. Ditto total_price.

One option to consider is eliminating portfolio all together. After rows is populated from the database, iterate over it and add the price and total_price keys to each row. Then the data is "self-contained", one row has everything needed for one <tr>.