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.