0

I executed the following to return a dict (grouped by stock symbols) of stock symbols, total shares and total value owned.

stocks = db.execute("SELECT buy.stock_code, SUM(buy.value), SUM(buy.shares) FROM buy JOIN users ON users.id = buy.userid GROUP BY buy.stock_code")
print(stocks)

return render_template("index.html", stocks=stocks)

Using the following Jinja code left blank spaces where the 'shares' and 'value' variables were inserted:

{% for stock in stocks %}
<tr>
    <td>{{ stock['stock_code'] }}</td>
    <td>TODO: Current Price</td>
    <td>{{ stock['shares'] }}</td>
    <td>{{ stock['value'] }}</td>
</tr>
{% endfor %}

I used print(stocks) at the command line to see what values were returned for a user who had made 3 purchases of 1, 2 and 4 shares of GOOG.

The values and stocks had added correctly. However, I noticed that where I had used SUM, a closed parenthesis had been added to the returned string e.g. 'value' was being returned as 'value)', 'shares' was being returned as 'shares)'.

[{'stock_code': 'GOOG', 'value)': 8321.74, 'shares)': 7}]

Strangely enough, inputting the parenthesis into the Jinja code makes the page display correctly:

{% for stock in stocks %}
<tr>
    <td>{{ stock['stock_code'] }}</td>
    <td>TODO: Current Price</td>
    <td>{{ stock['shares)'] }}</td>
    <td>{{ stock['value)'] }}</td>
</tr>
{% endfor %}

Removing the SUM function returns the string correctly without the parenthesis (but obviously means the values retrieved are not added and incorrect).

The SUM function seems to be causing this, but am unsure whether it is my syntax or something else I have completely missed.

Any help would be greatly appreciated!

1

You can use AS to rename a field, this is especially useful if you join multiple tables and return fields from several of them, or when using aggregate functions as you do here. Like "SELECT buy.stock_code AS symbol, SUM(buy.value) AS value, SUM(buy.shares) AS shares FROM buy ...

Alternatively, you can drop the AS, but if I remember correctly, for some databases you then need to put the field names you want in single quotes (so they are explicitly interpreted as strings), like "SELECT buy.stock_code 'symbol', SUM(buy.value) 'value', SUM(buy.shares) 'shares' FROM buy ...

I prefer using AS. Maybe personal preference.

2
  • Thanks @Blauelf, using that allowed me to change the values. Do you have any ideas why they would be returned incorrectly though?
    – pelhayek
    Jul 17 '18 at 22:57
  • The closing parenthesis in the string does not look right, but I would use AS in such case anyways, as different databases use different returned field names, no idea why it's that weird in this case.
    – Blauelf
    Jul 18 '18 at 6:14

You must log in to answer this question.

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