How to change a variable in the database, provided that the required parameter is available from the same database?

Question

Python

Here is the code
conn = sqlite3.connect("users.db", check_same_thread=False)
cursor = conn.cursor()
user = message.from_user.id
sql = "UPDATE users SET balance = balance + ? \
WHERE id = ? AND buster1 = 'No' AND buster2 = 'No' AND buster3 = 'No' "
val = (0.010, user)
cursor.execute(sql, val)
conn.commit()

he was added not 0.010, but for example 0.100 to the balance.

Is it possible to somehow implement this?

Answer 1

If we are talking about the formation of a bonus according to a condition that depends on other fields (as in this case, buster1, etc.), you can write this in the request itself through the case statement (logically similar to if branches in python itself). For example:

sql = "UPDATE users SET balance = balance + \
CASE WHEN buster1 = 'Нет' AND buster2 = 'Нет' AND buster3 = 'Нет' THEN 0.010 \
    WHEN buster1 = 'Да' AND buster2 = 'Нет' AND buster3 = 'Нет' THEN 0.050 \
    WHEN buster1 = 'Да' AND buster2 = 'Да' AND buster3 = 'Да' THEN 0.100 \
END \
WHERE id = ?"

And accordingly, in the request, send only the user id, the bonus itself will be calculated automatically.
Thus, you yourself form the dependence of the bonus on these fields.
If you meant something else, please write more with an example.

Answer 2

What you have specified as 0.010 is not a variable in the database, but a value. Variables in a DB are absolutely another.
Therefore, there is no formal answer to the question “how”.
As for “when the user has permission” - as I understand it, the booster fields are responsible for this in the database - you either need to make a new update line for each option with other parameters of the where logic, which check the values ​​of the booster fields, or do the logic in the program so that it reads the booster values, comprehends and forms the necessary update string for the base. Everything is simple.