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:
from sqlalchemy_mixins import ActiveRecordMixin class User(Base, ActiveRecordMixin): 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
- Installation
- Quick Start
- Features
- Internal architecture notes
- Comparison with existing solutions
- Changelog
Use pip
pip install sqlalchemy_mixins
Run tests
python -m unittest discover sqlalchemy_mixins/
Here's a quick demo of what out 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('user', 'post', 'post.comments').first())
# subqueryload posts and their comments
print(User.with_subquery('posts', 'posts.comments').first())
# sort by rating DESC, user name ASC
print(Post.sort('-rating', 'user___name').all())
See full example
Main features are Active Record, Eager Load, Django-like queries and Beauty __repr__.
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
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
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
provided by EagerLoadMixin
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_({
'posts': {
'comments': {
'user': JOINED
}
}
}).all()
or we can write class properties instead of strings:
User.with_({
User.posts: {
Post.comments: {
Comment.user: JOINED
}
}
}).all()
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_({
'user': JOINED, # joinedload user
'comments': (SUBQUERY, { # load comments in separate query
'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.
For simple cases, when you want to just joinedload or subqueryload a few relations, we have easier syntax for you:
Comment.with_joined('user', 'post', 'post.comments').first()
User.with_subquery('posts', 'posts.comments').all()
Note that you can split relations with dot like
post.comments
due to this SQLAlchemy feature
See full example and tests
provided by SmartQueryMixin
We implement Django-like field lookups and automatic relation joins.
It means you can filter and sort dynamically by attributes defined in strings!
So, having defined Post
model with Post.user
relationship to User
model,
you can write
Post.where(rating__gt=2, user___name__like='%Bi%').all() # post rating > 2 and post user name like ...
Post.sort('-rating', 'user___name').all() # sort by rating DESC, user name ASC
(___
splits relation and attribute, __
splits attribute and operator)
All relations used in filtering/sorting should be explicitly set, not just being a backref
In our example,
Post.user
relationship should be defined inPost
class even ifUser.posts
is defined too.So, you can't type
class User(BaseModel): # ... user = sa.orm.relationship('User', backref='posts')and skip defining
Post.user
relationship. You must define it anyway:class Post(BaseModel): # ... user = sa.orm.relationship('User') # define it anyway
For DRY-ifying your code and incapsulating business logic, you can use SQLAlchemy's hybrid attributes and hybrid_methods. Using them in our filtering/sorting is straightforward (see examples and tests).
See full example and tests
Well, as SmartQueryMixin
does auto-joins for filtering/sorting,
there's a sense to tell sqlalchemy that we already joined that relation.
So that relations are automatically set to be joinedload if they were used for filtering/sorting.
So, if we write
comments = Comment.where(post___public=True, post___user___name__like='Bi%').all()
then no additional query will be executed if we will access used relations
comments[0].post
comments[0].post.user
Cool, isn't it? =)
See full example and tests
provided by SmartQueryMixin
In real world, we want to filter, sort and also eager load some relations at once.
Well, if we use the same, say, User.posts
relation in filtering and sorting,
it should not be joined twice.
That's why we combined filter, sort and eager load in one smartest method:
Comment.smart_query(
filters={
'post___public': True,
'user__isnull': False
},
sort_attrs=['user___name', '-created_at'],
schema={
'post': {
'user': JOINED
}
}).all()
See full example and tests
provided by ReprMixin
As developers, we need to debug things with convenient. When we play in REPL, we can see this
>>> session.query(Post).all()
[<myapp.models.Post object at 0x04287A50>, <myapp.models.Post object at 0x04287A90>]
Well, using our mixin, we can have more readable output with post IDs:
>>> session.query(Post).all()
[<Post #11>, <Post #12>]
Even more, in Post
model, we can define what else (except id) we want to see:
class User(BaseModel):
__repr_attrs__ = ['name']
# ...
class Post(BaseModel):
__repr_attrs__ = ['user', 'body'] # body is just column, user is relationship
# ...
Now we have
>>> session.query(Post).all()
[<Post #11 user:<User #1 'Bill'> body:'post 11'>,
<Post #12 user:<User #2 'Bob'> body:'post 12'>]
Long attributes will be cutted:
long_post = Post(body='Post 2 long-long body', user=bob)
>>> long_post
<Post #2 body:'Post 2 long-lon...' user:<User #1 'Bob'>>
And you can customize max __repr__ length:
class Post(BaseModel):
# ...
__repr_max_length__ = 25
# ...
>>> long_post
<Post #2 body:'Post 2 long-long body' user:<User #1 'Bob'>>
See full example and tests
Some mixins re-use the same functionality. It lives in SessionMixin
(session access) and InspectionMixin
(inspecting columns, relations etc.) and other mixins inherit them.
You can use these mixins standalone if you want.
Here's a UML diagram of mixin hierarchy:
There're a lot of extensions for SQLAlchemy, but most of them are not so universal.
We found several implementations of this pattern.
Cool, but it forces you to use their own way to instantiate SQLAlchemy
while to use ActiveRecordMixin
you should just make you model to inherit it.
Cool, but tightly coupled with Flask.
Framework-agnostic, but lacks of functionality (only save
method is provided) and Readme.
There exists sqlalchemy-django-query package which does similar things and it's really awesome.
But:
- it doesn't automatic eager load relations
- it doesn't work with hybrid attributes and hybrid_methods
sqlalchemy-repr already does this, but there you can't choose which columns to output. It simply prints all columns, which can lead to too big output.
More clear methods in EagerLoadMixin
:
-
added
with_subquery
method: it's likewith_joined
, but for subqueryload. So you can now write:User.with_subquery('posts', 'comments').all()
-
with_joined
method arguments change: instead ofComment.with_joined(['user','post'])
now simply write
Comment.with_joined('user','post')
-
with_
method arguments change: it now accepts only dict schemas. If you want to quickly joinedload relations, usewith_joined
-
with_dict
method removed. Instead, usewith_
method
Other changes in EagerLoadMixin
:
-
constants rename: use cleaner
JOINED
andSUBQUERY
instead ofJOINEDLOAD
andSUBQUERYLOAD
-
do not allow
None
in schema anymore, so instead ofComment.with_({'user': None})
write
Comment.with_({'user': JOINED})
Fix in InspectionMixin.columns
property.
It didn't return columns inherited from other class. Now it works correct:
class Parent(BaseModel):
__tablename__ = 'parent'
id = sa.Column(sa.Integer, primary_key=True)
class Child(Parent):
some_prop = sa.Column(sa.String)
Child.columns # before it returned ['some_prop']
# now it returns ['id', 'some_prop']
Fixed bug in ReprMixin
: it crashed for objects without ID (newly created ones, not added yet to the session).