so everything I have so far is working except the part where I need to update the cash right after I sell a stock. As you can see in the table below what's happening is that everytime I sell a stock the bolded total decreases but the Cash row is supposed to increase. All I have to do is update the cash. I did this and it looks right in the terminal as well but for some reason the users table isn't actually updating. My original cash price was 7798.75. After clicking sell 440.25 was supposed to be added as you can see in the terminal. However, as you see in the terminal the same 7798.75 printed after. The addition in my update query isn't working. Please help!
@app.route("/")
@login_required
def index():
list=db.execute("SELECT stock, SUM(shares), price FROM 'transaction' WHERE username = :user GROUP BY stock", user = session["user_id"])
cash=db.execute("SELECT cash FROM users WHERE id=:user", user = session["user_id"])
cash = round(cash[0]['cash'],2)
print(cash)
sum = 0
for item in list:
symbol = item['stock']
iterable = lookup(symbol)
item['name'] = iterable["name"]
shares= item["SUM(shares)"]
price = item["price"]
total = shares * price
item['total'] = total
sum+=total
print(list)
return render_template("index.html", list = list, cash = cash, sum = sum)
@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
if request.method == "POST":
stock = request.form.get("symbol")
stockinfo = lookup(stock)
shares = request.form.get("shares")
if not stock or not shares:
return apology("Please complete all fields", 403)
elif lookup(stock) is None:
return apology("No such stock symbol exists", 403)
elif int(shares) <= 0:
return apology("Please input a positive integer", 403)
cash = db.execute("SELECT cash FROM users WHERE id = :username", username = session["user_id"])
cash=cash[0]['cash']
# db.execute("CREATE TABLE 'transaction' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'username' INTEGER NOT NULL, 'stock' TEXT NOT NULL,'shares' NUMERIC NOT NULL, 'price' NUMERIC NOT NULL, 'total' NUMERIC NOT NULL)")
if cash > (stockinfo['price'] * int(shares)):
currentcash = cash - (stockinfo['price'] * int(shares))
db.execute("UPDATE users SET cash= :cash WHERE id = :id", cash = currentcash, id = session["user_id"])
db.execute("INSERT INTO 'transaction' (username, stock, shares, price, total) VALUES (:username, :stock, :shares, :price, :total)", username = session["user_id"] , stock = stock, shares = int(shares), price = stockinfo["price"], total = stockinfo['price'] * int(shares))
return redirect("/")
else:
return apology("Cannot afford number of shares", 403)
else:
return render_template("buy.html")
@app.route("/sell", methods=["GET", "POST"])
@login_required
def sell():
if request.method == "POST":
stock = request.form.get("form")
shares = request.form.get("shares")
list = db.execute("SELECT SUM(shares) FROM 'transaction' WHERE username = :user AND stock =:stock GROUP BY stock", user = session["user_id"], stock = stock)
if list == []:
return("Missing symbol", 403)
sharesowned = list[0]['SUM(shares)']
if not stock or not shares:
return apology("Please complete all fields", 403)
elif int(shares) <= 0:
return apology("Please input a positive integer", 403)
elif int(shares) > sharesowned:
return apology("You don't own that many shares", 403)
difference = int(shares) * lookup(stock)["price"]
db.execute("UPDATE 'transaction' SET shares = shares-:num WHERE username = :user AND stock = :stock LIMIT 1 ", num = int(shares), user=session["user_id"], stock = stock )
db.execute("UPDATE users SET cash = cash+:difference WHERE username = :user", difference = difference, user=session["user_id"])
return redirect("/")
else:
list = db.execute("SELECT DISTINCT stock FROM 'transaction' WHERE username = :user", user = session["user_id"])
return render_template("sell.html", list = list)
