Week 9 - Finance - Foreign Key Mismatch

Question

I'm trying to solve this issue for a week and nothing works! in my /buy route, I cannot update the users table with updated cash after a "buy" order is submitted. Here is an example of what I get (in red):ERROR: UPDATE users SET cash = 9813.43 WHERE id = 7

*EDIT Here is the error log when I try, for example, buy 1 share of AAPL:

User ID in session: 7
INFO: SELECT * FROM users WHERE id = 7
User ID before update: 7
Error during trade execution: foreign key mismatch - "" referencing "users"
INFO: INSERT INTO trades (user_id, symbol, shares, price, type) VALUES(7, 'AAPL', 1, 188.86, 'Bought')
ERROR: UPDATE users SET cash = 9811.14 WHERE id = 7
INFO: 127.0.0.1 - - [07/Feb/2024 20:06:16] "POST /buy HTTP/1.1" 403 -
INFO: 127.0.0.1 - - [07/Feb/2024 20:06:16] "GET /static/styles.css HTTP/1.1" 200 -
INFO: 127.0.0.1 - - [07/Feb/2024 20:06:16] "GET /static/I_heart_validator.png HTTP/1.1" 200 -

Now, the error seems to be: Error during trade execution: foreign key mismatch - "" referencing "users".

I don't understand why it says "" referencing "users". Here is my trades table:

CREATE TABLE trades ( 
user_id INTEGER NOT NULL, 
shares NUMERIC NOT NULL, 
symbol TEXT NOT NULL, 
price NUMERIC NOT NULL, 
type TEXT NOT NULL, 
FOREIGN KEY(user_id) REFERENCES users(id) 
);

This is the original "users" table that comes with the distribution code:

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 UNIQUE INDEX username ON users (username);

Each trade I submit is recorded in "trades", but the new cash/updated cash is not updated in the users table and I get a foreign key mismatch error. I want to throw my computer our of the window lol.

Does anyone have an idea what can be the problem here? I have tried so many variations of the table, also so many variations of the code and nothing seems to work. ALWAYS foreign key mismatch!

Here is my /buy route:

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock"""
    if request.method == "GET":
        return render_template("buy.html")
    else:
        shares = int(request.form.get("shares"))
        symbol = request.form.get("symbol")
        if symbol == "":
            return apology("Missing Symbol", 403)
        if shares == "":
            return apology("Missing Shares", 403)
        if int(shares) <= 0:
            return apology("share number can't be negative number or zero", 403)

        quote = lookup(symbol)

        if not quote:
            return apology("INVALID SYMBOL", 403)

        total = int(shares) * quote["price"]
        user_cash = db.execute("SELECT * FROM users WHERE id = ?", session["user_id"])

        if user_cash[0]["cash"] < total:
            return apology("CAN'T AFFORD THIS TRADE", 403)

        else:
            try:
                print("User ID in session:", session.get("user_id"))
                db.execute("INSERT INTO trades (user_id, symbol, shares, price, type) VALUES(?, ?, ?, ?, ?)", session["user_id"], quote['symbol'], int(shares), quote['price'], 'Bought')
                cash = user_cash[0]["cash"]
                print("User ID before update:", session.get("user_id"))

                db.execute("UPDATE users SET cash = ? WHERE id = ?", float(cash - total), session["user_id"])
                flash('Bought!')
                return render_template("index.html")
            except Exception as e:
             # Log the error or print it for debugging
                print(f"Error during trade execution: {e}")
                return apology("Internal Server Error", 403)

EDIT Here is my /login route:

@app.route("/login", methods=["GET", "POST"])
def login():
    """Log user in"""

    session.clear()

    if request.method == "POST":
        if not request.form.get("username"):
            return apology("must provide username", 403)

        elif not request.form.get("password"):
            return apology("must provide password", 403)

        rows = db.execute(
            "SELECT * FROM users WHERE username = ?", request.form.get("username")
        )

        if len(rows) != 1 or not check_password_hash(
            rows[0]["hash"], request.form.get("password")
        ):
            return apology("invalid username and/or password", 403)

        session["user_id"] = rows[0]["id"]

        return redirect("/")

    else:
        return render_template("login.html")

Please help me

);

Answer 1

It's not what you think. The body of a try block should contain the least amount of code to catch an error. This routine should have one try/except block for each of the executes (and only the execute statement). If you make that change things will look radically different. It will shift attention away from the database red-herring and to this line return render_template("index.html"). Review the login function (or your register function) to find the difference.

Answer 2

Apparently I had a table hidden in my DB somehow. When I run .schema, I saw the following:

sqlite> .schema
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 TABLE IF NOT EXISTS "" (
    id INTEGER PRIMARY KEY NOT NULL,
    username TEXT NOT NULL,
    shares NUMERIC NOT NULL,
    symbol TEXT NOT NULL,
    value NUMERIC NOT NULL,
    cash NUMERIC NOT NULL DEFAULT 10000.0,
    FOREIGN KEY(username) REFERENCES users(username),
    FOREIGN KEY(cash) REFERENCES users(cash)
);
CREATE TABLE trades (
user_id INTEGER NOT NULL,
shares NUMERIC NOT NULL,
symbol TEXT NOT NULL,
price NUMERIC NOT NULL,
type TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE UNIQUE INDEX username ON users (username);

This table should NOT be there:

CREATE TABLE IF NOT EXISTS "" (
    id INTEGER PRIMARY KEY NOT NULL,
    username TEXT NOT NULL,
    shares NUMERIC NOT NULL,
    symbol TEXT NOT NULL,
    value NUMERIC NOT NULL,
    cash NUMERIC NOT NULL DEFAULT 10000.0,
    FOREIGN KEY(username) REFERENCES users(username),
    FOREIGN KEY(cash) REFERENCES users(cash)
);

I removed the "" table (I have no idea how it got there, it did not appear in my php damin; probably something I accidently created. Anyhow, this is my .schema now:

sqlite> .schema
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 TABLE trades (
user_id INTEGER NOT NULL,
shares NUMERIC NOT NULL,
symbol TEXT NOT NULL,
price NUMERIC NOT NULL,
type TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id)
);
CREATE UNIQUE INDEX username ON users (username);

Everything works as intended now.

Thank you all for your help!