Sqllex v0.2.0.3 JOIN outdated

Code

        data = self.db.select(
            self.db["users"],
            ['user_id', 'first_name', self.db["user_status"]["name"]],
            JOIN=(
                (self.db["user_status"], ON, self.db["user_status"]["id"] == self.db["users"]["status"]),
            )
        )

Exception

File "sqllex\core\tools\parsers\parsers.py", line 242, in add_join_to_script
    f"{join_method} {' '.join(j_arg for j_arg in _join)} "
TypeError: sequence item 0: expected str instance, SQLite3xTable found

but this code works fine

        data = self.db.select(
            self.db["users"],
            ['user_id', 'first_name', 'user_status.name'],
            JOIN=(
                ("user_status", ON, "user_status.id = users.status"),
            )
        )

ABTable.create_table returns empty list instead of None-return

db.create_table('user', {
    'id': sqllex.constants.INTEGER,
    'name': sqllex.constants.sqlite.TEXT
})
# []

============== TEMPLATE ==============

✅ DESCRIBE THE BUG

It should be under FROM in db.select() not TABLE

Example log:

 vaccine_db.select(SELECT= ALL, FROM = "Total", LIMIT= 10)

TypeError: select() missing 1 required positional argument: 'TABLE'
...
sqlite3.OperationalError: no such table: PRAGMA_TABLE_INFO

✅ CODE

Show the code (or just a few lines) raise this bug. Show what methods you call and what the input data is. MAKE SURE IT DOESN'T CONTAINS ANY PRIVATE DATA!

For example:

vaccine_db.select(SELECT= ALL, FROM = "Total", LIMIT= 10)

SCREENSHOTS

⚠️ CONFIGURATIONS

• OS: [Linux]
• Python version: [3.8]
• Sqllex version: [0.1.9.3]

OTHER

✅ 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})

WHERE currently no support "<" or ">" or "NOT" or "OR" or "AND" and other things except "="

I hope you 💖it. @v1a0

SQLLEX-Simple-and-Faster

Workflow "Test Sqlite3x"

YML: .github/workflows/test_sqlite3x.yml

Running tests/new_test_all.py and falling with sqlite3.OperationalError: near "DROP": syntax error.

Falling function has_add_remove_column_test(), temporary #commented

Here is the log:

2021-06-19 04:00:01.512 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
CREATE  TABLE  't8'  (
id INTEGER ,
test TEXT 
) ;


2021-06-19 04:00:01.513 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
SELECT name FROM sqlite_master WHERE type='table'


2021-06-19 04:00:01.513 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
ALTER TABLE 't8' ADD 'col1' TEXT DEFAULT 123


2021-06-19 04:00:01.513 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
ALTER TABLE 't8' ADD 'col2' TEXT


2021-06-19 04:00:01.514 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
SELECT name FROM PRAGMA_TABLE_INFO('t8')


2021-06-19 04:00:01.514 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
SELECT name FROM PRAGMA_TABLE_INFO('t8')


2021-06-19 04:00:01.514 | DEBUG    | sqllex.core.entities.sqlite3x.midleware:execute_wrapper:53 - 
ALTER TABLE 't8' DROP COLUMN 'col2'


