[](https://pypi.python.org/pypi/SqlalchemyMixin)
# SQLAlchemy Mixin
A pack of framework-agnostic, easy-to-integrate for SQLAlchemy ORM.
Heavily inspired by [Django ORM](https://docs.djangoproject.com/en/1.10/topics/db/queries/)
and [Eloquent ORM](https://laravel.com/docs/5.4/eloquent)
easy integration to your existing project like [FastApi](https://fastapi.tiangolo.com):
```python
from sqlalchemy_mixins import BaseMixin
class User(Base, BaseMixin):
pass
```
## Table of Contents
1. [Installation](#installation)
1. [Quick Start](#quick-start)
1. [Framework-agnostic](#framework-agnostic)
1. [Usage with Flask-SQLAlchemy](#usage-with-flask-sqlalchemy)
1. [Features](#features)
1. [Active Record](#active-record)
1. [CRUD](#crud)
1. [Querying](#querying)
1. [Eager Load](#eager-load)
1. [Django-like queries](#django-like-queries)
1. [Filter and sort by relations](#filter-and-sort-by-relations)
1. [Automatic eager load relations](#automatic-eager-load-relations)
1. [All-in-one: smart_query](#all-in-one-smart_query)
1. [Beauty \_\_repr\_\_](#beauty-__repr__)
1. [DateMixin](#timestamps)
## Installation
Use pip
```
pip install SqlalchemyMixin
```
## Quick Start
### Framework-agnostic
Here's a quick demo of what our mixins can do.
```python
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())
# created_at, updated_at timestamps added automatically
print("Created Bob at ", bob.created_at)
# serialize to dict, with relationships
```
### Usage with Flask-SQLAlchemy
```python
import sqlalchemy as sa
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy_mixins import BaseMixin
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
db = SQLAlchemy(app)
######### Models #########
class BaseModel(db.Model, BaseMixin):
__abstract__ = True
pass
class User(BaseModel):
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:
```python
session = scoped_session(sessionmaker(bind=engine, autocommit=True))
BaseModel.set_session(session)
```
or with `Flask-SQLAlchemy`
```python
db = SQLAlchemy(app, session_options={'autocommit': True})
```
# Features
Main features are
* [Active Record](#active-record)
* [Eager Load](#eager-load)
* [Django-like queries](#django-like-queries)
* [Beauty \_\_repr\_\_](#beauty-__repr__)
* [Timestamps](#timestamps)
## Active Record
provided by [`ActiveRecordMixin`](sqlalchemy_mixins/active_record.py)
SQLAlchemy's [Data Mapper](https://en.wikipedia.org/wiki/Data_mapper_pattern)
pattern is cool, but
[Active Record](https://en.wikipedia.org/wiki/Active_record_pattern)
pattern is easiest and more [DRY](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself).
Well, we implemented it on top of Data Mapper!
All we need is to just inject [session](http://docs.sqlalchemy.org/en/latest/orm/session.html) into ORM class while bootstrapping our app:
```python
BaseModel.set_session(session)
# now we have access to BaseOrmModel.session property
```
### CRUD
We all love SQLAlchemy, but doing [CRUD](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete)
is a bit tricky there.
For example, creating an object needs 3 lines of code:
```python
bob = User(name='Bobby', age=1)
session.add(bob)
session.flush()
```
Well, having access to session from model, we can just write
```python
bob = User.create(name='Bobby', age=1)
```
that's how it's done in [Django ORM](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#create)
and [Peewee](http://docs.peewee-orm.com/en/latest/peewee/querying.html#creating-a-new-record)
update and delete methods are provided as well
```python
bob.update(name='Bob', age=21)
bob.delete()
```
And, as in [Django](https://docs.djangoproject.com/en/1.10/topics/db/queries/#retrieving-a-single-object-with-get)
and [Eloquent](https://laravel.com/docs/5.4/eloquent#retrieving-single-models),
we can quickly retrieve object by id
```python
User.get(1) # instead of session.query(User).get(1)
```
and fail if such id doesn't exist
```python
User.get_or_abort(123987) # will raise sqlalchemy_mixins.ModelNotFoundError
```
### Querying
As in [Flask-SQLAlchemy](http://flask-sqlalchemy.pocoo.org/2.1/queries/#querying-records),
[Peewee](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.select)
and [Django ORM](https://docs.djangoproject.com/en/1.10/topics/db/queries/#retrieving-objects),
you can quickly query some class
```python
User.query # instead of session.query(User)
```
Also we can quickly retrieve first or all objects:
```python
User.first() # instead of session.query(User).first()
User.all() # instead of session.query(User).all()
```
## Eager load
provided by [`EagerLoadMixin`](sqlalchemy_mixins/eager_load.py)
### Nested eager load
If you use SQLAlchemy's [eager loading](http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#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
```python
User.with_({
'posts': {
'comments': {
'user': JOINED
}
}
}).all()
```
or we can write class properties instead of strings:
```python
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](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload).
For example, we load posts on page like [this](http://www.qopy.me/3V4Tsu_GTpCMJySzvVH1QQ),
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
```python
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](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html)
for explaining relationship loading techniques.
### Quick eager load
For simple cases, when you want to just
[joinedload](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload)
or [subqueryload](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload)
a few relations, we have easier syntax for you:
```python
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](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload_all)
## Filter and sort by relations
provided by [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py)
### Django-like queries
We implement Django-like
[field lookups](https://docs.djangoproject.com/en/1.10/topics/db/queries/#field-lookups)
and
[automatic relation joins](https://docs.djangoproject.com/en/1.10/topics/db/queries/#lookups-that-span-relationships).
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
```python
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)
> If you need more flexibility, you can use low-level `filter_expr` method `session.query(Post).filter(*Post.filter_expr(rating__gt=2, body='text'))`, [see example](examples/smartquery.py#L232).
>
> It's like [`filter_by` in SQLALchemy](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.filter_by), but also allows magic operators like `rating__gt`.
>
> Note: `filter_expr` method is very low-level and does NOT do magic Django-like joins. Use [`smart_query`](#all-in-one-smart_query) for that.
> **All relations used in filtering/sorting should be _explicitly set_, not just being a backref**
>
> In our example, `Post.user` relationship should be defined in `Post` class even if `User.posts` is defined too.
>
> So, you can't type
> ```python
> class User(BaseModel):
> # ...
> user = sa.orm.relationship('User', backref='posts')
> ```
> and skip defining `Post.user` relationship. You must define it anyway:
>
> ```python
> 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](http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html)
and [hybrid_methods](http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html?highlight=hybrid_method#sqlalchemy.ext.hybrid.hybrid_method).
Using them in our filtering/sorting is straightforward (see examples and tests).
### Automatic eager load relations
Well, as [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py) 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
```python
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
```python
comments[0].post
comments[0].post.user
```
### All-in-one: smart_query
#### Filter, sort and eager load in one smartest method.
provided by [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py)
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:
```python
Comment.smart_query(
filters={
'post___public': True,
'user__isnull': False
},
sort_attrs=['user___name', '-created_at'],
schema={
'post': {
'user': JOINED
}
}).all()
```
As developers, we need to debug things with convenience.
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:
```python
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'>]
```
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'>>
```
## DateMixin
provided by [`DateMixin`](sqlalchemy_mixins/date_mixin.py)
You can view the created and updated timestamps.
```python
bob = User(name="Bob")
session.add(bob)
session.flush()
print("Created Bob: ", bob.created_at)
# Created Bob: 2019-03-04 03:53:53.606765
print("Pre-update Bob: ", bob.updated_at)
# Pre-update Bob: 2019-03-04 03:53:53.606769
time.sleep(2)
bob.name = "Robert"
session.commit()
print("Updated Bob: ", bob.updated_at)
# Updated Bob: 2019-03-04 03:53:58.613044
```
Raw data
{
"_id": null,
"home_page": "https://github.com/blueromans/SqlalchemyMixin.git",
"name": "SqlalchemyMixin",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "sqlalchemy,active record,activerecord,orm,django-like,django,eager load,eagerload,repr,__repr__,mysql,postgresql,pymysql,sqlite",
"author": "Ya\u015far \u00d6zyurt",
"author_email": "blueromans@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/b5/65/fb17c8358a4920d8b28c769ce020c80544a34a56b5c785427a8bf46e8f94/SqlalchemyMixin-0.0.39.tar.gz",
"platform": null,
"description": "[](https://pypi.python.org/pypi/SqlalchemyMixin)\n# SQLAlchemy Mixin\n\nA pack of framework-agnostic, easy-to-integrate for SQLAlchemy ORM.\n\nHeavily inspired by [Django ORM](https://docs.djangoproject.com/en/1.10/topics/db/queries/)\nand [Eloquent ORM](https://laravel.com/docs/5.4/eloquent)\n\neasy integration to your existing project like [FastApi](https://fastapi.tiangolo.com):\n```python\nfrom sqlalchemy_mixins import BaseMixin\n\nclass User(Base, BaseMixin):\n pass\n```\n\n## Table of Contents\n\n1. [Installation](#installation)\n1. [Quick Start](#quick-start)\n 1. [Framework-agnostic](#framework-agnostic)\n 1. [Usage with Flask-SQLAlchemy](#usage-with-flask-sqlalchemy)\n1. [Features](#features)\n 1. [Active Record](#active-record)\n 1. [CRUD](#crud)\n 1. [Querying](#querying)\n 1. [Eager Load](#eager-load)\n 1. [Django-like queries](#django-like-queries)\n 1. [Filter and sort by relations](#filter-and-sort-by-relations)\n 1. [Automatic eager load relations](#automatic-eager-load-relations)\n 1. [All-in-one: smart_query](#all-in-one-smart_query)\n 1. [Beauty \\_\\_repr\\_\\_](#beauty-__repr__)\n 1. [DateMixin](#timestamps)\n\n## Installation\n\nUse pip\n```\npip install SqlalchemyMixin\n```\n\n## Quick Start\n\n### Framework-agnostic\nHere's a quick demo of what our mixins can do.\n\n```python\nbob = User.create(name='Bob')\npost1 = Post.create(body='Post 1', user=bob, rating=3)\npost2 = Post.create(body='long-long-long-long-long body', rating=2,\n user=User.create(name='Bill'),\n comments=[Comment.create(body='cool!', user=bob)])\n\n# filter using operators like 'in' and 'contains' and relations like 'user'\n# will output this beauty: <Post #1 body:'Post1' user:'Bill'>\nprint(Post.where(rating__in=[2, 3, 4], user___name__like='%Bi%').all())\n# joinedload post and user\nprint(Comment.with_joined('user', 'post', 'post.comments').first())\n# subqueryload posts and their comments\nprint(User.with_subquery('posts', 'posts.comments').first())\n# sort by rating DESC, user name ASC\nprint(Post.sort('-rating', 'user___name').all())\n# created_at, updated_at timestamps added automatically\nprint(\"Created Bob at \", bob.created_at) \n# serialize to dict, with relationships\n```\n\n\n\n### Usage with Flask-SQLAlchemy\n\n```python\nimport sqlalchemy as sa\nfrom flask import Flask\nfrom flask_sqlalchemy import SQLAlchemy\nfrom sqlalchemy_mixins import BaseMixin\n\napp = Flask(__name__)\napp.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'\ndb = SQLAlchemy(app)\n\n######### Models ######### \nclass BaseModel(db.Model, BaseMixin):\n __abstract__ = True\n pass\n\n\nclass User(BaseModel):\n name = sa.Column(sa.String)\n\n######## Initialize ########\nBaseModel.set_session(db.session)\n\n######## Create test entity ########\ndb.create_all()\nuser = User.create(name='bob')\nprint(user)\n```\n\n# *** Autocommit ***\nThis library relies on SQLAlchemy's `autocommit` flag. It needs to be set to True when initializing the session i.e:\n```python\nsession = scoped_session(sessionmaker(bind=engine, autocommit=True))\nBaseModel.set_session(session)\n```\nor with `Flask-SQLAlchemy`\n```python\ndb = SQLAlchemy(app, session_options={'autocommit': True})\n```\n\n# Features\n\nMain features are\n * [Active Record](#active-record)\n * [Eager Load](#eager-load)\n * [Django-like queries](#django-like-queries)\n * [Beauty \\_\\_repr\\_\\_](#beauty-__repr__)\n * [Timestamps](#timestamps)\n\n## Active Record\nprovided by [`ActiveRecordMixin`](sqlalchemy_mixins/active_record.py)\n\nSQLAlchemy's [Data Mapper](https://en.wikipedia.org/wiki/Data_mapper_pattern)\npattern is cool, but\n[Active Record](https://en.wikipedia.org/wiki/Active_record_pattern)\npattern is easiest and more [DRY](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself).\n\nWell, we implemented it on top of Data Mapper!\nAll we need is to just inject [session](http://docs.sqlalchemy.org/en/latest/orm/session.html) into ORM class while bootstrapping our app:\n\n```python\nBaseModel.set_session(session)\n# now we have access to BaseOrmModel.session property\n```\n\n### CRUD\nWe all love SQLAlchemy, but doing [CRUD](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete)\nis a bit tricky there.\n\nFor example, creating an object needs 3 lines of code:\n```python\nbob = User(name='Bobby', age=1)\nsession.add(bob)\nsession.flush()\n```\n\nWell, having access to session from model, we can just write\n```python\nbob = User.create(name='Bobby', age=1)\n```\nthat's how it's done in [Django ORM](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#create)\nand [Peewee](http://docs.peewee-orm.com/en/latest/peewee/querying.html#creating-a-new-record)\n\nupdate and delete methods are provided as well\n```python\nbob.update(name='Bob', age=21)\nbob.delete()\n```\n\nAnd, as in [Django](https://docs.djangoproject.com/en/1.10/topics/db/queries/#retrieving-a-single-object-with-get)\nand [Eloquent](https://laravel.com/docs/5.4/eloquent#retrieving-single-models),\nwe can quickly retrieve object by id\n```python\nUser.get(1) # instead of session.query(User).get(1)\n```\n\nand fail if such id doesn't exist\n```python\nUser.get_or_abort(123987) # will raise sqlalchemy_mixins.ModelNotFoundError\n```\n\n\n### Querying\nAs in [Flask-SQLAlchemy](http://flask-sqlalchemy.pocoo.org/2.1/queries/#querying-records),\n[Peewee](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.select)\nand [Django ORM](https://docs.djangoproject.com/en/1.10/topics/db/queries/#retrieving-objects),\nyou can quickly query some class\n```python\nUser.query # instead of session.query(User)\n```\n\nAlso we can quickly retrieve first or all objects:\n```python\nUser.first() # instead of session.query(User).first()\nUser.all() # instead of session.query(User).all()\n```\n\n## Eager load\nprovided by [`EagerLoadMixin`](sqlalchemy_mixins/eager_load.py)\n\n### Nested eager load\nIf you use SQLAlchemy's [eager loading](http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#eager-loading),\nyou may find it not very convenient, especially when we want, say,\nload user, all his posts and comments to every his post in the same query.\n\nWell, now you can easily set what ORM relations you want to eager load\n```python\nUser.with_({\n 'posts': {\n 'comments': {\n 'user': JOINED\n }\n }\n}).all()\n```\n\nor we can write class properties instead of strings:\n```python\nUser.with_({\n User.posts: {\n Post.comments: {\n Comment.user: JOINED\n }\n }\n}).all()\n```\n\n### Subquery load\nSometimes we want to load relations in separate query, i.e. do [subqueryload](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload).\nFor example, we load posts on page like [this](http://www.qopy.me/3V4Tsu_GTpCMJySzvVH1QQ),\nand for each post we want to have user and all comments (and comment authors).\n\nTo speed up query, we load comments in separate query, but, in this separate query, join user\n```python\nfrom sqlalchemy_mixins import JOINED, SUBQUERY\nPost.with_({\n 'user': JOINED, # joinedload user\n 'comments': (SUBQUERY, { # load comments in separate query\n 'user': JOINED # but, in this separate query, join user\n })\n}).all()\n```\n\nHere, posts will be loaded on first query, and comments with users - in second one.\nSee [SQLAlchemy docs](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html)\nfor explaining relationship loading techniques.\n\n### Quick eager load\nFor simple cases, when you want to just \n[joinedload](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.joinedload)\nor [subqueryload](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload) \na few relations, we have easier syntax for you:\n\n```python\nComment.with_joined('user', 'post', 'post.comments').first()\nUser.with_subquery('posts', 'posts.comments').all()\n```\n\n> Note that you can split relations with dot like `post.comments`\n> due to [this SQLAlchemy feature](http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.subqueryload_all)\n\n\n## Filter and sort by relations\nprovided by [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py)\n\n### Django-like queries\nWe implement Django-like\n[field lookups](https://docs.djangoproject.com/en/1.10/topics/db/queries/#field-lookups)\nand\n[automatic relation joins](https://docs.djangoproject.com/en/1.10/topics/db/queries/#lookups-that-span-relationships).\n\nIt means you can **filter and sort dynamically by attributes defined in strings!**\n\nSo, having defined `Post` model with `Post.user` relationship to `User` model,\nyou can write\n```python\nPost.where(rating__gt=2, user___name__like='%Bi%').all() # post rating > 2 and post user name like ...\nPost.sort('-rating', 'user___name').all() # sort by rating DESC, user name ASC\n```\n(`___` splits relation and attribute, `__` splits attribute and operator)\n\n> If you need more flexibility, you can use low-level `filter_expr` method `session.query(Post).filter(*Post.filter_expr(rating__gt=2, body='text'))`, [see example](examples/smartquery.py#L232).\n>\n> It's like [`filter_by` in SQLALchemy](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.filter_by), but also allows magic operators like `rating__gt`.\n>\n> Note: `filter_expr` method is very low-level and does NOT do magic Django-like joins. Use [`smart_query`](#all-in-one-smart_query) for that.\n\n> **All relations used in filtering/sorting should be _explicitly set_, not just being a backref**\n>\n> In our example, `Post.user` relationship should be defined in `Post` class even if `User.posts` is defined too.\n>\n> So, you can't type\n> ```python\n> class User(BaseModel):\n> # ...\n> user = sa.orm.relationship('User', backref='posts')\n> ```\n> and skip defining `Post.user` relationship. You must define it anyway:\n>\n> ```python\n> class Post(BaseModel):\n> # ...\n> user = sa.orm.relationship('User') # define it anyway\n> ```\n\nFor DRY-ifying your code and incapsulating business logic, you can use\nSQLAlchemy's [hybrid attributes](http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html)\nand [hybrid_methods](http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html?highlight=hybrid_method#sqlalchemy.ext.hybrid.hybrid_method).\nUsing them in our filtering/sorting is straightforward (see examples and tests).\n\n### Automatic eager load relations\nWell, as [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py) does auto-joins for filtering/sorting,\nthere's a sense to tell sqlalchemy that we already joined that relation.\n\nSo that relations are automatically set to be joinedload if they were used for filtering/sorting.\n\n\nSo, if we write\n```python\ncomments = Comment.where(post___public=True, post___user___name__like='Bi%').all()\n```\nthen no additional query will be executed if we will access used relations\n```python\ncomments[0].post\ncomments[0].post.user\n```\n\n### All-in-one: smart_query\n#### Filter, sort and eager load in one smartest method.\nprovided by [`SmartQueryMixin`](sqlalchemy_mixins/smart_query.py)\n\nIn real world, we want to filter, sort and also eager load some relations at once.\nWell, if we use the same, say, `User.posts` relation in filtering and sorting,\nit **should not be joined twice**.\n\nThat's why we combined filter, sort and eager load in one smartest method:\n```python\nComment.smart_query(\n filters={\n 'post___public': True,\n 'user__isnull': False\n },\n sort_attrs=['user___name', '-created_at'],\n schema={\n 'post': {\n 'user': JOINED\n }\n }).all()\n```\n\n\n\nAs developers, we need to debug things with convenience.\nWhen we play in REPL, we can see this\n\n```\n>>> session.query(Post).all()\n[<myapp.models.Post object at 0x04287A50>, <myapp.models.Post object at 0x04287A90>]\n```\n\nWell, using our mixin, we can have more readable output with post IDs:\n\n```\n>>> session.query(Post).all()\n[<Post #11>, <Post #12>]\n```\n\nEven more, in `Post` model, we can define what else (except id) we want to see:\n\n```python\nclass User(BaseModel):\n __repr_attrs__ = ['name']\n # ...\n\n\nclass Post(BaseModel):\n __repr_attrs__ = ['user', 'body'] # body is just column, user is relationship\n # ...\n\n```\n\nNow we have\n```\n>>> session.query(Post).all()\n[<Post #11 user:<User #1 'Bill'> body:'post 11'>,\n <Post #12 user:<User #2 'Bob'> body:'post 12'>]\n\n```\n\nAnd you can customize max `__repr__` length:\n```\nclass Post(BaseModel):\n # ...\n __repr_max_length__ = 25\n # ...\n \n>>> long_post\n<Post #2 body:'Post 2 long-long body' user:<User #1 'Bob'>> \n```\n\n\n\n## DateMixin\nprovided by [`DateMixin`](sqlalchemy_mixins/date_mixin.py)\n\nYou can view the created and updated timestamps.\n\n```python\nbob = User(name=\"Bob\")\nsession.add(bob)\nsession.flush()\n\nprint(\"Created Bob: \", bob.created_at)\n# Created Bob: 2019-03-04 03:53:53.606765\n\nprint(\"Pre-update Bob: \", bob.updated_at)\n# Pre-update Bob: 2019-03-04 03:53:53.606769\n\ntime.sleep(2)\n\nbob.name = \"Robert\"\nsession.commit()\n\nprint(\"Updated Bob: \", bob.updated_at)\n# Updated Bob: 2019-03-04 03:53:58.613044\n```\n",
"bugtrack_url": null,
"license": "",
"summary": "Active Record, Django-like queries, nested eager load and beauty __repr__ for SQLAlchemy",
"version": "0.0.39",
"project_urls": {
"Bug Tracker": "https://github.com/blueromans/SqlalchemyMixin/issues",
"Homepage": "https://github.com/blueromans/SqlalchemyMixin.git"
},
"split_keywords": [
"sqlalchemy",
"active record",
"activerecord",
"orm",
"django-like",
"django",
"eager load",
"eagerload",
"repr",
"__repr__",
"mysql",
"postgresql",
"pymysql",
"sqlite"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "bfc21650944ec52044d121b5f047d5800cb7c4b4288729a941b7c95460056173",
"md5": "4c78ce22b0bd65743e55217aaec68934",
"sha256": "6d507143e421ac458c00ac1df84a2a08b4d7673a358ab66760a4352a8db48ace"
},
"downloads": -1,
"filename": "SqlalchemyMixin-0.0.39-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4c78ce22b0bd65743e55217aaec68934",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.6",
"size": 23344,
"upload_time": "2023-08-02T14:16:22",
"upload_time_iso_8601": "2023-08-02T14:16:22.209225Z",
"url": "https://files.pythonhosted.org/packages/bf/c2/1650944ec52044d121b5f047d5800cb7c4b4288729a941b7c95460056173/SqlalchemyMixin-0.0.39-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "b565fb17c8358a4920d8b28c769ce020c80544a34a56b5c785427a8bf46e8f94",
"md5": "9b18b4da94774d5127f17fb8bb1d007a",
"sha256": "4fb7e3d22670a3b1e653be4f0106d91604ae7b234683c42b3604e4a6c51d74a3"
},
"downloads": -1,
"filename": "SqlalchemyMixin-0.0.39.tar.gz",
"has_sig": false,
"md5_digest": "9b18b4da94774d5127f17fb8bb1d007a",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 19378,
"upload_time": "2023-08-02T14:16:24",
"upload_time_iso_8601": "2023-08-02T14:16:24.288482Z",
"url": "https://files.pythonhosted.org/packages/b5/65/fb17c8358a4920d8b28c769ce020c80544a34a56b5c785427a8bf46e8f94/SqlalchemyMixin-0.0.39.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-02 14:16:24",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "blueromans",
"github_project": "SqlalchemyMixin",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "sqlalchemymixin"
}
JSON
