My buy function is crashing at the SQL query where I'm trying to insert data from the transaction into a transactions table. The types of each column are transaction_id(integer, primary key), user_id(integer), buy/sell(text), symbol(text), name(text), shares(integer), total(numeric(38,2)). All columns are not NULL

The query I am trying to execute is:

"""INSERT INTO transactions (user_id, buy/sell, symbol, name, shares, total) VALUES (1, Buy, 'NFLX', 'Netflix, Inc.', 1, 526.27)"""

Debug50 shows the query in red and the error underneath is:

ERROR:application:Exception on /buy [POST]

Here is my buy function:

def buy(): """Buy shares of stock"""

if request.method == "POST":

    # Calling lookup function from helpers.py
    # with the symbol provided from the form submission
    result = lookup(request.form.get("symbol"))
    if not result:
        return apology("Please enter a valid symbol")

    # Number of shares the user is trying to buy
    # Handling invalid inputs
    shares = int(request.form.get("shares"))
    if not shares or shares <= 0:
        return apology("Please enter an integer value of shares")

    # Accessing the logged in user's information
    buying_user = db.execute("SELECT * from users WHERE id=:user_id", user_id=session["user_id"])

    available_cash = buying_user[0]["cash"]
    needed_cash = round(result["price"] * shares, 2)

    # Handling if the user does not have enough cash
    # else, updating users cash and inserting transaction data into transactions table
    if needed_cash > available_cash:
        return apology("Sorry, you don't have enough cash")
        db.execute("UPDATE users SET cash=:cash WHERE id=:user_id",
                    cash=round(available_cash - needed_cash, 2), user_id=session["user_id"])
        db.execute("""INSERT INTO transactions
                    (user_id, buy/sell, symbol, name, shares, total) VALUES
                    (:user_id, Buy, :symbol, :name, :shares, :total)""",
                    user_id=session["user_id"], symbol=result["symbol"], name=result["name"],
                    shares=shares, total=needed_cash)
        return redirect("/")

    return render_template("buy.html")

Any ideas please?


Did you test the query directly on the database?

My gut says that the "/" in the buy/sell column name is causing problems. Try changing the column name in the table to something without any special characters. Maybe txntype?

  • Thank you, but I tried changing the name of the column and running in SQLlite and the query still doesn't execute? – Ewan Willsmer Aug 28 '20 at 7:53
  • Another problem is Buy in the VALUES clause. Assume it should be a string literal, enclosed in single quotes. Program is treating it as a variable, which would be undefined. – DinoCoderSaurus Aug 28 '20 at 9:48
  • That did the trick. Thanks! – Ewan Willsmer Aug 28 '20 at 19:04

You must log in to answer this question.

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