Traceback (most recent call last):
  File "/home/runner/work/sqllex/sqllex/new_test_all.py", line 423, in <module>
    has_add_remove_column_test()
  File "/home/runner/work/sqllex/sqllex/new_test_all.py", line 395, in has_add_remove_column_test
    t8.remove_column('col2')
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/sqlite3x.py", line 215, in remove_column
    self.db.remove_column(self.name, column)
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/sqlite3x.py", line 1417, in remove_column
    self.execute(
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/sqlite3x.py", line 1128, in execute
    return self._execute_stmt(script=script, values=values, request=request)
  File "/home/runner/work/sqllex/sqllex/sqllex/core/tools/convertors/listers.py", line 61, in t2l_wrapper
    ret = func(*args, **kwargs)
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/midleware.py", line 67, in execute_wrapper
    return executor(stmt.connection, stmt)
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/midleware.py", line 38, in executor
    raise error
  File "/home/runner/work/sqllex/sqllex/sqllex/core/entities/sqlite3x/midleware.py", line 33, in executor
    cur.execute(stmt.request.script)
sqlite3.OperationalError: near "DROP": syntax error
Error: Process completed with exit code 1.

I think it shouldn't be shown if default arguments is set

SIZE CROP! Expecting 2 arguments but 1 were given!
Expecting: ['id', 'money']
Given: (340935787,)

Add to docs few linked like this (shadow) examples with inline href right in code. I think it looks cool 🔥

Example

db: SQLite3x = ...

db.select(
            db["users"],
            [self.db["users"]['id'], 'user_id', 'first_name', 'user_status.name'],
            WHERE=self.db["users"]["user_id"] == user_id</a>,
            JOIN=(
                ("user_status", ON, "user_status.id = users.status"),
            ),
        )

db: SQLite3x = ...

db.select(
            db["users"],
            [self.db["users"]['id'], 'user_id', 'first_name', 'user_status.name'],
            WHERE=self.db["users"]["user_id"] == user_id,
            JOIN=(
                ("user_status", ON, "user_status.id = users.status"),
            ),
        )

Currently when you execute any SQL-scripts it's printing in terminal.

I guess here need some option to control log level.

Hey everyone, v1a0 here!

Over the past month this project has caught the attention of python community.

Sqllex package has been downloaded from pypi.org over 550 times, github repo got 45 new stars.

Sqllex community got its first pioneers:

• @kingabzpro - have written an article about sqllex and reported 3 first issues
• @Phizilion - reported 3 really important issues, fix of which marked the beginning of 0.1.9 version
• @dannkunt - reported mistaken spam in logs
• And many reddit users, who reviewed code and gave me many ideas how to make sqllex better than it was

I'm really thankful for all of you guys and I want to show one's gratitude. What if we'll add in readme list of your projects where were used sqllex, to show some real examples for new developers. I have kind of in vision like this:

I'm really interested where and how you use sqllex. Also it might be like a little promotion for your project.

So if you into this, leave some reaction below this post, it might be 👍 or something like that.
Also you can leave a link to projects you prefer to share with our community in comments blow.

Rename/migrate SQLite database and don't destroy all existing connected objects.

db: SQLite3x = ...

db.rename("new_name")
db._migrate("new_path")

I guess it might be useful

I would like to be able to set sqlite3.connection parameters. For example, check_some_thread.

I will be grateful for help :)

maybe something like that?

db = SQLite3x("db.db", connection_parameters={"check_some_thread": False})

Add new parameter into init of Database classes, something like auto_conn or just connect or init_conn, which means "connect immediately" with object initialization. After adding "with-as" statement support it might be reasonable to creating object without creation connection. This parameter have to be True by default.

from sqllex import SQLite3x

# might look like this
db = SQLite3x(path='/path/database.db', init_conn=False)

# or this
db = SQLite3x(path='/path/database.db', auto_conn=False)

# or maybe even
db = SQLite3x(path='/path/database.db', without_conn=True)

In code:

class SQLite3x(ABDatabase):

    def __init__(self,
                 path: PathType = "sql3x.db",
                 init_conn: bool = True,
                 template: DBTemplateType = None):


        ... # another routine

        self.__connection = None  # init connection
        
        if init_conn:       # <== HERE
            self.connect()  # creating connection with db
        
        ... # yet another routine

Welcome you to join this discussion! How should we name this parameter?

Sqllex v0.2.0.3

sqllex/core/entities/abc/sql_database.py line 1439

1426|    def select(
1427|            self,
1428|            TABLE: Union[AnyStr, AbstractTable] = None,
... |             ...
1439|    ) -> List[Tuple]: # instead on Tuple[Tuple] !!!!!!!!!!!!!!!!

SQLite3x.path returns relative path, but I guess return absolute path it's more reasonable for this property.

Now

db.path # 'my_db.db'

Will be

db.path # '/usr/local/.../my_project/my_db.db'

