0

Like others I have become stuck with the above error, although the site seems to display the correct info, any help would be appreciated:

Python Flask Code:

@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    user_id = session["user_id"]

    try:
        transaction_history_db = db.execute("SELECT symbol, SUM(quantity) AS quantity, price, SUM(quantity) * price AS total_value FROM transaction_history WHERE user_id = ? GROUP BY symbol HAVING SUM(quantity) != 0", user_id)
        cash_db = db.execute("SELECT cash FROM users WHERE id = ?", user_id)
        cash = int(cash_db[0]["cash"])
        total_db = db.execute("SELECT SUM(price * quantity) AS total FROM transaction_history WHERE user_id = ?", user_id)

        try:
            total = int(total_db[0]["total"])
        except:
            total = 0

        total_all = cash + total
        return render_template("index.html", database=transaction_history_db, cash=cash, total=total_all)

    except:
        return apology("Can't access database")


@app.route("/sell", methods=["GET", "POST"])
@login_required
def sell():
    """Sell shares of stock"""
    user_id = session["user_id"]

    if request.method == "GET":

        try:
            symbols_user = db.execute("SELECT symbol FROM transaction_history WHERE user_id = ? GROUP By symbol HAVING SUM(quantity) != 0", user_id)
            return render_template("sell.html", symbols=[row["symbol"] for row in symbols_user])
        except:
            return apology("Can't access database")
    if request.method == "POST":
        symbol_drop = request.form.get("symbol")
        symbol_text = request.form.get("symbol1")
        quantity = request.form.get("shares")
        try:
            quantity = int(quantity)
        except ValueError:
            return apology("Must Input a valid number")

        stock = lookup(symbol_drop)

        if stock == None:
            stock = lookup(symbol_text)
            if stock == None:
                apology("You must select or enter a valid stock")
            else:
                symbol = symbol_text.upper()

        else:
            symbol = symbol_drop.upper()

        if quantity <= 0:
            return apology("Quantity must be greater than ZERO")

        try:
            transaction_value = quantity * stock["price"]
        except:
            apology("You do not hold the stock you entered")

        user_cash_db = db.execute("SELECT cash FROM users WHERE id = ?", user_id)
        user_cash = int(user_cash_db[0]["cash"])

        quantity_held = db.execute("SELECT quantity FROM transaction_history WHERE user_id = ? AND symbol = ? GROUP BY symbol", user_id, symbol)

        try:
            quantity_shares = quantity_held[0]["quantity"]
        except:
            quantity_shares = 0

        if quantity > quantity_shares:
            return apology("You do not have enough shares to sell")

        update_cash = user_cash + transaction_value

        db.execute("UPDATE users SET cash = ? WHERE id = ?", update_cash, user_id)

        date = datetime.datetime.now()

        # insert new transaction line in table
        db.execute("INSERT INTO transaction_history (user_id, symbol, quantity, price, date, transaction_type) VALUES (?, ?, ?, ?, ?, ?)",user_id, stock["symbol"], -quantity, stock["price"], date, "Sell")

        flash("Transaction Successful")

        return redirect("/")

HTML:

{% extends "layout.html" %}

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

{% block main %}
    <table class="table">
        <thead>
            <tr>
                <th scope="col">Symbol</th>
                <th scope="col">Shares</th>
                <th scope="col">Price</th>
                <th scope="col">TOTAL</th>
            </tr>
        </thead>
        <tbody>
            {% for row in database%}
                <tr>
                    <td>{{ row["symbol"] }}</td>
                    <td>{{ row["quantity"] }}</td>
                    <td>{{ row["price"] | usd }}</td>
                    <td>{{ row["total_value"] | usd }}</td>
                </tr>
            {% endfor %}
        </tbody>
        <tfoot>
            <tr>
                <td></td>
                <td></td>
                <th>Cash</th>
                <td>{{ cash | usd }}</td>
            </tr>
            <tr>
                <td></td>
                <td></td>
                <th>TOTAL</th>
                <td>{{ total | usd }}</td>
            </tr>
        </tfoot>
    </table>

