1

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

);

5
  • please post the actual error message instead of breaking it apart, just copy and paste it. dont just try things randomly, understand what your code says and should say and figure out why it isnt doing what you think it should be doing. Otherwise you are just adding random noise to your code.
    – UpAndAdam
    Feb 7 at 16:24
  • why do you keep casting shares to an int, when you casted the result of the input to an int to start with? Unnecessary.
    – UpAndAdam
    Feb 7 at 16:26
  • can you include the login function? your code looks fine otherwise
    – UpAndAdam
    Feb 7 at 16:47
  • It doesn't make sense that the trades row is inserted if it gave an exception. Is the correct user_id in the inserted row? Suggest you wrap each execute in a try: loop to narrow things down. And it would be important to know the exact error message. Feb 7 at 17:09
  • I edited the OP with the original error log + /login route
    – Julia
    Feb 7 at 18:10

2 Answers 2

2

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.

2
  • Thank you! I solved it finally!! :)
    – Julia
    Feb 8 at 13:10
  • thats a fantastic catch. I'm not of the general philosophy of having a try contain the least amount of code to catch an error for other reasons that I won't go into here, but for beginners and for debugging this that is absolutely wildly interesting here how it plays out and shows the real trigger to the problem. I also would expect that if you actually generated and printed the stack trace it would also show that something else was going on. Here it sounds like the exception was actually coming from the render_template of index.
    – UpAndAdam
    Feb 9 at 15:28
1

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!

You must log in to answer this question.

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