Home

Welcome to the Sqllex Wiki! 👋

Here you can find some explanations and examples for Sqllex lib.

Chapters

• About

• Examples

• SQLite3x Methods

---

About

What the heck is Sqllex? 🤔

Squllex it's python library created to make it easier for developers to interact with databases. Currently it supports only SQLite databases but we plan to expand this list soon. SQLite is a nice and simple tool for admining databases and Sqllex make it even more easier and comfortable.

It'll be a lot easier to show then explain.

SQLite3x

Examples

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 the 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 indo 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 (what) FROM (where)
users_in_db = db.select('username', 'users')

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 equalised 1)
"""


users_group_1 = db.select(
    'username', 'users',
    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',  # here is name of table
    {           # here is table structure
        '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('username', 'users')

print(users_in_db)

users_group_1 = db.select(
    'username', 'users',
    where={'user_group': 1}
)

print(users_group_1)

Congratulation, now you know basic SQLite3x methods! Explore more features and method on the links down below.

Public SQLite3x methods:

• insert
• insertmany
• update
• replace
• select
• • select_distinct
• • select_all
• markup
• create_table
• • create_temp_table
• • create_temporary_table
• delete
• drop
• get_columns
• pragma
• • foreign_keys
• • journal_mode
• • table_info
• execute
• executemany