0

So I'm trying to update the "share" column in an existing row in the table portfolio instead of creating a new one when buying a stock that the user already has, here is my code:

def buy():
    """Buy shares of stock"""
    if request.method == "POST":
        symbol = request.form.get("symbol")
        n_shares = request.form.get("share")

        if int(n_shares) < 1:
            return apology("Buy at least one", 403)

        quote = lookup(symbol)
        if quote == None:
            return apology("Stock not found", 403)

        chart_value = quote["price"] * int(n_shares)
        stock_name = quote["name"]
        stock_price = quote["price"]
        current_cash = user_cash()

        if current_cash < int(chart_value):
            return apology("Can't afford it", 403)

        update_cash = round(current_cash - chart_value)
        date = datetime.datetime.now()
        date_format = date.strftime("%d-%m-%y %H:%M:%S")

        # Update database

        # Select table portfolio
        rows = ("SELECT * FROM portfolio WHERE user_id = ?", session["user_id"])

        # Check if portfolio is empty
        if len(rows) == 0:
            db.execute("INSERT INTO portfolio (name, symbol, price, share, user_id) VALUES (?, ?, ?, ?, ?)", stock_name, symbol, stock_price, n_shares, session["user_id"])
        
        # If user don't have stocks for that company, insert new row
        elif symbol not in rows:
            db.execute("INSERT INTO portfolio (name, symbol, price, share, user_id) VALUES (?, ?, ?, ?, ?)", stock_name, symbol, stock_price, n_shares, session["user_id"])
        
        # If user have stocks for that company, update it's number of shares
        else:
            stock1 = db.execute("SELECT symbol, stock_id FROM portfolio WHERE name = ? AND user_id = ?", stock_name, session["user_id"])
            stock1_symbol = stock1[0]["symbol"]
            stock1_id = stock1[0]["stock_id"]
            if symbol == stock1_symbol:
                db.execute("UPDATE portfolio SET share = share + ? WHERE user_id = ? AND stock_id = ?", n_shares, session["user_id"], stock1_id)

        db.execute("INSERT INTO history (name, symbol, price, share, date, user_id) VALUES (?, ?, ?, ?, ?, ?)", stock_name, symbol, stock_price, n_shares, date_format, session["user_id"])
        db.execute("UPDATE users SET cash = ? WHERE id = ?", update_cash, session["user_id"])

        return redirect("/")
    else:
        return render_template("buy.html")

I tried a lot of different versions of the code. This version inserts a new row instead of update it. As I could see, the command elif symbol not in rows: can't figure out that that symbol exists in rows and it inserts a new line. I don't know why. In another version, I managed to update an existing row, but it didn't insert a new one if the user didn't have it yet in the portfolio. Can anyone help me?

--EDIT--

I updated my code to this from rows = down:

rows = ("SELECT * FROM portfolio WHERE user_id = ? AND symbol = ?", session["user_id"], symbol)
        if len(rows) != 0:
            stock1 = db.execute("SELECT symbol, stock_id FROM portfolio WHERE name = ? AND user_id = ?", stock_name, session["user_id"])
            stock1_id = stock1[0]["stock_id"]
            db.execute("UPDATE portfolio SET share = share + ? WHERE user_id = ? AND stock_id = ?", n_shares, session["user_id"], stock1_id)

        else:
            db.execute("INSERT INTO portfolio (name, symbol, price, share, user_id) VALUES (?, ?, ?, ?, ?)", stock_name, symbol, stock_price, n_shares, session["user_id"])

But still not solved the problem. Even when the user don't have the stock in portfolio, it's running the command if len(rows) != 0:, so I'm guessing that the query for rows is not returning empty as it should.

--2nd EDIT--

Now my code look like this:

    stockid = db.execute("SELECT stock_id FROM portfolio WHERE symbol = ? AND user_id = ?", symbol, session["user_id"])

        if len(stockid) != 0:
            s_id = stockid[0]["stock_id"]
            db.execute("UPDATE portfolio SET share = share + ? WHERE user_id = ? AND stock_id = ?", n_shares, session["user_id"], s_id)

        else:
            db.execute("INSERT INTO portfolio (name, symbol, price, share, user_id) VALUES (?, ?, ?, ?, ?)", stock_name, symbol, stock_price, n_shares, session["user_id"])

And it works fine.

1

elif symbol not in rows: will always return True becauserows is a list of dictionaries, and symbol is a string. You could iterate through rows and check the value of each row['symbol'] against symbol.

--OR--

Think about how the function would work if this:

rows = ("SELECT * FROM portfolio WHERE user_id = ?", session["user_id"])

is replaced with [line break added for reading clarity]:

rows = ("SELECT * FROM portfolio WHERE user_id = ? and 
    symbol = ?", session["user_id"],symbol)

Now it knows whether user holds this stock or not.

--EDIT--

If rows is the empty list, insert a record, otherwise do the arithmetic and update the existing record. There are only two conditions.

4
  • Now it's updating the existing row, but it fails when should create a new one. It's skipping the command elif symbol not in rows: and tries to run the next command. Sep 2 at 20:35
  • Answer edited.. Sep 3 at 10:40
  • It supposed to have three conditions: - If rows is empty: add a new row - If user holds this stock: update existing row - If rows is not empty but user don't hold the stock: add a new row. I updated the sql query from rows to select where symbol = ?, but I find out that it only executes the command else: everytime, it doesn't matter if the table is empty or the user doesn't hold the stock, it will ignore the first two conditions and try to update the row which is impossible since the table is empty. Sep 3 at 20:56
  • Think about it a different way: if user holds this stock, update existing record. You know the user holds the stock if the result of the query (including symbol in the where clause) has a non-zero length. (ie is not an empty list). Otherwise, insert a record. The only two conditions required to decide wheter to insert or update. It isn't relevant whether the user holds other stocks. If you continue to have problems, please edit the question to include the current version of the code. Sep 3 at 22:46

You must log in to answer this question.

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