Add methods for SQLite3xTable

table.add_column(
{'column_name': INTAGER}
)

table.remove_column(
"column_name"
)

table.remove_column(
column: SQLite3xColumn
)

table.has_column(
"column_name"
)

table.has_column(
column: SQLite3xColumn
)

In v1 for multiple select rows by id from list of ids the command is:

list = [1,2,3,4,5...]
table.select(WHERE=['id', *list], ORDER_BY="published DESC")

In v2 not a feature for this.
Please add multiple select from list with order.

✅ Describe your idea or issue

Let suppose I want to update the entire table or multiple rows of the table, do I just run the loop? I think we can create some sort of function where it updates multiple columns and rows.

Hey everyone, I just created a telegram group to make it easier for us to communicate with each other. Welcome you to join!

or here is the link

r/Deezl-Vegas:
You can register flask extensions in one line of code.
The init_app pattern from the sqlite example is common

https://flask.palletsprojects.com/en/2.0.x/extensiondev/

Flask Extension Development

Flask, being a microframework, often requires some repetitive steps to get a third party library working. Many such extensions are already available on PyPI.

If you want to create your own Flask extension for something that does not exist yet, this guide to extension development will help you get your extension running in no time and to feel like users would expect your extension to behave.

SQLLEX project currently use wiki as documentation, so it's need major update, restructure and adding more actual examples.

If you have an ideas how to make it better, this issue for you.

i wanted to run my code to server however i got this error. The code works on my computer, so it might be the server

My computer is on Windows 10, the server is using CentOS 7
Python 3.8

File "main.py", line 9, in <module>
    detectives = db["detectives"]
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 931, in __getitem__
    return SQLite3xTable(db=self, name=key)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 653, in __init__
    self.columns = self.get_columns()
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 685, in get_columns
    return self.db.get_columns(table=self.name)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 1582, in get_columns
    columns = self.execute(f"SELECT name FROM PRAGMA_TABLE_INFO('{table}')")
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 1390, in execute
    return self._execute_stmt_(script=script, values=values, request=request)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 566, in t2l_wrapper
    ret = lister(func(*args, **kwargs))
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 405, in execute_wrapper
    ret_ = executor(conn, stmt)
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 382, in executor
    raise error
  File "/usr/local/lib/python3.8/site-packages/sqllex/classes/sqlite3x.py", line 377, in executor
    cur.execute(stmt.request.script)
sqlite3.OperationalError: no such table: PRAGMA_TABLE_INFO

if there is one row in the table, and I want to display all the rows, I do not get a list of all rows, but when there are 2 or more rows in the table, then I get a list of these rows

WHERE is a required argument for update(), although the absence of WHERE should change all rows in the table, that is, the WRERE argument should not be required and its absence should change all rows in the table

P.S. at the moment such an opportunity is not necessary for me, but it may be needed by me or others in the future

Add __enter__ and __exit__ methods for db, table and column classes to make possible nice temporary connection.

from sqllex import SQLite3x

with SQLite3x('/path/database.db') as db:
    # creting connection
    db.insert(...)
    ...
    # commit and disconnection

with SQLite3x('/path/database.db').connection as conn:
    # creting connection
    # doing things (idk)
    ...
    # commit and disconnection

with SQLite3x('/path/database.db')['table'] as table:
    # creting connection
    table.insert(...)
    table.select(...)
    # commit and disconnection

with SQLite3x('/path/database.db')['table']['column'] as column:
    # creting connection
    column.find(...)
    # commit and disconnection (if necessary)

P.S.: For developers, stackoverflow "Implementing use of 'with object as o' in custom class in python"

✅ THE BUG

I wrote the project and ran it on my main computer and everything works, but when I try to run this project on raspberrypi zero w I get an error (see below)

log:

