this error has getting me for a while now. From what I researched, it commonly occurs when inserting a wrong type in a column field or a value in the wrong order. Even though it appears in check50, I was able to neither find, reproduce nor fix this issue so far.
The error:
sending GET request to /signin
sending POST request to /login
sending POST request to /buy
exception raised in application: ValueError: FOREIGN KEY constraint failed
My "best" attempt to reproduce this Exception did not generate anything useful. I tried to iterate through the alphabet and two-letter combinations while buying different amounts of stocks, but with no results. As you can see, I could buy all combinations I tried besides a few that didn't exist, but the foreign key error was not raised at all:
My Database if as follows:
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX username ON users (username);
CREATE TABLE operations (operation_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER NOT NULL, symbol TEXT NOT NULL, name TEXT NOT NULL, shares INTEGER NOT NULL, operation_value REAL NOT NULL, operation_type TEXT NOT NULL, total INTEGER NOT NULL, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users (id));
CREATE TABLE history (operation_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER NOT NULL, symbol TEXT NOT NULL, name TEXT NOT NULL, shares INTEGER NOT NULL, operation_value REAL NOT NULL, operation_type TEXT NOT NULL, total INTEGER NOT NULL, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users (id));
My "Buy" code:
def buy():
"""Buy shares of stock"""
if request.method == "GET":
return render_template("buy.html")
# Get values
user_id = session.get("user_id")
symbol = request.form.get("symbol")
shares = request.form.get("shares")
stocks = lookup(symbol)
# Check if values are according to expected
if not stocks or stocks == "ERROR":
return apology("Try another symbol!", 400)
if not shares.isdigit() or int(shares) <= 0:
return apology("Incorrect value of shares!", 400)
price = truncate(stocks["price"])
total = truncate(price * int(shares))
name = stocks["name"]
print(symbol, name)
cash = db.execute("SELECT cash FROM users WHERE id = ?", user_id)[0]["cash"]
if cash < price:
return apology("No money bro")
# If anything is received, the user has bought the stock, so we have to increment
user_owns_stock = db.execute("SELECT * FROM operations WHERE user_id = ? AND symbol = ?", user_id, symbol)
# Either update the value of stocks or add them in a new line
if not user_owns_stock:
table_insert_all(table_name="operations", user_id=user_id, symbol=symbol, name=name,
shares=shares, price=price, operation="Buy", total=total)
else:
operations_update_shares(type="Buy", shares=shares, value=total, user_id=user_id, stock_symbol=symbol)
table_insert_all(table_name="history", user_id=user_id, symbol=symbol, name=name,
shares=shares, price=price, operation="Buy", total=total)
# Only Reduce the money if everything else works
users_update_cash(type="Buy", value=total, user_id=user_id)
return redirect("/")
Lastly, the Database-specifc functions from another script:
def table_insert_all(table_name, user_id, symbol, name, shares, price, operation, total):
"""Insert all data on either history or operaions table"""
db.execute(
f"""INSERT INTO {table_name} (user_id, symbol, name, shares, operation_value,
operation_type, total) VALUES(?, ?, ?, ?, ?, ?, ?)""",
user_id,
symbol,
name,
shares,
price,
operation,
total
)
def operations_update_shares(type, shares, value, user_id, stock_symbol):
"""Updates the amount of shares a user has"""
if type =="Sell":
db.execute(
"""UPDATE operations SET shares = shares - ?, total = total - ?
WHERE user_id = ? AND symbol = ?""", shares, value, user_id, stock_symbol
)
else:
db.execute(
"""UPDATE operations SET shares = shares + ?, total = total + ?
WHERE user_id = ? AND symbol = ?""", shares, value, user_id, stock_symbol
)
def users_update_cash(type, value, user_id):
"""Update cash in user's account"""
if type == "Sell":
db.execute(
"""UPDATE users SET cash = cash + ?
WHERE id = ?""", truncate(value), user_id
)
if type == "Buy": #Explicit check
db.execute(
"""UPDATE users SET cash = cash - ?
WHERE id = ?""", truncate(value), user_id
)
I do believe I'm missing something very simple here, but I would really like to understand "why" its wrong instead of "what" is wrong. Thanks in advance!
