FEATURE | Increase/decrease value in update

[damnkrat]

✅ Idea or issue

I'd want to increase the amount of money, but there is no function for this in sqllex. Temporary solution:

db.execute("UPDATE users SET money = money + ? WHERE id = ?",
          (100.0, call.message.chat.id))

I think it should look like this:

db.update('users', SET={"money": ["money", "+", "100.0"]}, WHERE={"id": call.message.chat.id})

[v1a0]

Hey @dannkunt, thanks for opening new issue.

How about we'll design it like this? Is this code clear to read and understand?

users = db['users']

users .update(
    SET={
        users['money']: users['money'] + 100.0
        },
    WHERE=(
        users['id'] == call.message.chat.id
        )
)

This is just a template, it doesn't work now, but I think it looks cool.

[damnkrat]

@v1a0 At first look is cool, but on second thought it isn't logical.

What is users['money']? Is this column or money of one of the users? How can it be equal to chat id?

Currently, it is a list of all user balances, that is logical, which is not the case with the new design.

My design isn't cool, but it's logical, I think we can create another design, but currently I have no Idea.

[v1a0]

How can it be equal to chat id?

Sorry, miss typed, ofc it have to be:

users = db['users']

users .update(
    SET={
        users['money']: users['money'] + 100.0
        },
    WHERE=(
        users['id'] == call.message.chat.id
        )
)

I understand it like:

SET (new value) for records in 'money' column 
WHERE (value) of record in 'id' column == chat.id
(new value) = old value 'money' column + 100.0

But yeah, users['money']: users['money'] + 100.0 looks little bit wired

[v1a0]

Hey @dannkunt here is sqllex v0.1.10.2, update up to latest version. This issue fix now.

pip install sqllex -U

---

And here an example from WARRING.md how now you can use this new features

...

users = db['users']    # Get table from database as object

id_col = users['id']    # Get table from database as object

name_col = users['name']    # Get another column from table as object

users.insert([1, 'Alex'])
users.insert([2, 'Blex'])

...

users.update(
        {
            id_col: id_col + 2
        },
        WHERE=(name_col == 'Alex')
)

users.select([name_col, id_col], WHERE=(id_col == 3))   # [['Alex', 3]]