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

Question

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)

It returns some number of results (a sequence) of some number of columns (a dictionary) of some values (in this case, a number). An int isn't enough to handle all possible results from a call to execute. — Ruzihm
– Ruzihm, Commented Nov 2, 2018 at 21:55

AybikeA · Accepted Answer · 2018-11-02 23:34:55Z

2

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.

answered Nov 2, 2018 at 23:34

AybikeA

505 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Quarkendon Over a year ago

Thanks a lot, really appreciate your help

AybikeA Over a year ago

You are very welcome, @Quarkendon. I appreciate it if you can click the "checkmark" next to the most helpful answer to mark it as accepted. Thanks and good luck!

p1234 · Accepted Answer · 2018-11-02 21:56:46Z

1

What you receive in cash is a result set. You can think of it as a table like structure.

So cash[0] means the "row" on index 0 and cash[0][cash] means "field" cash in "row" on index 0

answered Nov 2, 2018 at 21:56

p1234

5294 silver badges15 bronze badges

2 Comments

Quarkendon Over a year ago

Thanks Sir, Does that mean that i could have used: 'code' cash = db.execute("SELECT * 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") Does it make sense to SELECT cash instead of SELECT *?

p1234 Over a year ago

@Quarkendon I think this could work too, depending on your DB driver (the Python DB API doesnt specify this explicitly). You could just try it out. -Edit-: But be aware, that SELECT * ... is not good practice. Better to select explicit than implicit.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related