SQLAlchemy mixins

Note: As of v1.3, only python 3.5+ is supported.

A pack of framework-agnostic, easy-to-integrate and well tested mixins for SQLAlchemy ORM.

Heavily inspired by Django ORM and Eloquent ORM

Why it's cool:

• framework-agnostic
• easy integration to your existing project:

   from sqlalchemy_mixins import AllFeaturesMixin
  
   class User(Base, AllFeaturesMixin):
        pass
  

• clean code, splitted by modules
• follows best practices of Django ORM, Peewee and Eloquent ORM,
• 95%+ test coverage
• already powers a big project

Russian readers, see related article on habrahabr.ru

Table of Contents

1. Installation
2. Quick Start
   1. Framework-agnostic
   2. Usage with Flask-SQLAlchemy
3. Features
   1. Active Record
      1. CRUD
      2. Querying
   2. Eager Load
   3. Django-like queries
      1. Filter and sort by relations
      2. Automatic eager load relations
   4. All-in-one: smart_query
   5. Beauty __repr__
   6. Serialize to dict
   7. Timestamps
4. Internal architecture notes
5. Comparison with existing solutions
6. Changelog

Installation

Use pip

pip install sqlalchemy_mixins

Run tests

python -m unittest discover sqlalchemy_mixins/

Quick Start

Framework-agnostic

Here's a quick demo of what our mixins can do.

bob = User.create(name='Bob')
post1 = Post.create(body='Post 1', user=bob, rating=3)
post2 = Post.create(body='long-long-long-long-long body', rating=2,
                    user=User.create(name='Bill'),
                    comments=[Comment.create(body='cool!', user=bob)])

# filter using operators like 'in' and 'contains' and relations like 'user'
# will output this beauty: <Post #1 body:'Post1' user:'Bill'>
print(Post.where(rating__in=[2, 3, 4], user___name__like='%Bi%').all())
# joinedload post and user
print(Comment.with_joined(Comment.user, Comment.post).first())
# subqueryload posts
print(User.with_subquery(User.posts).first())
# sort by rating DESC, user name ASC
print(Post.sort('-rating', 'user___name').all())
# created_at, updated_at timestamps added automatically
print("Created Bob at ", bob.created_at)   
# serialize to dict, with relationships
print(bob.to_dict(nested=True).all())

See full example

To interactively play with this example from CLI, install iPython and type ipython -i examples\all_features.py

Usage with Flask-SQLAlchemy

import sqlalchemy as sa
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy_mixins import AllFeaturesMixin

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
db = SQLAlchemy(app)

######### Models ######### 
class BaseModel(db.Model, AllFeaturesMixin):
    __abstract__ = True
    pass


class User(BaseModel):
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

######## Initialize ########
BaseModel.set_session(db.session)

######## Create test entity ########
db.create_all()
user = User.create(name='bob')
print(user)

*** Autocommit ***

This library relies on SQLAlchemy's autocommit flag. It needs to be set to True when initializing the session i.e:

session = scoped_session(sessionmaker(bind=engine, autocommit=True))
BaseModel.set_session(session)

or with Flask-SQLAlchemy

db = SQLAlchemy(app, session_options={'autocommit': True})

Features

Main features are

• Active Record
• Eager Load
• Django-like queries
• Beauty __repr__
• Timestamps
• Serialize to dict

Active Record

provided by ActiveRecordMixin

SQLAlchemy's Data Mapper pattern is cool, but Active Record pattern is easiest and more DRY.

Well, we implemented it on top of Data Mapper! All we need is to just inject session into ORM class while bootstrapping our app:

BaseModel.set_session(session)
# now we have access to BaseOrmModel.session property

CRUD

We all love SQLAlchemy, but doing CRUD is a bit tricky there.

For example, creating an object needs 3 lines of code:

bob = User(name='Bobby', age=1)
session.add(bob)
session.flush()

Well, having access to session from model, we can just write

bob = User.create(name='Bobby', age=1)

that's how it's done in Django ORM and Peewee

update and delete methods are provided as well

bob.update(name='Bob', age=21)
bob.delete()

And, as in Django and Eloquent, we can quickly retrieve object by id

User.find(1) # instead of session.query(User).get(1)

and fail if such id doesn't exist

User.find_or_fail(123987) # will raise sqlalchemy_mixins.ModelNotFoundError

See full example and tests

Querying

As in Flask-SQLAlchemy, Peewee and Django ORM, you can quickly query some class

User.query # instead of session.query(User)

Also we can quickly retrieve first or all objects:

User.first() # instead of session.query(User).first()
User.all() # instead of session.query(User).all()

See full example and tests

Eager load

provided by EagerLoadMixin

Nested eager load

If you use SQLAlchemy's eager loading, you may find it not very convenient, especially when we want, say, load user, all his posts and comments to every his post in the same query.

Well, now you can easily set what ORM relations you want to eager load

User.with_({
    User.posts: {
        Post.comments: {
            Comment.user: JOINED
        }
    }
}).all()

Subquery load

Sometimes we want to load relations in separate query, i.e. do subqueryload. For example, we load posts on page like this, and for each post we want to have user and all comments (and comment authors).

To speed up query, we load comments in separate query, but, in this separate query, join user

from sqlalchemy_mixins import JOINED, SUBQUERY
Post.with_({
    Post.user: JOINED, # joinedload user
    Post.comments: (SUBQUERY, {  # load comments in separate query
        Comment.user: JOINED  # but, in this separate query, join user
    })
}).all()

Here, posts will be loaded on first query, and comments with users - in second one. See SQLAlchemy docs for explaining relationship loading techniques.

Quick eager load

For simple cases, when you want to just joinedload or subqueryload a few relations, we have easier syntax for you:

Comment.with_joined(Comment.user, Comment.post).first()
User.with_subquery(User.posts).all()

See full example and tests

Filter and sort by relations

provided by SmartQueryMixin

Django-like queries

We implement Django-like field lookups and automatic relation joins.

It means you can filter and sort dynamically by attributes defined in strings!