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.

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)

"""
    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!

What if I have LARGE dataset to insert? Still easy.

...
# Your awesome dataset
dataset = [
    [3, "pi", 0],
    [1, "pi", 1],
    [4, "pi", 2],
    [1, "pi", 3],
    [5, "pi", 4],
    [9, "pi", 5],
    [2, "pi", 6],
    ...
]

# One line
db.insertmany('math', dataset)

# Done

Advances

Ok ok, what if you are SUPER_PRO_1337_SQL_GOY, and you need 100% of SQL features. Check this out.

from sqllex import *

db = SQLite3x(
    path='./path/my_awesome.db',
    template={
        "groups": {
            "group_id": [INTEGER, PRIMARY_KEY, UNIQUE],
            "name": [TEXT, NOT_NULL, DEFAULT, 'Unknown'],
        }
    }
)

# Insert some groups into groups table
db.insertmany(
    'groups',
    group_id=[1, 2],
    name=["Admin", "User"],
)


# Let's create a new table with some FOREIGN_KEYs
db.create_table(
    name='users',
    columns={
        "username": [TEXT, NOT_NULL, DEFAULT, 'Unknown'],
        "group_id": INTEGER,
        FOREIGN_KEY: {
            "group_id": ["groups", "group_id"]
        },
    },
    without_rowid=True,
    as_=__something__
)

# Insert some users into users table
db.insertmany(
    table='users',
    username=['User_1', 'User_2', 'User_3', 'User_4', 'User_5', 'User_6'],
    group_id=[1, 2, 1, 1, 2, 2]
)

db.insert(
    or_=REPLACE,
    table='users',
    username='User_4',
    group_id=1,
)

# ANd now take it back whith some conditions
users = db.select(
    select='username',
    table='users',
    where={
        'group_id': 2
    },
    order_by={
        'username': 'DESC'
    },
    with_=__something__,
    limit=10,
    offset=1,
    execute=True,
)

# and do your business ...

SQLite3x

Public 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