Home
Sqllex is a python library 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.
If you never tried working with databases before, but you really wan to, I'm highly recommend starting with SQLLEX. Due to it is add-on for sqlite3, it's really simple to find guides and fix bugs.
It'll be a lot easier to show then explain.
If you never used SQLite before read this awesome example
Imagine you need create some database, with structure like:
| Your awesome database | ||
|---|---|---|
| Table | Columns | Column params |
| Groups | id | INTEGER PRIMARY KEY UNIQUE |
| name | TEXT NOT NULL DEFAULT 'Unknown' |
|
| Users | id | INTEGER PRIMARY KEY UNIQUE |
| username | TEXT NOT NULL |
|
| user_group | FOREIGN KEY "user_group" REFERENCES groups "id" |
|
I don't need explains, just show me the code
"""
For the first, you need to import * (all) from Sqllex lib and init your database
"""
# import * (all) from Sqllex
from sqllex import *
# Init-ing your databse
db = SQLite3x(path='my_awesome_db.db')
"""
Ok, now we need to create your tables into a database,
use create_table method (as SQL-like CREATE TABLE)
"""
# Creating Groups table
db.create_table(
'groups', # here is name of table
{ # here is table structure
'id': [INTEGER, PRIMARY_KEY, UNIQUE], # group id
'name': [TEXT, NOT_NULL, DEFAULT, 'Unknown'] # group name
}
)
"""
And one more table
"""
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
}
})
"""
Well done, now let's add some groups and some users into your database
For example:
1: Admin
2: User
3: Guest
"""
# Record some groups for the first
db.insert('groups', id=1, name="Admin") # You can add data like this
db.insert('groups', [2, "User"]) # Or like this
db.insert('groups', 3, 'Guest') # Or like this
"""
Now let's add many users
"""
# Here we have a list of users in format: [id, name, group_id]
users_list = [
[0, "User_0", 1],
[1, "User_1", 2],
[2, "User_2", 3],
[3, "User_3", 1],
[4, "User_4", 2],
[5, "User_5", 3],
[6, "User_6", 1],
[7, "User_7", 2],
[8, "User_8", 3],
[9, "User_9", 1],
]
# Insert it by one line
db.insertmany('users', users_list)
# Done!
"""
Now we need to take it back by select method (as SQL-like SELECT)
"""
# SELECT FROM (table) (what)
users_in_db = db.select('users', 'username')
print(users_in_db)
# It'll print:
# ['User_0', 'User_1', 'User_2', 'User_3', 'User_4', 'User_5', 'User_6', 'User_7', 'User_8', 'User_9']
"""
Prefect, and now select some specific records
(only usernames where group_id parameter equalized 1)
"""
users_group_1 = db.select(
'users', 'username',
WHERE={'user_group': 1}
)
print(users_group_1)
# It'll print:
# ['User_0', 'User_3', 'User_6', 'User_9']Code without comments
from sqllex import *
db = SQLite3x(path='my_awesome_db.db')
db.create_table(
'groups',
{
'id': [INTEGER, PRIMARY_KEY, UNIQUE],
'name': [TEXT, NOT_NULL, DEFAULT, 'Unknown']
}
)
db.create_table(
name='users',
columns={
'id': [INTEGER, PRIMARY_KEY, UNIQUE],
'username': [TEXT, NOT_NULL, DEFAULT, 'Unknown'],
'user_group': INTEGER,
FOREIGN_KEY: {
"user_group": ["groups", "id"]
}
})
db.insert('groups', id=1, name="Admin")
db.insert('groups', [2, "User"])
db.insert('groups', 3, 'Guest')
users_list = [
[0, "User_0", 1],
[1, "User_1", 2],
[2, "User_2", 3],
[3, "User_3", 1],
[4, "User_4", 2],
[5, "User_5", 3],
[6, "User_6", 1],
[7, "User_7", 2],
[8, "User_8", 3],
[9, "User_9", 1],
]
db.insertmany('users', users_list)
users_in_db = db.select('users', 'username')
print(users_in_db)
users_group_1 = db.select(
'users', 'username',
WHERE={'user_group': 1}
)
print(users_group_1)