{% endblock %}

As said, any help on this would be great as it seems to work correctly and I have added the AAAA line to helper so I can add these as a buy/sell option.

3
  • your indentation is off. everywhere
    – UpAndAdam
    Commented Mar 27 at 14:04
  • please put your code through style50 and when importing it, just put three backticks before and after the whole thing and it should be indented properly here..
    – UpAndAdam
    Commented Mar 28 at 21:35
  • im making a slight edit to put your python code together
    – UpAndAdam
    Commented Mar 29 at 18:15

2 Answers 2

1

You have some fundamental problems in your code.

  1. You should have your database queries wrapped in a try catch to handle exceptions in their execution.
  2. In your HTML the section for the row in database right now you are adding all the table data for each row of the database into one row of your HTML table, pretty sure you want to be doing that for loop starting outside of the <tr> and ending after the closing </tr>, also why aren't price and total_value a function of row? every single row you print will have the same value.
  3. hard to evaluate any further when you dont include your source code for the index route
  4. pretty sure your calls to apology are missing an argument.
  5. you probably shouldn't be giving users the option to sell stock they dont own anymore. currently you do. You probably want to make sure that when you cull transaction history for symbols that the quantity owned is non zero. but that's just me. I'm of the school of thought that it is bad UI design to lead a user to a page where they can't enter a value that will work. If they don't own the stock anymore, and they go the sell page and select it, no matter what quantity they enter you are going to deliver them an error message that they dont have enough shares to sell.

Try starting from a clean account (or wipe and reinitialize your database). Buy 1 shares of something, check the index, then sell 1 of it, unless you do this while the market is active you should have all the money you started with, and your position should be back to zero. If that works buy 2, sell 1, check, then sell the other 1. At each stop your total value shouldnt be changing, but the value of the position should be changing. Now do it with two stocks, buy two different stocks, 2 of each. check, then sell 1 of each, check, then sell remaining one of each. check. Are you all good?

UPDATE

based on your update, the problem is in your HTML code the same way you are referring to symbol and quantity from row, you need to do that for price and total value... on the left hand side of the pipe to the usd function.

10
  • Thanks for the feedback.
    – faarn
    Commented Mar 28 at 19:44
  • Thanks for the feedback. In reply to your points: 1. I'll work on that. 2 Good spot, I'd been trying different way to get the $ sign through either the HTML adding | usd or in app.py using usd() hoping it would get rid of the error and ended up mucking up the code and some not containing row[] 3. I'll add this below and would be grateful if you could have a look 4.Not sure what I'm missing here as they work 5. Good point, I've added "HAVING SUM(quantity) != 0" at end of db query which seems to work. It was bugging me that it allowed this so thanks for making me sort it!
    – faarn
    Commented Mar 28 at 19:51
  • if my answer is useful please upvote it. if it solves yuor questions please accept it... will look closer at your reply to see how else i can help later tonight
    – UpAndAdam
    Commented Mar 28 at 21:33
  • you shouldnt be calling usd in your python code for the index function. that for loop isnt accomplishing anything
    – UpAndAdam
    Commented Mar 28 at 21:36
  • the apology thing isnt a big deal, but generally you put the return code but it must default to the right value.. in their lecture code they showed examples. glad i could help you
    – UpAndAdam
    Commented Mar 28 at 21:39
0

Found the line that was wrong:

quantity_held = db.execute("SELECT quantity FROM transaction_history WHERE user_id = ? AND symbol = ? GROUP BY symbol", user_id, symbol)

Should have read:

quantity_held = db.execute("SELECT SUM(quantity) As quantity FROM transaction_history WHERE user_id = ? AND symbol = ? GROUP BY symbol", user_id, symbol)

I was reading the quantity of the last entry not the sum of all entries. 3 days of trouble shooting!!!

You must log in to answer this question.

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