File "/home/pi/.local/lib/python3.7/site-packages/sqllex/classes/__init__.py", line 5, in <module>
    from sqllex.core.entities.sqlite3x import *
  File "/home/pi/.local/lib/python3.7/site-packages/sqllex/core/entities/__init__.py", line 4, in <module>
    from sqllex.core.entities.sqlite3x import *
  File "/home/pi/.local/lib/python3.7/site-packages/sqllex/core/entities/sqlite3x/__init__.py", line 4, in <module>
    from sqllex.core.entities.sqlite3x.sqlite3x import *
  File "/home/pi/.local/lib/python3.7/site-packages/sqllex/core/entities/sqlite3x/sqlite3x.py", line 4, in <module>
    from sqllex.core.entities.abc import \
  File "/home/pi/.local/lib/python3.7/site-packages/sqllex/core/entities/abc/__init__.py", line 4, in <module>
    from sqllex.core.entities.abc.sql_database import AbstractDatabase, AbstractTable, AbstractColumn
...
File "<fstring>", line 1
    (SET=)

✅ CODE

from sqllex import *

⚠️ CONFIGURATIONS

• OS: [Raspbian GNU/Linux 10 (buster)]
• Python version: [3.7.3]
• Sqllex version: [0.2.0.3]

OTHER

Add new class SQLite3xColumn

SQLite3x -> SQLite3xTable -> SQLite3xColumn

In this issue you can leave your request for new constant.

List of existing constants:

SQLite3x

__all__ = [
    'ABORT',
    'ALL',
    'AS',
    'AUTOINCREMENT',

    'BLOB',

    'CHECK',
    'CONST_PRIORITY',
    'CROSS_JOIN',

    'DEFAULT',

    'FAIL',
    'FOREIGN_KEY',

    'IGNORE',
    'INNER_JOIN',
    'INTEGER',

    'LEFT_JOIN',

    'NONE',
    'NOT_NULL',
    'NULL',
    'NUMERIC',

    'ON',

    'PRIMARY_KEY',

    'REAL',
    'REFERENCES',
    'REPLACE',
    'ROLLBACK',

    'TEXT',

    'UNIQUE'
]

Sqllex v0.1.10.3b

Converting (tuples) to [lists] costs for select-like methods 55.8% of all time.

This is an results for:

def select_where_1(db: SQLite3x):
    db.select(
        'main', 'id',
        WHERE={
            'name': 'Alex'
        },
        LIMIT=1000
    )

In my opinion it's too much time for such feature. Maybe we should use numpy.arrays inside sqllex's processing (instead of lists).

Found this example and i think it might be the decision.

https://stackoverflow.com/questions/63409324/why-numpy-array-is-faster-than-list-in-python

P.S.:
<built-in method builtins.isinstance> takes 1/10 of all time

P.P.S:

sqllex v0.1.10.3a

crete_table(db)       # 0.003847
insert_fats(db)       # 0.06685 sec (1000 rec)
insert_slow(db)       # 0.2699 sec  (1000 rec)
insert_many_fast(db)  # 0.005199    (1000 rec)
insert_many_slow(db)  # 0.005518    (1000 rec)
select_all(db)        # 0.005709    (1000 rec)
select_where_1(db)    # 0.002922    (1000 rec)
select_where_2(db)    # 0.003836    (1000 rec) << why?

tree/dev/tests/time-tests

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

urs_id: SQLite3xColumn

