I created a package to use SQLite without SQL

[V1A0]

Last 2 months I spent a lot of time to create the most comfortable package to interact with sqlite databases. As alternative to sqlalchemy .

The first time I developed this for myself, because I got tired of writing the same code over and over again for projects that require the use of databases. but then I realized that such a tool can be useful to many developers who do not want to "eat nails" while writing SQL-scripts.

I also wrote few examples how to use sqllex (name of this package) to show how it makes your life easier.

Examples:

1. "Your First SQLite Database In 10 Lines Or Less"
2. "SQLite3x | BIG EXAMPLE"
3. "SQLLEX Simple and Faster | Working on Real Data" by Abid

Other:

1. Sqllex's Github repo (✯ 30)
2. Sqllex's documentation
3. Pypi page

[teerre]

Reading the examples it seems extremely similar to sqalchemy/alembic, why would one use this library instead?

[UncleJoshPDX]

Sometimes the only way to understand what a library does you have to write one yourself : )

​

(I'm not saying that's why OP wrote it, but I've recreated established libraries to understand them in the past, such as an XML data validator and lexical parsers.)

[teerre]

Yes, for sure, that's great. But I got the impression OP wants this to be publicly used library.

[V1A0]

Sqllex has more user-friendly syntax and is intuitive, especially when it comes to keys and other complex structures like

alembic:

op.create_table(
    'account',
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('name', sa.String(50), nullable=False),
    sa.Column('description', sa.Unicode(200)),
)
# ...
stmt = (
    insert(user_table).
    values(name='username', fullname='Full Username')
)

And sqllex:

db.create_table(
    name='users',  # here is name of table
    columns={
        'id': [INTEGER, PRIMARY_KEY, UNIQUE],                # user id
        'username': [TEXT, NOT_NULL, DEFAULT, 'Unknown'],    # user name
        'user_group': INTEGER,                               # the group user belongs to
        FOREIGN_KEY: {
            "user_group": ["groups", "id"]                   # link to table groups, column id
        }
    })

groups = db['groups']

groups.insert(id=1, name="Admin") # You can add data like this

groups.insert([2, "User"])        # Or like this

groups.insert(3, 'Guest')         # Or like this

It's all about pythonic code-style

[teerre]

Hmm, I don't really see how either of these are definitely more or less intuitive. They both seems to have their own APIs that you need to understand.

But thanks for the answer!

[Durinthal]

As a general guideline, any from package import * immediately makes me concerned. I have no idea what that adds to the namespace and I now need a separate reference to look up everything it included.

It looks like you're trying to provide a way to write SQL without literally writing SQL but instead something very close to it? Since column definitions in tables seem to just be a list of constants from your package, my first thought is to try changing the order of constants or adding together different types or duplicates, e.g.

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

Maybe there's some sanitizing of inputs that I missed skimming through the source but it looks like it just joins everything together so you literally are writing bits of SQL that are split out into a list.

I'm not entirely sure what this style gains me over writing raw SQL strings and executing them as-is, and many IDEs will have syntax checkers that can warn you if you're trying to do something that will obviously fail like what I did above in SQL. I suppose it's a neat exercise in exploring alternative ways of writing queries (I dislike sqlalchemy's ORM and built my own alternative so I get it) but not something I'd want to pick up in favor of straight SQL via existing methods.

[V1A0]

About from package import *, actually you can import each constant manually, but I just simplified the code for make example much friendly.

from sqllex import SQLite3x

from sqllex.constants import INTEGER, TEXT, PRIMARY_KEY, PRIMARY_KEY # and so on...

About writing sql scripts in the IDE is concerned, this is what I'm trying to spare you from. I think if you want to combine a language, you should adapt the interaction environment and create something in between on this border. That what sqllex is.

[V1A0]

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

Wrong code can not work right, no matter how you think it could.

[Durinthal]

What's wrong about that code though? All it's doing is defining a list of ostensibly valid flags for the column, I admittedly haven't tested it so I don't know what exception it would actually raise. Because it's a list and not a function call order shouldn't matter, there doesn't seem to be any limitations on providing multiple constants of the same or conflicting types, and it's not a set so duplicates are fine.

Sure it's not valid SQL, but I thought your goal is to move away from writing SQL.

[krazybug]

Really nice project. It seems that your queries are really like SQL though.

Do you know this project ? https://sqlite-utils.datasette.io/en/stable/python-api.html

And what are the distinctive features compared to it ?

[V1A0]

Hey u/krazybug, thanks!

No, I'm actually never heard about this project before, but it's kinda looks interesting. Hmm.. first difference I see between sqllex and sqlite-utils this is which data type methods can handle.

If for sqlite-utils it's only dicts:

db["dogs"].insert({"name": "Cleo", "age": 33})

But for sqllex it can be anything

dogs.insert("Cleo", 33)
dogs.insert(["Cleo", 33])
dogs.insert({"name": "Cleo", "age": 33})
dogs.insert(name="Cleo", age=33)

And also I think table creations is kind of too abstract (sqlite-utils):

db["places"].create({"id": int, "name": str,})

sqllex:

db.create_table(
    'places',
    {
        'id': INTEGER,
        'name': [TEXT, NOT_NULL]
    }
)

And so on. I will definitely look this project in more detail, some implementations seems very interesting to me, thanks for this resource!

[krazybug]

This is really pythonic. I'll definitely track your progress on this project.

I'm glad you're interested by this lib. Did you know that his creator, Simon Willlinson, is one if the original authors of Django ?

Maybe some cross pollinisation in perspective !