Pset7, Index, why does it not show other stocks i own?

Question

I'm trying to get all stocks to just show up on my index screen, but can't seem to get my head around the jinja template + Python + SQL commands.

Can someone tell me why is it that only my last bought share shows up and my other stocks don't show up? Is it because of my flawed "for" loop execution?

Here's my Python code:

@app.route("/")
@login_required
def index():

    shares = db.execute("SELECT symbol, price, name, SUM(shares) AS quanties FROM shares WHERE id=:id", id=session["user_id"])

    for share in shares:
        symbol= str(share["symbol"])
        price= (share["price"])
        sname= str(share["name"])
        quantity= (share["quanties"])     #share["shares"] need to corelate/be reflected in HTML?

    rows=db.execute("SELECT * FROM users where id = :id", id = session["user_id"])
    return render_template("index.html", shares=shares, sname=sname, quantity=quantity, price=price, name=rows[0]["username"])

Index.HTML:

{% extends "layout.html" %}

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

{% block main %}

    <h2>RESULTS:</h2>

    <ul>
        <li><h2> {{ name }}</h2></li>
    </ul>

<table class="table table-striped">

        <thead>
            <th>Share Symbol</th>
            <th>Shares Name</th>
            <th>Number of Shares</th>
            <th>Current price</th>
            <th>Total value</th>
        </thead>

         {% for share in shares %}
            <tr>
                <td>{{ share.symbol }}</td>
                <td>{{ share.name }}</td>
                <td>{{ share.quanties }}</td>     <!--this nees to corelate to quantity=(share["shares"]), not share["quantity"])-->
                <td>{{ share.price }}</td>
            </tr>
        {% endfor %}

    </table>
{% endblock %}

result: It shows all my shares "SUM"ed in the SQL command. But does not display the rest of the symbol and names of other stocks i own.. :(

Answer 1

This sql "SELECT symbol, price, name, SUM(shares) AS quanties FROM shares WHERE id=:id" always returns one row. Try it in sqlite3/phpliteadmin. You need a GROUP BY clause . Since you (presumably) want one row per symbol, you would add GROUP BY symbol after the where clause (try that in sqlite3/phpliteadmin to see the difference).