TableColumn and __lt__, __le__, __eq__, __ne__, __gt__, __ge__ by r/sandmasterflash_

[v1a0]

r/sandmasterflash_

This actually seems pretty cool. And in a lot feels my intuitive. I think a for awkward parts of the syntax that could be improved are how comparison operators are entered as string tokens rather than a constant, like GT, LT, something like that, that also applies to the order by syntax where you enter DESC as a string inside the order by clause, I think a constant would be better there as well.

r/v1a0

Hey r/sandmasterflash_, yeah i totally agree with you. I'll definitely add new constants like DESC, LONG and other SQLite syntax-supportive things. And even it might be reasonable to open an issue on github for add new constants requests.

But by now I have no any ideas how to enter comparison operators not as string tokens. I don't think there exist any possible way to make it work like this:

table.select('column_x', column_y>2, column_z<4)

UPD:

If only code it like this

col_y: TableColumn = column_y
col_z: TableColumn = column_z
table.select('column_x', col_y>2, col_z <4)

and add TableColumn class methods gt, lt and so on... it could work!

[asadafasab]

Hello, I could work on this issue.

What class variables and methods should have SQLite3xColumn? Is there a way to select a range of values? WHERE={'col': [['>', 50],['<', 110]],} doesn't work and WHERE={'col': ['>', 50],'col':['<', 110]} due to dict's nature reduces to {'col': ['<', 110]}

Here is my approach/solution to SQLite3xColumn class. It's incomplete implementation probably there should be some SQL type checking and so on.

class SQLite3xColumn:
    def __init__(self, name,type="???"):
        self.name = name
        self.type = type

    def __lt__(self, value):
        return f"({self.name}<{value})"
        # return (self.name, "<", value)

    def __le__(self, value):
        return f"({self.name}<={value})"
        # return (self.name, "<=", value)

    def __eq__(self, value):
        return f"({self.name}={value})"
        # return (self.name, "=", value)

    def __ne__(self, value):
        return f"({self.name}<>{value})"
        # return (self.name, "<>", value)

    def __gt__(self, value):
        return f"({self.name}>{value})"
        # return (self.name, ">", value)

    def __ge__(self, value):
        return f"({self.name}>={value})"
        # return (self.name, ">=", value)

    @staticmethod
    def and_(left,right):
        # check brackets
        return f"{left}AND{right}"

    @staticmethod
    def or_(left,right):
        # check brackets
        return f"{left}OR{right}"

    @staticmethod
    def not_(sql):
        # check brackets
        return f"NOT{sql}"

col = SQLite3xColumn("column")
table.select("column", col > 20, col < 30)
table.select("column", col.or_(col > 20, col < 30))
table.select("column",WHERE=[col > 20, col < 30])

Returning directly SQL might reduce/replace __where__ decorator and there will be a lot of changes in other parts of code.

[v1a0]

Damn, I'm lovin' it ❤️

[v1a0]

I have only one question, what is it for self.type parameter?

[asadafasab]

I have only one question, what is it for self.type parameter?

I thought that maybe it could be useful for checking type of value in magic methods but IDK.

[v1a0]

Well, I'm almost done. Need test it before release, so I'll release it tomorrow :)

[asadafasab]

It's done but in a hacky way. Tomorrow I'm gonna re-examine this and maybe I'll come up with better ideas.

[v1a0]

@asadafasab here is sqllex v0.1.10.2, update up to latest version.

pip install sqllex -U

---

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

WHERE

Now you can set WHERE condition like this:

users = db['users']

users.select(
    SELECT=users['name'],
    WHERE=( users['id'] == 2 )
)

# OR (the same)

users.select(
    'name',
    users['id'] == 2
)

EXAMPLE

from sqllex import SQLite3x, INTEGER, TEXT, ALL

db = SQLite3x(path='test.db')

db.create_table(
    'users',
    {
        'id': INTEGER,
        'name': TEXT
    }
)

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

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

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

print(users.columns_names)    # ['id', 'name']

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

users.select(ALL, (urs_id == 2) | (urs_id == 1))    # [[1, 'Alex'], [2, 'Blex']]

users.update(
    {'name': "XXXX"},
    WHERE=urs_id == 1
)

users.select(
    [usr_name, urs_id],
    WHERE=(
        (urs_id != 0) & (urs_id != 1)
    )
)   # [['Blex', 2]]

users.update(
        {
            urs_id: urs_id + 2
        },
        WHERE=usr_name == 'XXXX'
)

users.select([usr_name, urs_id], WHERE=(urs_id == 3))   # [['XXXX', 3]]

[v1a0]

I don't know why but it doesn't works with and, or, but works great with |, &.

(urs_id != 0) & (urs_id != 1) # works great
(urs_id = 0) | (urs_id = 1) # works great

(urs_id != 0) and (urs_id != 1) # returns bullshit -  "urs_id <> 0"
(urs_id = 0) or (urs_id = 1) # returns bullshit - "urs_id = 1"

I'll open an issue about whit bug, but it's not really critical and necessary.

[asadafasab]

Yes, and and or are logical operators and they work with boolean values is not a bug(probably).