Below is my code for buy. After I run flask and tried to make a purchase, the history table updated and cash deducted from users table. However, portfolio table is still empty. I am thinking the error is probably in my portfolio table but I dont know where.
The schema of portfolio table: CREATE TABLE 'portfolio' ('id' INTEGER NOT NULL , 'symbol' TEXT NOT NULL , 'name' TEXT NOT NULL , 'shares' INTEGER NOT NULL , 'price' NUMERIC NOT NULL , 'total' NUMERIC NOT NULL );
Code for buy:
@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
"""Buy shares of stock"""
if request.method == "POST":
if not request.form.get("symbol"):
return apology("must provide symbol")
elif not request.form.get("shares") or not request.form.get("shares").isdigit() or int(request.form.get("shares")) <= 0:
return apology("must provide positive number of shares you want to purchase")
else:
quote = lookup(request.form.get("symbol").upper())
if not quote:
return apology("invalid stock name")
cash = db.execute("SELECT cash FROM users WHERE id= :id", id=session["user_id"])
if not cash or float(cash[0]["cash"]) < quote["price"] * int(request.form.get("shares")):
return apology("insufficient cash")
db.execute("UPDATE users SET cash = cash - :price WHERE id = :id", price = quote["price"] * int(request.form.get("shares")), id = session["user_id"])
rows = db.execute("SELECT * FROM portfolio WHERE id = :id AND symbol = :symbol",symbol = request.form.get("symbol").upper(), id = session["user_id"])
if len(rows) == 0:
db.execute("INSERT INTO portfolio (id, symbol, shares) VALUES (:id, :symbol, :shares)", id= session["user_id"], symbol = request.form.get("symbol").upper(), shares= int(request.form.get("shares")))
else:
db.execute("UPDATE portfolio SET shares = shares + :shares WHERE symbol = :symbol", shares= int(request.form.get("shares")), symbol = request.form.get("symbol").upper())
db.execute("INSERT INTO history (id, symbol, shares, price) VALUES (:id, :symbol, :shares, :price)", id = session["user_id"], symbol = request.form.get("symbol").upper(), shares = int(request.form.get("shares")), price = usd(quote["price"]))
return redirect("/index")
else:
return render_template("buy.html")