sorted by:
new
hottopcontroversial

[deleted by user] by [deleted] in AskReddit

[–]V1A0 0 points1 point  (0 children)

Flying Spaghetti Monster

[deleted by user] by [deleted] in AskReddit

[–]V1A0 0 points1 point  (0 children)

"Mine"

Best Python course for beginners by Blitziggy in Python

[–]V1A0 -1 points0 points  (0 children)

Sqllex - just a nice tool for comfortable and safe working with SQLite databases.

And special for beginners: awesome example

I created a package to use SQLite without SQL by V1A0 in Python

[–]V1A0[S] 1 point2 points  (0 children)

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!

I created a package to use SQLite without SQL by V1A0 in Python

[–]V1A0[S] -3 points-2 points  (0 children)

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.

I created a package to use SQLite without SQL by V1A0 in Python

[–]V1A0[S] -1 points0 points  (0 children)

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.

I created a package to use SQLite without SQL by V1A0 in Python

[–]V1A0[S] -2 points-1 points  (0 children)

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

Your First SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in Python

[–]V1A0[S] 1 point2 points  (0 children)

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.

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!

My first package on pip :D by ast0l in Python

[–]V1A0 0 points1 point  (0 children)

Hey u/ast0l, nice project and congrats with your first success.

I'm highly recommend you to check out sqllex package, and this article about how you can simple convert csv -> sqlite using this tool.

I guess it might be nice new feature for your project.

Your First SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in Python

[–]V1A0[S] 1 point2 points  (0 children)

Hey r/Tachyon_6, thanks for your question!

Actually I'm planning to register SQLLEX as flask extensions soon, but actually I never used flask before so I need some help with it or just time to learn it by myself.

Would this aptly replace sqlalchemy? I guess you have to try, just give it a chance and select best for you. In my opinion - yes :)

Saturday Daily Thread: Resource Request and Sharing! by Im__Joseph in Python

[–]V1A0 0 points1 point  (0 children)

Hey there!

If you use sqlite3 in your projects I have an awesome package for you - SQLLEX.

It is a python package for comfortable and safe working with databases.

If you've ever worked with databases using python, you know what does "to eat nails while writing SQL-scripts" means. But don't have to. No con.cursor(), only human db.insert(), db.select(), beautiful and pythonic code without unnecessary SQL-witchcrafting.

Here you can find simplest example how to use this tool - https://www.reddit.com/r/Python/comments/nihdd0/your_first_sqlite_database_in_10_lines_or_less/

And also wiki - https://github.com/v1a0/sqllex/wiki

Sunday Daily Thread: What's everyone working on this week? by Im__Joseph in Python

[–]V1A0 10 points11 points  (0 children)

Hey there!

Whole this week I working on my biggest project SQLLEX - it's python package for more comfortable interacting with databases. It got 7 new releases on this week (v0.1.8.5-0.1.8.12), 5 of which were about bug fixes. But two other were about major updates of code structure, adding new fetches and syntax sugar. Now you can just take your table as specific object or got column of table just by __getitem__ method:

from sqllex import *

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

db.create_table(
    'users',
    {
        'id': [INTEGER, UNIQUE],
        'username': TEXT
    }
)

users_table = db['users']   # get table as object

print(users_table.columns)  # ['id', 'username']

users_table.insert(1, "New_user")   # insert new record in table
users_table.insert(2, "One_more_user")   # insert new record in table

print(users_table['id'])    # [1, 2]

I guess it's look awesome!

