Welcome to the Sqllex Documentation! 👋


Here you can find some explanations and examples for Sqllex ORM


Chapters

About

What the heck is Sqllex? 🤔

Sqllex is a python ORM for comfortable and safe interaction with databases.

If you've ever worked with databases using python, you know what does "Eat nails while writing SQL-scripts" means. So give a sqllex deal with it, just call needed method, give it a data or necessary parameters and done.

THERE NO con.cursor(), only human db.insert(), db.select(), db['table'], only beautiful and pythonic code without unnecessary SQL-witchcrafting.

Sqllex has friendly API, documentation and it's awesome for beginners (but not only). By the reason it is just an add-on for sqlite3/psycopg2, there will be easy to find explains for typical sqlite3 raised errors. And also in any moment you can call db.execute() method and run any sql-script directly in the database.

It'll be a lot easier to show then explain. So check out examples down below.

SQLite3

Sqllex was originally created for SQLite. So It's currently best suited especially for work with it.

Postgres

PostgreSQL now is only partially support. It has the same api interface as SQLite3x so feel free to use documentation of it for PostgreSQLx. Need to installed ``

Contents

How does this work?

Basic idea of sqllex as any ORM is to give user an interface to interact with a database as abstract object.

┌───────────────────────────────────────────┐ 
│  DATABASE                                 │        
│     ┌───────────────────────────────────┐ │
│     │  TABLE                            │ │
│     │     ┌───────────────────────────┐ │ │
│     │     │  COLUMN                   │ │ │
│     │     │     ┌───────────────────┐ │ │ │
│     │     │     │  DATA             │ │ │ │
│     │     │     └───────────────────┘ │ │ │
│     │     └───────────────────────────┘ │ │
│     │                                   │ │
│     └───────────────────────────────────┘ │
└───────────────────────────────────────────┘

Imagine you have a database called 'server.db' with only one table inside named 'users', this table has 3 columns 'id', 'name' and 'age' (so as a pic down below)

┌───────────────────────────────────────────┐ 
│  DATABASE 'server.db'                     │             
│     ┌───────────────────────────────────┐ │
│     │  TABLE 'users'                    │ │
│     │     ┌───────────────────────────┐ │ │
│     │     │  COLUMN 'id'              │ │ │
│     │     │     ┌───────────────────┐ │ │ │
│     │     │     │  INTEGER DATA     │ │ │ │
│     │     │     └───────────────────┘ │ │ │
│     │     └───────────────────────────┘ │ │
│     │     ┌───────────────────────────┐ │ │
│     │     │  COLUMN 'name'            │ │ │
│     │     │     ┌───────────────────┐ │ │ │
│     │     │     │  TEXT DATA        │ │ │ │
│     │     │     └───────────────────┘ │ │ │
│     │     └───────────────────────────┘ │ │
│     │     ┌───────────────────────────┐ │ │
│     │     │  COLUMN 'age'             │ │ │
│     │     │     ┌───────────────────┐ │ │ │
│     │     │     │  INTEGER DATA     │ │ │ │
│     │     │     └───────────────────┘ │ │ │
│     │     └───────────────────────────┘ │ │
│     └───────────────────────────────────┘ │
└───────────────────────────────────────────┘

With the sqllex you can interact with this database in the craziest way

# Import necessary modules
import sqllex as sx

# Database
db = sx.SQLite3x('server.db')

# Create this table
db.create_table(
  'users',
  {
    'id': sx.INTEGER,
    'name': sx.TEXT,
    'age': sx.INTEGER
  },
  IF_NOT_EXIST=True
)

table_users = db['users']
# table_users = db.get_table('users') <-- the same

# add one user
table_users.insert(1, 'Sqllex', 33)

# add 3 more
table_users.insertmany(
  (2, 'Phizilion', 22),
  (3, 'kingabzpro', 44),
  (4, 'asadafasab', 55)
)

print(table_users.select_all()) # [(1, 'Sqllex', 33), (2, 'Phizilion', 22), (3, 'kingabzpro', 44), (4, 'asadafasab', 55)]

# Get column age as object
column_age = table_users['age']

print(
  table_users.select(
    WHERE=(column_age > 40)
  )
) # [(3, 'kingabzpro', 44), (4, 'asadafasab', 55)]


column_name = table_users['name']


print(
  table_users.select(
    WHERE=(column_age > 40) & (column_name |sx.LIKE| 'kin%')
  )
) # [(3, 'kingabzpro', 44)]

Also, you can do crazy things like this

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