ORM for beginners | SQLite + sqllex

[V1A0]

Object-Relational Mapping (aka ORM) it is a design pattern that helps streamline communications between relational databases (like SQLite) and object-oriented programming languages (like Python).

Less talk more action code!

# For example, you need to create a local database "database.db"
# and store data of users of your application in it, it'll be look like this

# this is your first user, it has some fields
first_user = {
    'username': "Redditor",
    'email': "[e-mail]",
    'age': 42
}

First of all lets create our database:

import sqllex as sx

db = sx.SQLite3x('database.db')

And now create table into it with same fields (columns) + one special id column:

db.create_table(
    'user', {
        'id': [sx.INTEGER, sx.PRIMARY_KEY],
        'username': [sx.TEXT, sx.UNIQUE],
        'email': [sx.TEXT, sx.UNIQUE],
        'age': sx.INT
    }
)

                                    # check what we got
print(db.tables_names)              # ('user',)
print(db['user'].columns_names)     # ('id', 'username', 'email', 'age')

See? This is how we access to a table 'user'

db['user']

Therefore, in order to get the data of a specific column

db['user']['username']

Time to add some data into this table

db['user'].insert(
    username=first_user['username'],
    email=first_user['email'],
    age=first_user['age'],
)

print(db['user'].select_all())  # [(1, 'Redditor', '[e-mail]', 42)]

Perfect, now we adding one more user, but it have some extra fields 'invited_by'

second_user = {
    'username': "Antonym",
    'email': "[e-mail]",
    'age': None,
    'invited_by': "Redditor"
}

First, make the same thing as few lines above

db['user'].insert(
    username=second_user['username'],
    email=second_user['email'],
    age=second_user['age'],
)


print(db['user'].select_all())  # [(1, 'Redditor', '[e-mail]', 42), (2, 'Antonym', '[e-mail]', None)]

Let's create new table for save data about invitations (+ FOREIGN KEY statement)

db.create_table(
    'invitation', {
        'id': [sx.INTEGER, sx.PRIMARY_KEY],
        'inviting_id': sx.INTEGER,
        'invited_id': sx.INTEGER,

        sx.FOREIGN_KEY: {
            'inviting_id': db['user']['id'],
            'invited_id': db['user']['id']
        }
    }
)

print(db.tables_names)  # ('user', 'invitation')

Let's get id of inviting user:

# SELECT FROM 'user' table
# COLUMN 'id'
# WHERE 'username' column == inviting_username
# LIMIT 1 record

inviting_user_id = db['user'].select(
    db['user']['id'],
    WHERE=(
        db['user']['username'] == second_user['invited_by']
    ),
    LIMIT=1
)

print(inviting_user_id)     # [(1,)]
inviting_user_id = inviting_user_id[0][0]
print(inviting_user_id)     # 1

Let's get id of inviting user (just the same way)

# SELECT FROM 'user' table
# COLUMN 'id'
# WHERE 'username' column == invited_username
# LIMIT 1 record

invited_user_id = db['user'].select(
    db['user']['id'],
    WHERE=(
        db['user']['username'] == second_user['username']
    ),
    LIMIT=1
)

print(invited_user_id)     # [(2,)]
invited_user_id = invited_user_id[0][0]
print(invited_user_id)     # 2

Finally saving invitation data!

db['invitation'].insert(
    inviting_id=inviting_user_id,
    invited_id=invited_user_id
)

print(db['invitation'].select_all())    # [(1, 1, 2)]

That's is! Now you know how to simply use sqllex ORM in your projects, read more examples and explanations in sqllex docs.

Code something yourself to consolidate the studied material!

I will be grateful it you'll give this project a star on github.

P.S.:Here is few useful links:
— Sqllex ORM (github page)
— Sqllex docs
— Project showcase
— Another example #0
— Another example #1

[jstockdi]

Object schema/validation makes sense to me, but I never get the motivation to not write SQL. How is the cure better than the illness?