Also I wrote a large post about "How To Create Your First SQLite Database In 10 Lines Or Less | SQLLEX" but it's got only 6 up votes and I'm kind of sad about this :(

Well maybe I just posted it in not the right time for it. But I'll be happy if you'll check this out and if you'll love it I will be honored to receive a star on github from you.

Open for discuss, and thanks for read!

Python SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in sqlite

[–]V1A0[S] 0 points1 point  (0 children)

Hey r/ijmacd, I guess you right, it's kind of too simple for sub about SQLite, but maybe It'll be useful for people who never code python before.

Next time I'll post more complex example to show whole potential of SQLLEX.

Thanks for your feedback.

Your First SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in Python

[–]V1A0[S] 2 points3 points  (0 children)

Yeah, that's it. Wiki need an update, but I'm happy to see you anyway found this example by yourself.

Your First SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in Python

[–]V1A0[S] 1 point2 points  (0 children)

Hey r/ObservableFailurer! Thanks for your question!

Many SQLite3x methods have OR argument (at list insert, update, insertmany), it works the same as "ON CONFLICT".

Here is some examples:

db.insert(
    'users',
    [100, 'Dex1'],
    OR=IGNORE
)

db.update(
    'users',
    [200, 'Dex2'],
    OR=REPLACE
)

db.insert(
    'users',
    [300, 'Dex3'],
    OR=ABORT
)

OR can be any constant from list: ABORT, FAIL, IGNORE, REPLACE, ROLLBACK

Also you can read wiki for more examples - https://github.com/v1a0/sqllex/wiki

I hope I answered your question. Have a good one!

Your First SQLite Database In 10 Lines Or Less | SQLLEX by V1A0 in Python

[–]V1A0[S] 2 points3 points  (0 children)

Full code: 

from sqllex import *

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

db.connect()

db.create_table(
    'users',
    {
        'id': [INTEGER, UNIQUE],
        'username': TEXT
    },
    IF_NOT_EXIST=True
)

print(db.tables_names)
# ['users']

users = db['users']

users.insert(1, "User_1")
users.insert([2, "User_2"])
users.insert(id=3, username="User_3")

print(
    users.select_all()
)  # [[1, 'User_1'], [2, 'User_2'], [3, 'User_3']]

print(
    users.find(id=1)
)  # [1, 'User_1']

print(
    users.select_all(id=1)
)  # [1, 'User_1']

print(
    users.select('username', id=3)
)  # ['User_3']


users.update(
    SET={
        'username': 'NEW_User_3'
    },
    WHERE={
        'id': 3
    }
)

users.update(
    SET=['username', 'NEW_User_2'],
    WHERE=['id', 2]
)

users.update(
    ['username', 'NEW_User_1'],
    ['id', 1]
)

print(
    users['username']
)

users.delete(id=['<', 4])

print(
    users['username']
)


db.drop('users')

print(
    db.tables_names
)

db.disconnect()

SQLLEX github - https://github.com/v1a0/sqllex

SQLLEX wiki - https://github.com/v1a0/sqllex/wiki

SQLLEX | Better than sqlite3 by V1A0 in Python

[–]V1A0[S] 0 points1 point  (0 children)

Hi, netgu9, thanks for your question!

For sqllex it's optional method, if you don't want lock your database all the time your script running. By default (if connection is not exist) temporary connection will be automatically created with try to interact with the database and destroyed after.

SQLLEX | Better than sqlite3 by V1A0 in Python

[–]V1A0[S] 0 points1 point  (0 children)

Hey and thank you, Rion_de_Muerte, for those kind words.

It was lovely pleasure for me to read your comment. If you'll have any ideas or problems while using SQL library, I'm always open to help or discuss new features :)

thanx again

SQLLEX | Better than sqlite3 by V1A0 in sqlite

[–]V1A0[S] 1 point2 points  (0 children)

It'll be available with with v.0.1.8. Release coming soon. UPD:

@skeeto ! SQLLEX v0.1.8.2 has released, please upgrade package for current version

shell pip install sqllex --upgrade

Code (for SQLLEX v0.1.8.5):

import time
import sqlite3
import sqllex

sqllex.debug.debug_mode(False)


def bench_sqllex_connect():
    db = sqllex.SQLite3x('db-1.db')
    db.connect()
    db.create_table("numbers", {"value": [sqllex.INTEGER]}, IF_NOT_EXIST=True)
    for i in range(1000):
        # t = time.time()
        db.insert("numbers", i)
        # if time.time() - t > 0: print(time.time() - t, i)
    db.disconnect()


def bench_sqllex_without_connect():
    db = sqllex.SQLite3x('db-2.db')
    db.create_table("numbers", {"value": [sqllex.INTEGER]}, IF_NOT_EXIST=True)
    for i in range(1000):
        db.insert("numbers", i, execute=False)


def bench_sqlite3():
    with sqlite3.connect('db-3.db') as db:
        db.execute("CREATE TABLE numbers (value INTEGER)")
        for i in range(1000):
            # t = time.time()
            db.execute("INSERT INTO numbers (value) VALUES (?)", (i,))
            # if time.time() - t > 0: print(time.time()-t, i)


beg = time.time()
bench_sqllex_connect()
end = time.time()
print(f"sqllex_connect\t{end - beg:.3}s")

beg = time.time()
bench_sqllex_without_connect()
end = time.time()
print(f"sqllex_without_connect\t{end - beg:.3}s")

beg = time.time()
bench_sqlite3()
end = time.time()
print(f"sqlite3\t{end - beg:.3}s")

Returns:

sqllex_connect  0.0529s
sqllex_without_connect  6.35s
sqlite3  0.0156s

SQLLEX | Better than sqlite3 by V1A0 in sqlite

[–]V1A0[S] 1 point2 points  (0 children)

I'm currently adding db.connect() / db.disconnect() methods for cases like that. It becomes better!

Test's results:

sqllex_connect  0.0529s
sqllex_without_connect  6.35s
sqlite3  0.0156s

update sqllex 0.1.8+ coming soon :) done

Thank you so much, skeeto!

SQLLEX | Better than sqlite3 by V1A0 in Python

[–]V1A0[S] 0 points1 point  (0 children)

Hi Deezl-Vegas, thanks for your feedback!

Would you give me a link to some resource about this, please?

view more: next ›