(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.

Not supporting different sorting order for different columns.

Example log:

expected = self.db.execute('SELECT id, firstName FROM "employee" ORDER BY firstName ASC, surname DESC')

        self.assertEqual(
            expected,
            self.db['employee'].select(
                SELECT=['id', 'firstName'],
                ORDER_BY=['firstName', 'ASC', 'surname', 'DESC']
            )
        )

# sqlite3.OperationalError: no such column: ASC

Generated script

SELECT id, firstName FROM "employee"  ORDER BY firstName, ASC, surname, DESC
                                                        ^             ^
                                                       [!]           [!]

How script have to look like

SELECT id, firstName FROM "employee"  ORDER BY firstName ASC, surname DESC

Version:

sqllex v0.2.0.5

============== TEMPLATE ==============

✅ DESCRIBE THE BUG

When you try to add a duplicate PRIMARY ID, it doesn't give an error. It just adds some kind of values to populate the table and make the rest of the table NONE. I am actually working on another article so, I am sorry to point out bugs so rapidly.

Example log:

[['Pakistan',
  8263763,
  38.9,
  '1.1%',
  'CanSino, Oxford/AstraZeneca, Sinopharm/Beijing, Sinovac, Sputnik V',
  '13/06/2021 08:59:22'],
 [b'\xb0\x8d\x9cy\x84\x7f\x00\x00\x10\xf7\xc7y\x84\x7f\x00\x00\xd0\xdc\x90y\x84\x7f\x00\x000\xa0\x9cy\x84\x7f\x00\x000\xc1\x9by\x84\x7f\x00\x00p\x01\x94y\x84\x7f\x00\x00',
  None,
  None,
  None,
  None,
  None]]
...
sqlite3.OperationalError: no such table: PRAGMA_TABLE_INFO

✅ CODE

Show the code (or just a few lines) raise this bug. Show what methods you call and what the input data is. MAKE SURE IT DOESN'T CONTAINS ANY PRIVATE DATA!

For example:

vaccine_db.insert('Pakistan', data[data["Country"] == "Pakistan"].values);

SCREENSHOTS

⚠️ CONFIGURATIONS

• OS: [Linux]
• Python version: [3.8]
• Sqllex version: [ 0.1.9.4]

OTHER

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!

Get size of SQLite3x database

db: SQLite3x = ...

print(db.get_size())
print(db._size)
print(db._file_size)

In case when WHERE condition generating by SQLite3xSearchCondition and have str value in other argument it's just inserting inline in SQL-script.
But it have to be added to stmt.request.values and then executed safely:

cur.execute(stmt.request.script, stmt.request.values)

Unsafe case

users.update(
        {
            id_col: id_col + 2
        },
        WHERE=(name_col == 'Alex')   # might be SQL-injection instead "Alex"

class SQLite3xSearchCondition(str):
    ...
    def _str_gen(self, value, operator: str):
        if type(value) == str:
            return SQLite3xSearchCondition(
                f"({self}{operator}'{value}')"  # unsafe!
            )
        else:
            return SQLite3xSearchCondition(
                f"({self}{operator}{value})"
            )

Description

Found bug in registration where registration date is not writing in db.

Code

db.insert('users', uid, from_user=from_user,
          registration_date=datetime.now().strftime("%Y-%m-%d %H:%M"))

Research

Shout to solve it myself, but it's 3:00 pm, maybe tomorrow or not me
So I discovered that this bug is in sqlite3x.py on 1119 - 1127

if args:
    _columns = self.get_columns(table=TABLE)
    _columns, args = crop(_columns, args)
    insert_values = args

elif kwargs:
    _columns = tuple(kwargs.keys())
    insert_values = list(kwargs.values())

else:
    raise ArgumentError(args_kwargs="Unset", error="No data to insert")

btw 1120 might be _columns, args = crop(self.get_columns(table=TABLE), args)

It handles only args or kwargs but not both

✅ I have uploaded the new article using Sqllex

✅ Article

✅ DESCRIBE THE BUG

The code about inserts or any other function except select should not return anything not even empty array

✅ CODE

The code about inserts or any other function except select should not return anything not even empty array

For example:

vaccine_db.insertmany('Total',data.drop(columns= "Date_Time",axis =1).values)

SCREENSHOTS

⚠️ CONFIGURATIONS

• OS: Linux
• Python version: 3.8
• Sqllex version: v0.1.9.4

value = 1

db.update(
        table='wallets',
        set_={
            'balance': f'pre_balance + {value}'
        },
        where={
            'id': user_id,
        },
        with_={
            'pre_balance': db.select(select='balance',
                                     table='wallets',
                                     where={
                                         'id': user_id,
                                     },
                                     execute=False
                                     )
        }
    )

SQL:

WITH pre_balance AS 
(SELECT balance FROM coins WHERE (user_id=?) ) 
UPDATE coins SET balance = (?) 
WHERE (user_id=?)  (123, 123, 'pre_balance + 1', 123) 

ERROR:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 4 supplied.

Code:

def somefunc():
    template: DBTemplateType = {
        'users': {
            'id': [PRIMARY_KEY, INTEGER, NOT_NULL],
        }
    }
    db = SQLite3x(template=template)

error:
sqlite3.OperationalError: near "INTEGER": syntax error

CREATE TABLE IF NOT EXISTS 'users'  (
id PRIMARY KEY INTEGER NOT NULL
) ;  

because it's awaiting

CREATE TABLE IF NOT EXISTS 'users'  (
id INTEGER PRIMARY KEY NOT NULL
) ;  

Just add GROUP BY

In python UPPERCASE usually used for to highlight constants, but here I guess it might be reasonable to change args like "or_", "set_" to "OR" and "SET"

It's much easier to read and understand for person who ever used SQL-like databases

Here some examples:

BEFORE:

def insert_balance(user_id: int, balance: int):
    db.insert(
        table='coins',
        or_=REPLACE,
        id=user_id, balance=balance
    )

AFTER:

def insert_balance(user_id: int, balance: int):
    db.insert(
        OR=REPLACE,
        TABLE='coins',
        id=user_id, balance=balance
    )

BEFORE:

def update_balance(user_id: int, balance: int):
    db.update(
        table='coins',
        set_={
            'balance': balance
        },
        where={
            'id': user_id,
        }
    )

AFTER:

def update_balance(user_id: int, balance: int):
    db.update(
        TABLE='coins',
        SET={
            'balance': balance
        },
        WHERE={
            'id': user_id,
        }
    )

✅ Describe your idea or issue

SQLite allows for a LIKE operator that performs wildcard searches, but as far as I can tell, it's not accessible in SQLLEX, at least not when criteria are given as a dict, like this:

jerrys = db['users'].select(ALL, WHERE={'name': ['LIKE', 'Jerry%']})

or this:

jerrys = db['users'].select(ALL, WHERE={'name': ['%', 'Jerry%']})

Would you be willing to add support for this?

✅ Additional context

It's possible to enable support, at least in this specific use-case, by adding "LIKE" to the recognized operators listed here, and patching it to add spaces when necessary, like so:

                    if len(values) > 1 and values[0] in [
                        "<", "<<", "<=",
                        ">=", ">>", ">",
                        "=", "==", "!=",
                        "<>", "LIKE",
                    ]:
                        operator = values.pop(0)
                        if operator == 'LIKE':
                           operator = ' LIKE '

But that's a pretty hacky solution, and I don't know how it interacts with the other ways to write WHERE statements, so I figured I would file an issue instead of a PR.

Some doc's examples (like this) have implicit imports:

from sqllex import *

db = SQLite3x(path=...)

db.create_table(
    'users',
    {
        'id': [INTEGER, PRIMARY_KEY, UNIQUE],
        'name': [TEXT, NOT_NULL, DEFAULT, 'Unknown']
    }
)

It's kind off unclear what is actually importing from package, it can be some classes or constants or functions. And if you use this way in your project it may be confused whch classes and constants is "yours" and whats imported from sqllex. And also namespace collisions.

That's why I decided to change it this way:

import sqllex as sx

db = sx.SQLite3x(path=...)

db.create_table(
    'users',
    {
        'id': [sx.INTEGER, sx.PRIMARY_KEY, sx.UNIQUE],
        'name': [sx.TEXT, sx.NOT_NULL, sx.DEFAULT, 'Unknown']
    }
)

Now if you see sx prefix than it's 100% clear this element is as part of sqllex package.
Yes, code becomes a little bit longer but you see where did the variables come from.

Explicit is better than implicit — PEP20

CREATE  TABLE IF NOT EXISTS 'xmts'  (
id INTEGER AUTOINCREMENT PRIMARY KEY,
xmt TEXT UNIQUE NOT NULL
) ;

have to be

CREATE  TABLE IF NOT EXISTS 'xmts'  (
id INTEGER PRIMARY KEY AUTOINCREMENT,
xmt TEXT UNIQUE NOT NULL
) ;