Models

Any class can be used as a model. However, subclassing sqlorm's Model class provides a number of advantages:

• ActiveRecord style methods to quickly fetch / save the object
• Tracking of dirty attributes to only save attributes that have been modified
• Auto fetching lazy attributes and relationships when accessed

Note

When using classes that do not subclass Model, Mapper.from_class() is used to generate a mapping. See Mapping any class.

Defining models

Models are classes inheriting from Model. To define which table they represent, use the table class property.

To define column mapping, define properties via annotations. The type used will be converted to an sql type. For more control over the mapping, you can use instantiate Column() objects:

from sqlorm import Model

class Task(Model):
    table = "tasks"

    id: int
    title: str = Column(type="varchar(20)") # set the column type (used in create_table())
    done = Column("completed", bool, default=False) # no annotation, column name is "completed" but property name will be "done"

Once columns are defined via annotations or Column properties, they are accessible as class and instance properties.

As class properties, they can be used as a composable piece of sql:

stmt = SQL("SELECT * FROM tasks WHERE", Task.done == False)

As instance properties, they can be get or set like any other property. Lazy loading and dirty flagging is performed accordingly.

task = Task()
task.title = "title"

Tip

To create abstract models which are not meant to be mapped, make sure your class subclasses abc.ABC

import abc

class MyBaseModel(Model, abc.ABC):
    # ...
class MyModel(MyBaseModel):
    # ...

SQL methods on models

Create SQL methods as you would SQL functions. Use @classmethod and @staticmethod decorator when needed.

class Task(Model):
    @classmethod
    def find_todos(cls):
        "SELECT * FROM tasks WHERE NOT done"

    def toggle(self):
        "UPDATE tasks SET done = NOT done WHERE id = %(self.id)s RETURNING *"

with engine:
    todos = Task.find_todos() # SELECT * FROM tasks WHERE NOT done

    task = Task.get(1)
    task.toggle() # UPDATE tasks SET done = NOT done WHERE id = 1 RETURNING *

By default, the following things happen:

• SELECT statements use fetchhyddrated(Model, ...)
• INSERT and UPDATE statements use execute() unless RETURNING is used in the statement, in which case it will update the object with the fetched value (using fetchhydrated(Model, ..., obj=self))
• DELETE statements use execute()

You can change this default behavior using query decorators: fetchall, fetchone, fetchscalar, fetchscalars, execute, update.

from sqlorm import update

class Task(Model):
    @update # update the object using the fetched row
    def refresh_title(self):
        "SELECT title FROM tasks WHERE id = %(self.id)s"

with engine:
    task = Task(id=1)
    task.refresh_title() # SELECT title FROM tasks WHERE id = 1
    assert task.title

Because the SELECT FROM part of the query can be auto generated by sqlorm based on your model definition, you can start statements with SELECT WHERE directly.

class Task(Model):
    @classmethod
    def find_todos(cls):
        "SELECT WHERE NOT done"

Similarly:

• INSERT INTO (col1) VALUES (%(value)s) will be transformed to INSERT INTO {table} (...
• UPDATE SET col = %(value)s to UPDATE {table} SET ...
• DELETE WHERE ... to DELETE FROM {table} WHERE ...
• WHERE SELF to WHERE {self.__mapper__.primary_key_condition(self)}

You can also create methods using query decorators directly:

from sqlorm import Model, fetchall, update, SQL

class Task(Model):
    @classmethod
    @fetchall
    def find_todos(cls):
        return cls.select_from().where(done=False)

    @update
    def toggle(self):
        return SQL.update(self.table, {"done": SQL("not done")}).where(id=self.id).returning("*")

Note

Using select_from() also ensures that the column list, lazy and eager loading are respected.

Querying model objects

Models can be used like any other classes with the model argument of the fetch api. However, Model also exposes easier to use ways to fetch data using without the need of explicitely passing a transaction. When called out of a transaction context, a non commited transaction will automatically be started. If bound to an engine, these class methods can also be called out of a session context.

• query() executes the provided statement using fetchhydrated()
• find_all() constructs a select statement based on the provided arguments and executes using query()
• find_one() same as find_all() but only returns the first row
• get() to find one row by primary key

The two finder methods can take as argument a where condition (sql string) or keyword arguments representing attributes to filter by.

with engine:
    todos = Task.query("SELECT * FROM tasks WHERE NOT done")
    todos = Task.find_all("NOT done")
    todos = Task.find_all(Task.done==False)
    todos = Task.find_all(done=False)
    task = Task.find_one("id=1")
    task = Task.get(1)

Tip

You can also build select statement with auto populated column list and from clause using Model.select_from().

Mapped columns can easily be used as pieces of composable sql: accessing the class attribute representing the column returns an SQL.Col object that can be used with python operators to return sql conditions:

import datetime

with engine:
    todos = Task.find_all(Task.done==False & Task.created_at<datetime.date.today())

Manipulating model objects

Manipulate model objects as you would with any python objects. The following methods help you execute DML statements:

• save() executes insert() or update() depending on the fact that the object has a primary key or not
• insert() executes an insert statement
• update() executes an update statement
• delete() deletes a delete statement
• refresh() executes a select statement (same as get()) and updates the object attribute values
• create() a class method to create and insert an object in one line

These methods (apart from create()) return a boolean indicating if the operation was performed.

Note

DML (Data Manipulation Language) statements are the statement that modify data in the database (insert, update and delete mostly)

The data used to insert or update will be limited to "dirty" attributes, which means attributes that have been modified since the last DML statement. Setting an attribute will automatically flag it as dirty.

from sqlorm import is_dirty

with engine:
    task = Task.create(title="my task") # INSERT INTO tasks (title) VALUES ('my task')

    task = Task()
    task.title = "my task"
    task.save() # INSERT INTO tasks (title) VALUES ('my task')
    # same as task.insert()

    task = Task.get(1)
    task.title = "renamed task"
    assert is_dirty(task) == True
    task.save() # UPDATE tasks SET title = 'renamed task' WHERE id = 1
    # same as task.update()

    task = Task.get(2)
    task.delete() # DELETE FROM tasks WHERE id = 2

    task = Task()
    task.id = 1
    task.refresh() # SELECT * FROM tasks WHERE id = 1

Tip

You can also manually flag an attribute as dirty using flag_dirty_attr(obj, attr). Dirty attributes are stored in the object's __dirty__ attribute.

If you do not wish to use dirty tracking, you can disable it in the model definition:

class MyBaseModel(Model, abc.ABC):
    class Meta:
        insert_update_dirty_only = False

Tip

Which engine is used when manipulating models entirely depends on the current session context. Additionnally, sqlorm do not track objects globally.
This means objects can be serialized easily and re-used in other sessions without any concept of "attaching them" etc...

As a result, you can easily move objects from one engine to another:

with engine1:
   task = Task.get(1) # SELECT * FROM tasks WHERE id=1
with engine2:
   task.insert() # INSERT INTO tasks (id, ...) VALUES (1, ...)

Handling unknown columns

By default, unknown columns (ones which are not mapped), will be set as attributes on the object. Dirty attributes which are not mapped will also be saved in DML statements.

You can thus create models with no mapped columns. However, models require a primary key to function properly. One will be automatically added when non are defined (named "id").

When no columns are mapped, SELECT * is used.

class Task(Model):
    table = "tasks"

with engine:
    task = Task.find_all(done=False) # SELECT * FROM tasks WHERE done = false
    print(task.title) # works
    task.title = "renamed task"
    task.save() # UPDATE tasks SET title = 'renamed task' WHERE id = 1

Tip

ModelClass.c is a shorthand to access the mapper columns (ModelClass.__mapper__.columns). When unknown columns are allowed, you can use any attributes to get a column object to build queries.

todos = Task.find_all(Task.c.done==False)

Warning

You should not disable dirty tracking when allowing unknown columns otherwise setting attributes will not result in them being used in DML statements unless they are mapped.
When dirty tracking is disabled and you are using unknown attributes, the only way sqlorm keeps track of them is through the __hydrated_attrs__ attribute.

You can disallow unknown columns

class MyBaseModel(Model, abc.ABC):
    class Meta:
        allow_unknown_columns = False

Or change the default primary key name:

class MyBaseModel(Model, abc.ABC):
    class Meta:
        auto_primary_key = "uid" # or False to disable auto creating primary key

Relationships

Define relationships on models using Relationship(target_model, target_column, source_column):

from sqlorm import Relationship

class Post(Model):
    comments = Relationship("Comment", "post_id") # target model can be a string if not yet defined, default source column is the primary key

class Comment(Model):
    post = Relationship(Post, "id", "post_id", single=True) # use single=True for many-to-one relationships

with engine:
    post = Post.get(1)
    for comment in post.comments: # SELECT executed now to load comments
        print(comment.content)

    comment = Comment.get(1)
    print(comment.post.slug) # SELECT executed now to load post

Note

There are no notion of backref like you may find in other ORMs. I prefer the explicit nature of declaring the relationship both ways

The list of objects when not using single=True is not a normal python list. It can be iterated over and accessed through brackets. However, it only contains 2 methods to modify the list: append and remove. These methods will set the attribute on the related object. It will not save the object.

with engine:
    post = Post.get(1)
    comment = Comment(content="hello")
    post.comments.append(comment) # set comment.post_id
    comment.save()

By default, relationships are always lazy loaded. Eager loading is possible by setting lazy=False or using with_rels in finder methods.

class Comment(Model):
    post = Relationship("Post", "id", "post_id", single=True, lazy=False) # add lazy=False

with engine:
    c = Comment.get(1) # SELECT comments.*, posts.id as posts__id, posts.slug as posts__slug, ... FROM comments LEFT JOIN posts ON posts.id = comments.post_id
    c.post.slug # no statement executed, already loaded

    post = Post.get(1, with_rels=["comments"]) # will load comments through the composite row mechanism
    for comment in post.comments: # no statement executed, already loaded
        # ...

Tip

Use with_rels=False to prevent eager loading relationships

Sometimes you want to query through relationships but not load the related objects. Use with_joins similarly as with_rels but it will only add the join clauses. You can also access attributes through the relationship object to get properly table aliased columns:

comments = Comment.find_all(Comment.post.slug=="slug", with_joins=["post"]) # SELECT comments.* FROM comments LEFT JOIN posts ON posts.id = comments.post_id WHERE posts.slug == 'slug'
comments = Comment.query(Comment.select_from(with_joins=[Comment.post]).where(Comment.post.slug=="slug")) # alternative

Tip

The join clause is customizable using join_type and join_condition

class Post(Model):
   comments = Relationship("Comment", "post_id",
       join_condition="{target_alias}.post_id = {source_alias}.id AND NOT {target_alias}.archived") # an sql template with 2 special locals: target_alias and source_alias

class Comment(Model):
   post = Relationship(Post, "id", "post_id", single=True, join_type="INNER JOIN")

Note

There are no support for many-to-many relationships for now

Warning

sqlorm does not handle deleting related objects when the parent is deleted. Use cascading rules in your table definitions.

Binding models to engines

Unless bound to a specific engine, models will need a session context to execute statements. When bound to an engine, a non-commiting session is automatically created to execute statements.

To bind models to an engine, create a new base model class using Model.bind():

Model = Model.bind(engine)

class Task(Model):
    pass

tasks = Task.find_all() # works without a session context

You can also bind a model to an engine by simply setting its __engine__ class attribute.

Note

While binding engine to models is possible, it is not the preferred way to use sqlorm

Model registry

When defining models using the Model class, the model classes are registered in a model registry available under Model.__model_registry__.

Using Model.bind() will create a new registry only for the subclasses of this new base class.

You can reference models in your sql methods:

class Post(Model):
    def list_comments(self):
        "SELECT * FROM {Comment.table} WHERE {Comment.post_id} = %(self.id)"

class Comment(Model):
    pass

Eager and lazy loading columns

Sometime you want to load additional data as part of the querying of objects and other times you want to delay loading additional data to when it's actually needed.

Lazy and eager loading mechanism only apply when using select_from(), find_all(), find_one() and get().

class Task(Model):
    lazy_column = Column(lazy=True)

    # create lazy groups: all columns of a group are loaded together when one is accessed
    grouped_lazy_column1 = Column(lazy="group1")
    grouped_lazy_column2 = Column(lazy="group1")


with engine:
    task = Task.get(1)
    # lazy columns are not loaded, they weren't included in the select statement
    task.lazy_column # SELECT lazy_column FROM tasks WHERE id=1
    task.grouped_lazy_column1 # SELECT grouped_lazy_column1, grouped_lazy_column2 FROM tasks WHERE id=1
    task.grouped_lazy_column2 # no select, already loaded

You can eager load lazy fields using with_lazy in query methods:

with engine:
    task = Task.get(1, with_lazy=True)
    task = Task.get(1, with_lazy=["group1"]) # only load some lazy items (column or group names)

Column types

Columns can have a type which defines the SQL type and serialization/deserialization functions.

Note

The goal is not to re-define sql types in python. Types are optional in your column definitions. sqlorm relies mainly on the underlying DBAPI driver to do the conversion. Drivers have custom methods to provide type mapping.

Define types using SQLType:

from sqlorm import SQLType
import json

JSON = SQLType("json", json.loads, json.dumps)

class MyModel(Model):
    my_json_column = Column(type=JSON)

The following types are already defines and importable from the sqlorm package: Integer, Decimal, Varchar, Text, Boolean, Date, Time, DateTime, JSON, Pickle.

Python types from annotations will automatically used one of these type when appropriate (see sqlorm.types.PYTHON_TYPES_MAP).