0

I have an issue with an if statement that does not match condition in my application.py. However, when I test the same statement in a test script with real values instead of variables, it works as expected.

    # Check if the symbol is already part of portfolio. Add number of purchased shares to portfolio if already exists
    rows = db.execute('SELECT * FROM "portfolio" WHERE "user_id" = :user_id AND "symbol" = ":symbol"', user_id = user_id, symbol = symbol)

    if len(rows) == 1:
        shareBalance = db.execute('SELECT "shares" FROM "portfolio" WHERE "user_id" = :user_id AND "symbol" = ":symbol"', user_id = user_id, symbol = symbol)[0]["shares"]
        shareBalance += shares
        db.execute('UPDATE "portfolio" SET "shares" = :shareBalance WHERE "user_id" = :user_id AND "symbol" = ":symbol"', shareBalance = shareBalance, user_id = user_id, symbol = symbol)

   # Add a new entry in portfolio database if the symbol doesn't already exist
    else:
        db.execute('INSERT INTO "portfolio" ("user_id", "symbol", "name", "shares") VALUES (:user_id, :symbol, :name, :shares)',
                    user_id = user_id, symbol = symbol, name = name, shares = shares)

Te debugger shows me that the if statement is skipped:

DEBUG:cs50:SELECT * FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "'FTNT'"
DEBUG:cs50:INSERT INTO "portfolio" ("user_id", "symbol", "name", "shares") VALUES (10, 'FTNT', 'Fortinet Inc.', 10)

Although, when I try the same thing in a test script, here's what I get:

shareNum = 10

rows = db.execute('SELECT * FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "FTNT"')
if len(rows) == 1:
    shareBalance = db.execute('SELECT "shares" FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "FTNT"')[0]["shares"]
    shareBalance += shareNum
    db.execute('UPDATE "portfolio" SET "shares" = :shareBalance WHERE "user_id" = 10 AND "symbol" = "FTNT"', shareBalance = shareBalance)

# Add a new entry in portfolio database if the symbol doesn't already exist
else:
    db.execute('INSERT INTO "portfolio" ("user_id", "symbol", "name", "shares") VALUES ("10", "FTNT", :name, :shares)',
               name = lookup(request.form.get("symbol"))["name"], shares = shareNum)

With the following debug output:

DEBUG:cs50:SELECT * FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "FTNT"
DEBUG:cs50:SELECT "shares" FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "FTNT"
DEBUG:cs50:UPDATE "portfolio" SET "shares" = 30 WHERE "user_id" = 10 AND "symbol" = "FTNT"

I don't understand how I am getting different results. Any help will be much appreciated.

0

Very (very!) subtle, but meaningful difference in the DEBUG outputs of the selects:

1) DEBUG:cs50:SELECT * FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "'FTNT'"
2) DEBUG:cs50:SELECT * FROM "portfolio" WHERE "user_id" = 10 AND "symbol" = "FTNT"

The first is looking for symbol to match 'FINT'; it never will. The correction would be changing this "symbol" = ":symbol"' to this "symbol" = :symbol' (ie remove the double-quotes around the bind parameter)

Apparently SQLAlchemy (or some other black-box) automagically manages the quoting based on column type, so you don't have to.

1
  • Ugh.. I was looking right at it and was missing it. Thank you for pointing it out.
    – IDT
    Dec 10 '18 at 2:50

You must log in to answer this question.

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