Inserting transaction type into SQL database (CS50 Finance)

Question

I am currently encountering an issue where my code won't insert the transaction type into my history database. If I remove transaction from the SQL querry the code works fine. Any ideas what the issue might be here?

RuntimeError: near "transaction": syntax error [SQL: "INSERT INTO history (id, stock, shares, price, value, date, transaction) VALUES (6, 'AAPL', 2, 203.65, 407.3, '2019-08-05', 'Buy')"] (Background on this error at: http://sqlalche.me/e/e3q8)

my query looks as follows:

    currentDT = datetime.datetime.now()
    date_formatted = currentDT.strftime("%Y-%d-%m")


    db.execute("INSERT INTO history (id, stock, shares, price, value, date, transaction) VALUES (:id, :stock, :shares, :price, :value, :date, :transaction)", \
        id = session["user_id"], \
        stock = stock['symbol'], \
        shares = quantity, \
        price = stock['price'], \
        value = value, \
        date = date_formatted, \
        transaction = "Buy")

Answer 1

"transaction" is a reserved word in SQL, meant for SQL-style transactions (executing a number of statements and having them applied all at once or rolled back).

You could rename your field, put it in quotation marks 'transaction' in the SQL statement, or remove it and use positive share numbers for buying, and negative for selling (this can be useful if you want to sum them up in SQL later).

BTW, the id from other table users is usually named user_id. It's just a naming convention, but useful for reading queries.