Why does a database query in python not return an integer value?

Why does this work:

cash = db.execute("SELECT cash FROM users WHERE id = :user_id", user_id = session["user_id"])

# Ensure user can afford the stock
if cash[0]["cash"] < share * quote["price"]:
    return apology("Not enough money")

and this doesn’t:

cash = db.execute("SELECT cash FROM users WHERE id = :user_id", user_id = session["user_id"])

# Ensure user can afford the stock
if cash < share * quote["price"]:
    return apology("Not enough money")

I would like to understand how DB queries work in python. What is that “cash” variable stores? Isn’t it the cash value for a user? (int)

Answer

The db.execute returns a matrix/table structure. In this particular example, that matrix is called cash, which has only one column again named cash, and has only one value in it.

So let’s modify your query slightly to understand it better. If your query was:

result = db.execute("SELECT * FROM users WHERE id = :user_id", user_id = session["user_id"])

That result would hypothetically look like:

all columns

And when you query this:

result[0]["cash"]

It would return 100.

If you select only cash instead of *, you’ll get only one column of this table (which is what you are doing):

cash column only

And again, result[0]["cash"] will give you 100.

If I were you I wouldn’t select *. select cash is good because it fetches only what it needs. If you want to store that cash value to use it later, call the database the same way you do now, and just create a variable cashValue = result[0]["cash"] and use that. Also, don’t name different objects the same name, it will confuse you. If there is a column in the database table named cash, call your output something else, like result. And then if you want to get the value in it, call it again something else, like cashValue.

Hope this clarifies things.