SQLite3x | Awesome example #1

# 
# For the first, you need to import database-class and constants from Sqllex lib and init your database
# 

# from sqllex import *
from sqllex.classes import SQLite3x
from sqllex.constants.sqlite import *

db = SQLite3x(path='my_awesome_db.db')  # Init-ing your database

db.connect()    # It'll lock yor database until you disconnect, but makes sqllex work damn faster


# 
# 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 this data
#

groups = db['groups']   # Get table 'groups' from db as object

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

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

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

#
# Same thing but without table object
# db.insert('groups', id=1, name="Admin")
# db.insert('groups', (2, "User"))
# db.insert('groups', 3, 'Guest')
#

# 
# Now let's add many users, like a large dataset
# 


# Down below is a list of users, 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),
]

users = db['users']     # Get table 'groups' from db as object

users.insertmany(users_list)    # Insert it all by one line

#
# Done!
# 
# Now we need to take it back by select method (as SQL-like SELECT)
# 

users_in_db = users.select('username')  # Without any special arguments == SELECT ALL (by default)

print(users_in_db)  # [('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
# For example: 
# only 'usernames' of records WHERE column 'user_group' == 1
# 


users_group_1 = users.select(
    'username',
    WHERE=(users['user_group'] ==  1),
)

# or you can set this argument different ways
#   WHERE={'user_group': 1}
#   WHERE=['user_group', 1]
#   WHERE="user_group = 1"


print(users_group_1)    # [('User_0',), ('User_3',), ('User_6',), ('User_9',)]


#
# And some large example for some another imaginary table
# !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
# !!! CODE DOWN BELOW WOULD NOT WORK AS IS !!!
# !!!    This is an example of syntax      !!!
# !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
#

# Old and simple way 
users.select(
    SELECT=[                                                                 # SELECT username, group_name, description
        'username',
        'group_name',
        'description'
    ],      
    JOIN=(                                                                   # JOIN
        ('groups', AS, 'gr', ON, 'users.group_id == gr.group_id'),           # INNER JOIN groups AS gr ON us.group_id == gr.group_id
        (CROSS_JOIN, 'about', 'ab', ON, 'ab.group_id == gr.group_id')        # CROSS JOIN about ab ON ab.group_id == gr.group_id
    ),
    WHERE= (users['username'] != 'user_1') & (users['username'] != 'user_2'),  # WHERE (users.username<>'user_1') AND (users.username<>'user_2')
    ORDER_BY='age DESC',                                                     # ORDER BY age DESC
    LIMIT=50,                                                                # LIMIT 50
    OFFSET=20                                                                # OFFSET 20
)

db.disconnect() # unlock your database and save all changes

# DATABASE SCHEMA
# {
#      'position': {
#          'id': [INTEGER, PRIMARY_KEY, AUTOINCREMENT],
#          'name': TEXT,
#          'description': [TEXT, DEFAULT, NULL],
#      },
#      'employee': {
#          'id': [INTEGER, PRIMARY_KEY, AUTOINCREMENT],
#          'firstName': TEXT,
#          'surname': TEXT,
#          'age': [INTEGER, NOT_NULL],
#          'positionID': INTEGER,
#
#          FOREIGN_KEY: {
#              'positionID': ['position', 'id']
#          }
#      },
#      'payments': {
#          'date': [TEXT],
#          'employeeID': INTEGER,
#          'amount': [INTEGER, NOT_NULL],
#          
#          FOREIGN_KEY: {
#              'positionID': ['employee', 'id']
#          },
#      }
# }

db['employee'].select(
    SELECT=[
        db['employee']['id'],
        db['employee']['firstName'],
        db['position']['name']
    ],
    JOIN=(
        INNER_JOIN, self.db['position'],
        ON, db['position']['id'] == db['employee']['positionID']
    ),
    ORDER_BY=(
        db['position']['id'],
        'DESC'
    )
)

# DATABASE SCHEMA
# {
#      'position': {
#          'id': [INTEGER, PRIMARY_KEY, AUTOINCREMENT],
#          'name': TEXT,
#          'description': [TEXT, DEFAULT, NULL],
#      },
#      'employee': {
#          'id': [INTEGER, PRIMARY_KEY, AUTOINCREMENT],
#          'firstName': TEXT,
#          'surname': TEXT,
#          'age': [INTEGER, NOT_NULL],
#          'positionID': INTEGER,
#
#          FOREIGN_KEY: {
#              'positionID': ['position', 'id']
#          }
#      },
#      'payments': {
#          'date': [TEXT],
#          'employeeID': INTEGER,
#          'amount': [INTEGER, NOT_NULL],
#          
#          FOREIGN_KEY: {
#              'positionID': ['employee', 'id']
#          },
#      }
# }

self.db['employee'].select(
    SELECT=[
        db['employee']['id'],
        db['employee']['firstName'],
        db['position']['name']
    ],
    JOIN=(
        (
            LEFT_JOIN, db['position'],
            ON, db['position']['id'] == db['employee']['positionID']
        ),
        (
            INNER_JOIN, self.db['payments'],
            ON, db['employee']['id'] == db['payments']['employeeID']
        )
    ),
    ORDER_BY=(
        db['payments']['amount'],
        'DESC'
    )
)