absent1706/sqlalchemy-mixins

Active Record, Django-like queries, nested eager load and beauty __repr__ for SQLAlchemy

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!

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)

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.

It's like filter_by in SQLALchemy, 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 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

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

Automatic eager load relations

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

All-in-one: smart_query

Filter, sort and eager load in one smartest method.

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={
        Comment.post: {
            Post.user: JOINED
        }
    }).all()

** New in 0.2.3 ** In real world, you may need to "smartly" apply filters/sort/eagerload to any arbitrary query. And you can do this with standalone smart_query function:

smart_query(any_query, filters=...)

It's especially useful for filtering/sorting/eagerloading relations with lazy='dynamic' for pages like this:

smart_query(user.comments_, filters=...)

See this example

** Experimental ** Additional logic (OR, AND, NOT etc) can be expressed using a nested structure for filters, with sqlalchemy operators (or any callable) as keys:

from sqlalchemy import or_
Comment.smart_query(filters={ or_: {
    'post___public': True, 
    'user__isnull': False
}})

See this example for more details

See full example and tests

Beauty __repr__

provided by ReprMixin

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:

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 cut:

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

Serialize to dict

provided by SerializeMixin

You can convert your model to dict.

# 1. Without relationships
#
# {'id': 1, 'name': 'Bob'}
print(user.to_dict())

# 2. With relationships
#
# {'id': 1,
# 'name': 'Bob',
# 'posts': [{'body': 'Post 1', 'id': 1, 'user_id': 1},
#           {'body': 'Post 2', 'id': 2, 'user_id': 1}]}
print(user.to_dict(nested=True))

See full example

Timestamps

provided by TimestampsMixin

You can view the created and updated timestamps.

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

See full example

Internal architecture notes

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.

Comparison with existing solutions

There're a lot of extensions for SQLAlchemy, but most of them are not so universal.

Active record

We found several implementations of this pattern.

ActiveAlchemy

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.

Flask-ActiveRecord

Cool, but tightly coupled with Flask.

sqlalchemy-activerecord

Framework-agnostic, but lacks of functionality (only save method is provided) and Readme.

Django-like queries

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

Beauty __repr__

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.

Changelog

v0.2

More clear methods in EagerLoadMixin:

• added with_subquery method: it's like with_joined, but for subqueryload. So you can now write:

  User.with_subquery('posts', 'comments').all()

• with_joined method arguments change: instead of

  Comment.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, use with_joined

• with_dict method removed. Instead, use with_ method

Other changes in EagerLoadMixin:

• constants rename: use cleaner JOINED and SUBQUERY instead of JOINEDLOAD and SUBQUERYLOAD

• do not allow None in schema anymore, so instead of

  Comment.with_({'user': None})

  write

  Comment.with_({'user': JOINED})

v0.2.1

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'] 

v0.2.2

Fixed bug in ReprMixin: it crashed for objects without ID (newly created ones, not added yet to the session).

v0.2.3

SmartQueryMixin: decoupled smart_query function from ORM classes so now you can use it with any query like

smart_query(any_query, filters=...)

See description (at the end of paragraph) and example

v1.0.1

1. Added SerializationMixin (thanks, jonatasleon)

2. Added ne operator (thanks, https://github.com/sophalch), so now you can write something like

Post.where(rating__ne=2).all()

v1.2

This version contains breaking change, reverted in v1.2.1. So:

• v1.2 was removed from PyPi to avoid confusions
• for those who already downloaded v1.2, we hardly recommend to switch to 1.2.1.

Just use v1.2.1 instead

By mistake, v1.2 code was released on PyPi as v1.1. It has been deleted from PyPi to avoid confusion. Sorry for any inconvenience guys.

1. Removed Python 2, Python 3.2 compatibility.

2. Added Python 3.7, 3.8 compatibility.

3. Added TimestampsMixin (thanks, jonatasleon).

4. (Breaking change, fixed in v1.2.1) TimestampsMixin was included it to AllFeaturesMixin which means created_at and updated_at fields were added to all models using AllFeaturesMixin which means you need to write migrations adding these fields.

5. Added contains operator (thanks, alexbredo).

6. Added date comparison operators (thanks, proteusvacuum), so now you can write something like

Post.where(created_at__year_ge=2014).all()
Post.where(created_at__month_gt=10).all()
Post.where(created_at__day_le=30).all()

v1.2.1

Reverted breaking change introduced in 1.2:

removed TimestampsMixin from AllFeaturesMixin. This addition in v1.2 forced package users to write and run migration to add created_at and updated_at fields to all tables whose ORM models used AllFeaturesMixin. Now you should add TimestampsMixin separately:

class BaseModel(Base, AllFeaturesMixin, TimestampsMixin):
    # ...

v1.3

Add support for SQLAlchemy 1.4

v2.0.0

This version contains breaking changes in multiple methods i.e methods that simplify eager loading. The use of strings while eager loading has been removed completely in SQLAlchemy 2.0. To much this behaviour, we have also removed the use of strings when eager loading

1. Migrate to SQLAlchemy 2.0
2. All methods in the EagerLoadMixin no longer accept strings. Note This means that you can only pass direct relationships.
3. The schema parameter of the smart_query method/function no longer accepts string keys.
4. Dropped Python 3.6
5. Add Python 3.10 compatibility

sqlalchemy-mixins's Issues

Inspection broken when used with sqlalchemy.ext.declarative.declarative_base

I tried to get help on a class that inherits from AllFeaturesMixin as well as some other classes. I've been able to reduce it to this small failing case:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_mixins import AllFeaturesMixin

Base = declarative_base()

class Thing(Base, AllFeaturesMixin):
    __tablename__ = 'things'
    id = Column(Integer, primary_key=True)

help(Thing)

This results in the following failure:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/_sitebuiltins.py", line 103, in __call__
    return pydoc.help(*args, **kwds)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 1895, in __call__
    self.help(request)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 1954, in help
    else: doc(request, 'Help on %s:', output=self._output)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 1674, in doc
    pager(render_doc(thing, title, forceload))
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 1667, in render_doc
    return title % desc + '\n\n' + renderer.document(object, name)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 386, in document
    if inspect.isclass(object): return self.docclass(*args)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 1312, in docclass
    for name, kind, cls, value in classify_class_attrs(object)
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/pydoc.py", line 209, in classify_class_attrs
    for (name, kind, cls, value) in inspect.classify_class_attrs(object):
  File "/usr/local/Cellar/python/3.7.0/Frameworks/Python.framework/Versions/3.7/lib/python3.7/inspect.py", line 434, in classify_class_attrs
    srch_obj = getattr(srch_cls, name, None)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_mixins/utils.py", line 12, in __get__
    return self.fget(owner_cls)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_mixins/inspection.py", line 17, in columns
    return inspect(cls).columns.keys()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/inspection.py", line 75, in inspect
    type_)
sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>

SqlAlchemy 1.4 deprecation warning

Hi

With SqlAlchemy 1.4 it's possible to test if the code will work with future SqlAlchemy 2.0

https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#migration-to-2-0-step-two-turn-on-removedin20warnings

using this we receive some deprecation warnings from sqlalchemy-mixins

sqlalchemy==1.4.23
sqlalchemy-mixins==1.5

Releases not appearing in GitHub?

Great to see the activity here. pip list --outdated shows that I can upgrade from 0.2.3 to 1.1. However, https://github.com/absent1706/sqlalchemy-mixins/releases doesn't show that (although the Tags tab does).

REFACTOR: maybe, get rid of InspectionMixin.settable_attributes, or check it optionally

Idea: OrderableMixin

Hey there!

I sometimes need to make object comparable to each other and define a custom order. For example some records should be orderable by an enduser.

Up to now I always wrote some custom logic for every project. Now I decided to make it a bit more abstract and universal.

I thought about something like this:

from sqlalchemy import func, Column, Integer
from sqlalchemy.engine.default import DefaultExecutionContext
from sqlalchemy.sql.expression import select
from sqlalchemy.sql.selectable import Select
from sqlalchemy.engine.base import Engine
from sqlalchemy.engine.result import RowProxy


def default_order_index(context: DefaultExecutionContext) -> int:
    """
    Get the current highest index for each model
    """
    if context:
        engine: Engine = context.engine
        try:
            query: Select = select([func.max(1, func.max(context.current_column.table.c.order_index) + 1)])
            r: RowProxy = engine.execute(query).fetchone()
            i: int = int(r[0])
            return i
        except (TypeError, IndexError):
            return 0
    else:
        return 0


class OrderableMixin(object):
    """
    Mixin to make database models comparable.
    --
    The highest object has the lowest index (e.g. ZERO (0))

    The lowest object has the highest index (.e.g INF (9999999999))

    """

    order_index = Column(Integer,
                         default=default_order_index,
                         index=True)

    @classmethod
    def normalize(cls) -> None:
        """ Normalize all order indexes """

        for idx, item in enumerate(cls.query.order_by(cls.order_index).all()):
            item.order_index = idx

    def move_up(self) -> None:
        """ Move the database object one up -> decreases index by one"""
        if self.order_index == 0:
            return

        # get all items ordered by their index
        items = self.query.order_by(self.__class__.order_index).all()
        idx = items.index(self)

        # swap with item above
        above = items[idx - 1]
        above.order_index, self.order_index = idx, above.order_index

    def move_down(self) -> None:
        """ Move the database object one down -> increases index by on"""

        # get all items ordered by their index
        items = self.query.order_by(self.__class__.order_index).all()
        idx = items.index(self)

        # if item is last do nothing
        if idx == len(items) - 1:
            return

        # swap with item below
        below = items[idx + 1]
        below.order_index, self.order_index = idx, below.order_index

This could then be used as follows:

Base = declarative_base()


class BaseModel(Base, SessionMixin):
    __abstract__ = True


class SampleModel(BaseModel, OrderableMixin):
    __tablename__ = "samples"

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(255))

obj = SampleModel()

obj.move_up()
obj.move_down()

What do you guys think about that?

could Pagination be added？

I love sqlalchemy-mixins as a user of Laravel.

Hope it can add Pagination like

• sqlakeyset
• active-alchemy
• paginate_sqlalchemy
• SQLA-Wrapper

Missing attributes in SerializeMixin

It seems that the SerializeMixin is only serializing the columns and relationships in its to_dict method. This misses attributes defined on the model such as Hybrid Attributes.
A reference implementation that handles it can be found here: https://wakatime.com/blog/32-flask-part-1-sqlalchemy-models-to-json

Unfortunately in order to not miss anything, one has to use dir() and remove all the properties that are already accounted for in the Columns and Relationships, as well as dropping everything starting with an underscore.

Other than that, I really like your Mixin library and plan to use it in my upcoming project :)
Thanks!

Duplicate bases fo classs

I found your lib from Internet,
Im testing with some your example .
all_features.py
I got some warning (in vscode with pylint). Is that normaly ?

And in sqlalchemy, i used init for set some default value like that

 class User(BaseModel):
    __tablename__ = 'users'
    id = Column(String(80), primary_key=True)
    username = Column(String(80))
    email = Column(String(80))
    password = Column(String(80))
    access_token = Column(Text)
    create_time = Column(DateTime)
    update_time = Column(DateTime)
    status = Column(Integer)

    def __init__(self, id, username, email, password, access_token=None, create_time=None, update_time=None, status=1):
        self.id = id
        self.username = username
        self.email = email
        self.password = password
        self.access_token = access_token
        if create_time is None:
            create_time = datetime.utcnow()
        if update_time is None:
            update_time = datetime.utcnow()
        self.create_time = create_time
        self.update_time = update_time
        self.status = status

    def __repr__(self):
        return '<Username %r>' % self.username

So in sqlalchemy-mixins, can i use init ? And how to use.
I call fun User.create() and got error :

[2019-07-18 12:44:52,157] ERROR in app: Exception on /api/auth/register [POST]
Traceback (most recent call last):
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/flask/app.py", line 1832, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/flask/app.py", line 1818, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/flask_restplus/api.py", line 325, in wrapper
    resp = resource(*args, **kwargs)
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/flask/views.py", line 88, in view
    return self.dispatch_request(*args, **kwargs)
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/flask_restplus/resource.py", line 44, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/home/datpt/Documents/github/malbot/apis/auth/auth.py", line 61, in post
    User.create(username=data['username'],email=data['email'],password=sha256.hash(data['password']))
  File "/home/datpt/Documents/github/malbot/env/lib/python3.6/site-packages/sqlalchemy_mixins/activerecord.py", line 39, in create
    return cls().fill(**kwargs).save()
TypeError: __init__() missing 4 required positional arguments: 'id', 'username', 'email', and 'password'
10.15.154.23 - - [18/Jul/2019 12:44:52] "POST /api/auth/register HTTP/1.1" 500 -

Feature request: SerializeMixin - Add `to_json()` with support for custom JSON encoding

Types like UUID or custom types from libraries like sqlalchemy-utils cannot be serialized to JSON by default from a Python dict, making monkey-patching the default JSONEncoder or providing a custom encoder class common practice. It would be nice to add a to_json() method to the SerializeMixin alongside the provided to_dict() method, with the ability to pass in an encoder param to the function to serialize a model to JSON.

How to cover the original model by TimestampsMixin?

I have a table in MySQL like this

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Auto generate

pip install pymysql
pip install sqlacodegen

sqlacodegen mysql+pymysql://root:[email protected]:3306/test

Result

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)
    deleted_at = Column(DateTime)

And I combine it with sqlalchemy_mixins

main.py

from flask import Flask
from sqlalchemy_mixins import AllFeaturesMixin
from sqlalchemy_mixins.timestamp import TimestampsMixin
from sqlalchemy import Column, Integer, String, DateTime
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:[email protected]:3306/test'
db = SQLAlchemy(app, session_options={'autocommit': True})


class Base(db.Model, AllFeaturesMixin, TimestampsMixin):
    __abstract__ = True


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    created_at = Column(DateTime)
    updated_at = Column(DateTime)
    deleted_at = Column(DateTime)


Base.set_session(db.session)
db.create_all()

if __name__ == '__main__':
    user = User.create(name='Bob')
    print(user.to_dict())
    # {'id': 1, 'name': 'Bob', 'created_at': None, 'updated_at': None, 'deleted_at': None}

How to make created_at and updated_at are not None without deleting their statements?

Does BaseModel.set_session(session) only run once?

Or BaseModel.set_session(session) needs to run for every request?

Make users’ `hybrid_property` subclasses appear in `InspectionMixin.hybrid_properties`

Unless you have a specific, commented reason for doing so, you should never do those

# this is bad
type(obj) is SomeClass
# or, even worse
type(obj) == SomeClass

Instead, you should use

isinstance(obj, SomeClass)

Your use of the antipattern prevents users from having their subclasses of hybrid_property appear in InspectionMixin.hybrid_properties.

Datetime or Time Format

Serialization not working to field type DateTime or Time.

class StoreOpeningHour(BaseModel):
    __tablename__ = 'stores_opening_hours'

    id = Column(Integer, primary_key=True, autoincrement=True)
    day = Column(Integer, index=True)
    hour_open = Column(Time, index=True)
    hour_close = Column(Time, index=True)
    status = Column(Boolean, default=True)

    store_id = Column(Integer, ForeignKey('stores.id'))

Error:

Expect:

[ActiveRecordMixin] model not persisted unless I call db.session.commit()

When I call model.create(...), it isn't persisted in the database unless I call db.session.commit() after. What am I doing wrong?

contains_eager issues

The smart_query method is quite clever and uses contains_eager where it can to avoid duplicating joins. It does this by adding a contains_eager option for every entity it visits:

sqlalchemy-mixins/sqlalchemy_mixins/smartquery.py

Lines 112 to 117 in fdb0256

	loaded_paths = []
	for path, al in aliases.items():
	relationship_path = path.replace(RELATION_SPLITTER, '.')
	query = query.outerjoin(al[0], al[1]) \
	.options(contains_eager(relationship_path, alias=al[0]))
	loaded_paths.append(relationship_path)

Then when applying the options from the schema argument, it skips those which were already visited

sqlalchemy-mixins/sqlalchemy_mixins/smartquery.py

Lines 146 to 151 in fdb0256

	if schema:
	flat_schema = _flatten_schema(schema)
	not_loaded_part = {path: v for path, v in flat_schema.items()
	if path not in loaded_paths}
	query = query.options(*_eager_expr_from_flat_schema(
	not_loaded_part))

There are a couple of issues with this:

1. There's a disabled test (test_override_eagerload_method_in_schema) with a TODO comment. This checks that the following statement should execute 2 DB commands, but in fact it issues 1:

        res = Comment.smart_query(
            filters=dict(post___public=True, post___user___name__like='Bi%'),
            schema={
                'post': SUBQUERY
            }
        ).all()

That is - the SUBQUERY join specified in schema isn't being performed because the post table has already been visited and contains_eager has been applied.

For maybe 99% of cases the practical result is the same, but

2. Using contains_eager means you take responsibility for loading the contents of a collection, see
   https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#using-contains-eager-to-load-a-custom-filtered-collection-result

An example would be (based on the test-cases in test_smartquery):

res = User.smart_query(
            filters=dict(posts___archived=True)
        ).all()

This returns a single user (Bill u1) which is correct, and his posts relationship contains just the single archived post. There are actually 2 posts for this user in the database but by using contains_eager we have taken control of how the posts collection is loaded and only 1 is visible.

There's a bit of a question here about what the correct behaviour is, and there's a possibility that changing this will break things.

I think the optimal strategy would be something like

1. Track all visited entities/aliases which could be candidates for contains_eager option
2. If they don't have an explicit entry in the schema dict then call contains_eager, else do the thing requested in schema.

This looks like the behaviour that the disabled test was coded against, but if you have existing code it may return extra objects (which might be a security concern if, for example the filter is used to implement permissions on objects).

My quick workaround for this is to add another parameter to smart_query: separate_eager_load which defaults to False for current behaviour but if True simply skips any call to contains_eager - this breaks the automatic eager-loading detection so you have to fully specify schema.

Apologies for the long write-up - any thoughts on this much appreciated

Pass relationship values to update method

I have a Parent model and a Child model, the parent contains a relationship to its children.
My API should now be able to handle requests like this:

PUT <api_url>/parent/1
{ 
  "id": 1,
  "children": [
    {
      "id": 42,
      "name": "Foo"
    }
  ]
}

The backend will handle this request like this:

def update_parent(id: int):
  body: dict = request.get_json()
  parent: Parent = Parent.find(id)
  parent.update(**body)

However this does not work with the children attribute, since update expects a list of Child objects.
I can use something like marshmallow to deserialize the request body to a transient Parent object which correctly deserializes the relationship. Then the children attribute can be updated like this:

def update_parent(id: int):
  body: dict = request.get_json()
  transient_parent: Parent = ParentSchema().load(body)
  body.pop("children")
  parent: Parent = Parent.find(id)
  parent.update(**body, children=transient_parent.children)

While this works I don't want to implement this for every relationship I have.
How can this be achieved in a more general way?

Smart query on m2m backref fails

I have the following (simplified) model:

children_association = db.Table(
    'x_children',
    db.Column('parent_id', db.Integer, db.ForeignKey('x.id'), primary_key=True),
    db.Column('child_id', db.Integer, db.ForeignKey('x.id'), primary_key=True),
)

class X(BaseModel):
    id = db.Column(db.Integer, primary_key=True)
    slug = db.Column(db.String(256), index=True, unique=True, nullable=False, server_default='')
    
    children = db.relationship(
        'X',
        secondary=children_association,
        primaryjoin=id == children_association.c.parent_id,
        secondaryjoin=id == children_association.c.child_id,
        backref='parents',
    )

    def __repr__(self):
        return '<X %r>' % self.slug

But I can't query on parents (m2m backrefs), only on children:

X.where(children___slug='abc').first() # Works

X.where(parents___slug='abc').first() # Fails
Traceback (most recent call last):
   ... some unrelated stuff from my app
  File "./venv/lib/python3.8/site-packages/sqlalchemy_mixins/smartquery.py", line 343, in where
    return cls.smart_query(filters)
  File "./venv/lib/python3.8/site-packages/sqlalchemy_mixins/smartquery.py", line 327, in smart_query
    return smart_query(cls.query, filters, sort_attrs, schema)
  File "./venv/lib/python3.8/site-packages/sqlalchemy_mixins/smartquery.py", line 91, in smart_query
    _parse_path_and_make_aliases(root_cls, '', attrs, aliases)
  File "./venv/lib/python3.8/site-packages/sqlalchemy_mixins/smartquery.py", line 61, in _parse_path_and_make_aliases
    alias = aliased(relationship.property.argument())

Latest release not pushed to archive

Hi, trying to install this via pipenv which still get the v1.1. I see there have been upgrades since then but pip is not able to get them. Can you please update?

MySQL DB update

Need to add commit() statement to save() function in activerecord.py module:
def save(self):
"""Saves the updated model to the current entity db.
"""
self.session.add(self)
self.session.flush()
self.session.commit() # save in MySQL
return self

sqlalchemy-mixins incompatible with Flask-SQLAlchemy's custom model class recommendation.

This is a little convoluted to explain, but I've found that, sqlalchemy-mixin does not exactly work with Flask-SQLAlchemy as both are documented.

According to the Flask-SQLAlchemy docs, if you want to define your own base class, you pass it to SQLAlchemy(). For example:

from flask_sqlalchemy import Model


class MyModel(Model):
    @declared_attr
    def id(cls):
        return db.Column(db.Integer, primary_key=True, index=True)

db = SQLAlchemy(model_class=MyModel)

class Pet(db.Model):
    ...

Notice that you're inheriting from db.Model, not from MyModel. That's because, when I pass model_class to SQLAlchemy, it does some magic afterwards to actually add declarative_base() as a superclass and generates a final class that merges the two.

Why is this important, you ask? Consider this code block:

from flask_sqlalchemy import Model
from sqlalchemy_mixins import AllFeaturesMixin


class MyModel(Model. AllFeaturesMixin):
    @declared_attr
    def id(cls):
        return db.Column(db.Integer, primary_key=True, index=True)

db = SQLAlchemy(model_class=MyModel)

While I'm using AllFeaturesMixin and Flask-SQLAlchemy's Model class as recommended, I'll get an exception.

    return db.Column(db.Integer, primary_key=True, index=True)
NameError: name 'db' is not defined

The reason is that Flask-SQLAlchemy's SQLAlchemy() class does not expect a subclass from Base in Base = declarative_base(). It mixes in declarative base on its own.

That is to say, with straight Flask-SQLAlchemy, the part of the above code that resolves the declared_attr doesn't run until db is instanciated. But because sqlalchemy-mixins inherits directly from Base, its declared_attrs are resolved immediately and there's a NameError.

My general suspicion is that the solution is for sqlalchemy-mixins to perhaps be mixins, not subclasses of Base. But I'm not sure. At any rate, I thought I would bring up the issue for consideration. Thanks for the great library!

Limit and Offset

Is there a way to do limits and offsets in sql query mixins ?

FEATURE: add FakerMixin

Imperative mode

Is there the possibility of using this library in an imperative way?

Here is an example that show how I usually define my tables:

from sqlalchemy import Column, Integer, Table
from sqlalchemy.orm import registry

class MyTable:
    id: int

mapper_registry = registry()

table_name = 'my_table'

columns = [
    Column("id", Integer, primary_key=True),
]

table = Table(
    table_name,
    mapper_registry.metadata,
    *columns,
)

mapper_registry.map_imperatively(MyTable, table)

Changing the imperative mode is a bit difficult for me because of how the whole project is structured and because of its size. Each of the parts of this example are typically in separate files.

Any kind of help anyone can offer is a great light for me because I really want to incorporate the SmartQueryMixin 🙏.

Contributing code - extending filter logic

Hi,

I've been using sqlalchemy-mixins in a project recently and found it really useful. One thing I wanted to be able to do was to have more complex logic when filtering objects. To lift your example from the docs - show all posts that are either public or have no user.

I had a bit of a hack around and extended smart_query to take dictionaries of the form:

from sqlalchemy import or_
Comment.smart_query(filters={ or_: {
    'post___public': True, 
    'user__isnull': False
}})

This seems to work well, though it's a bit ugly to use functions as dictionary keys and it only works with smart_query, not where if the function is at the top level (function kwargs need to be strings)

Comment.where(**{or_: {...}})
TypeError...

I've got tests, docs etc. in my branch here: fredludlow/sqlalchemy-mixins@master...filter-operators
If you like it then I can make a PR. If you don't like it in its current state but can think of another good way to do this I'm all ears!

Project status?

Hi there

Does anyone know the project status? Is it dead?

In this case, did you guys found any similar project?

Thank you in advance

Specify different python versions in test_pr.yml action.

Currently, the workflow only runs in python 3.9 (see here We need to support more versions, maybe from 3.4. We will be dropping support for python3.3 as mentioned in #63

FEATURE: add TimestampsMixin

`ActiveRecord.create` missing hybrid properties when defined in a mixin

This might be a very edgy edge case, but it goes as follows:
I'm using sqlalchemy_utils.i18n translation_hybrid in my application to provide translations for some of the fields in my models. Since I use them quite often, I created Mixins that define them. Since the name of the columns is not defined in the mixin directly, it has to be defined via the @declared_attr decorator. This (I suspect) causes the name of the attribute to not be available in the settable_attributes property of the class (it only shows getter), which, in turn, causes create to fail when such an attribute is provided as kwarg.

My mixin sample code:

class NameMixin:
    """This mixin is used to add a translatable name column to a table."""
    name_tr = Column(MutableDict.as_mutable(JSONB))

    @declared_attr
    def name(cls):
        return translation_hybrid(cls.name_tr)

Now given a class:

class WithName(NameMixin, Base):
    id = Column(Integer, primary_key=True, index=True)

This will not work:

wn = WithName.create(name="Omri")

But this will work:

wn = WithName.create(name_tr={'en': 'Omri', 'de': 'Omri'})

And this is what printing settable_attributes gives me:

print(WithName.settable_attributes)
# result: ['id', 'name_tr', 'getter']

Hope this help - I don't know how many people my stumble upon this, but I thought it's better to report and perhaps find a nice solution for this...

EDIT:
Using wn = WithName(name='Omri'); session.add(wn); session.commit() does work!

Question: was v1.1 deleted from pypi?

Check for serializemixin instance in nested relations

For SerializeMixin, if nested = True and item in relations is one/many to many type (instance of Iterable), it's not checking whether related model has SerializeMixin enable or not. It's being check for one to one type relation.

[question] Could you please clarify how to use it with Flask-Sqlalchemy?

In Flask-Sqlalchemy I inherit my models from db.Model .
It's hard for me to understand how to write my models if I use sqlalchemy-mixins

Using scalars and func.count()

I want to peform a count of specific objects in the database. Currently i am doing it this way.
count = cls.where(patient_id__startswith='%P20%).count()

But how do i do it with sqlalcmeny's func.count() function. I tries the line below but it does not work

scalar = cls.query(func.count(Patient.uid)).filter(Patient.patient_id.like('P20')).scalar()

where my Patient model extends the allfeaturemixin

@as_declarative()
class DBModel(AllFeaturesMixin):
    __name__: str
    __abstract__ = True
    
    uid = Column(Integer, primary_key=True, index=True, nullable=False, autoincrement=True)
    
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()


class Patient(DBModel):
    client_patient_id = Column(String, index=True, unique=True, nullable=False)
    patient_id = Column(String, index=True, unique=True, nullable=True)
    first_name = Column(String, nullable=False)
    middle_name = Column(String, nullable=True)
    last_name = Column(String, nullable=False)
    active = Column(Boolean(), default=True)
    
    @property
    def full_name(self):
        if self.middle_name:
            return f"{self.first_name} {self.middle_name} {self.last_name}"
        else:
            return f"{self.first_name} {self.last_name}"

    @classmethod
    def create(cls, obj_in: schemas.PatientCreate) -> schemas.Patient:
        data = cls._import(obj_in)
        return super().create(**data)

    def update(self, obj_in: schemas.PatientUpdate) -> schemas.Patient:
        data = self._import(obj_in)
        return super().update(**data) 
        
    @classmethod
    def create_patient_id(cls):
        prefix_key = "P"
        prefix_year = str(datetime.now().year)[2:]
        prefix = f"{prefix_key}{prefix_year}"
        count = cls.where(patient_id__startswith=f'%{prefix}%').count()
        # Find a way to use the line below: its faster
        scalar = cls.session.query(func.count(Patient.uid)).filter(Patient.patient_id.like(prefix)).scalar()
        if isinstance(count, type(None)):
            count = 0
        return f"{prefix}-{sequencer(count + 1, 5)}"

basically what i am trying to achieve here is to create patients with unique patient_id that follow a defined custom string sequence. The way i am implementing this is as follows

patient_id = models.Patient.create_patient_id()
incoming["patient_id"] = patient_id
patient = models.Patient.create(**incoming)

Relationship exclude to serializate

I have run into the problem that, exclude to field relation, it does not do so.

First Model

class User(BaseModel):
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), index=True)
    shopping_carts = relationship(
        'ShoppingCart', uselist=True, back_populates='user'
    )

Second Model

class ShoppingCart(BaseModel):
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship(
        'User', uselist=False, back_populates='shopping_carts'
    )

Use smart query.

Error: sqlalchemy.exc.NoInspectionAvailable

Hi there,

thanks for this great package. I try to use your package version 0.2.2 with sqlalchemy version 1.1.10 But got some errors from the InspectionMixin. Could you help me with this. Thanks

Traceback (most recent call last):
  File "/Users/Pei/code/myproject/services/auth/auth_env/bin/nameko", line 11, in <module>
    sys.exit(main())
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/nameko/cli/main.py", line 69, in main
    args.main(args)
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/nameko/cli/commands.py", line 85, in main
    main(args)
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/nameko/cli/run.py", line 178, in main
    import_service(path)
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/nameko/cli/run.py", line 70, in import_service
    if inspect.getmembers(potential_service, is_entrypoint):
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/inspect.py", line 253, in getmembers
    value = getattr(object, key)
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/sqlalchemy_mixins/utils.py", line 12, in __get__
    return self.fget(owner_cls)
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/sqlalchemy_mixins/inspection.py", line 17, in columns
    return inspect(cls).columns.keys()
  File "/Users/Pei/code/myproject/services/auth/auth_env/lib/python2.7/site-packages/sqlalchemy/inspection.py", line 75, in inspect
    type_)
sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>

FEATURE: add SerializeMixin with to_dict() method

Improvements, and plans for the future.

@michaelbukachi, we should collect all suggestions/thoughts from the community, on which things we should focus on in the future, which features we should implement, and what type of improvements the community would like to see sqlalchemy-mixins.

Any comments/suggestions from the community are welcomed!

Smart querying performing multiple joins

I have the following db structure:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


class Author(BaseModel):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)


class Book(BaseModel):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    author_id = Column(Integer, ForeignKey('author.id'), nullable=False)

    author = relationship(Author, lazy='selectin')

And I was trying to do something like the following in django:

Book.query.filter(author__first_name='a').filter(author__last_name='b')

And I tried the following in smart_query, and found that it was doing 2 joins - while Django does just 1 join

>>> from sqlalchemy_mixins.smartquery import smart_query

>>> query = Book.query
>>> query = smart_query(query, filters={'author___name': 'a'})
>>> print(query)
SELECT * FROM book
LEFT OUTER JOIN author_ ON author.id = book.author_id
WHERE author.name = 'a'

>>> query = smart_query(query, filters={'author___name': 'b'})
>>> print(query)
SELECT * FROM book
LEFT OUTER JOIN author_1 ON author_1.id = book.author_id
LEFT OUTER JOIN author_2 ON author_2.id = book.author_id
WHERE author_1.first_name = 'a' AND author_2.last_name = 'b'

This seems like a major issue for me - because it doesn't allow me to chain my filters easily.

Am I missing something ? I was planning on adding smart_query to my BaseQuery in sqlalchemy and use that - but this makes it difficult for me to chain my filters

Allow escape characters in `like`/`ilike` queries (and use them in derived ones)

The like and ilike queries have an active character, %. It would be nice to be able to match that one literally if necessary.

Also queries like iendswith are implemented based on ilike. They should be implemented something like this:

def iendswith(col, value):
    escaped = value.replace('\\', r'\\').replace('%', r'\%')
	return col.ilike(f'%{escaped}', escape='\\')

When will next version be released? Current is 1.2.1

The SerializeMixin in version 1.2.1 does not include hybrid_attributes in to_dict().

I have cloned the repo and built from source, but I would prefer not to maintain that.

When will the next version be released?

Thanks. Carlos

Status of project

I came across this project recently and it looks like a great complement to SQL Alchemy. Looking at the current state of it though, I'm curious of the status of this project. Is there still active development being done or monitoring of the pull requests/issues. Before I commit to pulling this into my project I want to make sure it will have updates to support newer versions of SQL Alchemy. Thanks for all the hard work on this and look forward to hearing back.

Migrate documentation to the mkdocs

Description coming soon.

AttributeError: type object 'OtherModel' has no attribute 'class_'

Using smart_query(query=SomeModel.join(OtherModel)) produces an error:

AttributeError: type object 'OtherModel' has no attribute 'class_'

It's probably caused because of the use of _joinpoint_zero().

Please add synonyms to the list of "settable_attributes"

Hi there- not sure if this project is still active,

The sqlalchemy Synonym column names are not contained in the list of settable_attributes.

https://docs.sqlalchemy.org/en/14/orm/mapped_attributes.html#synonyms

There is a check here for determining if the updated field is in the list of settable_attributes, and synonyms should be a settable field, per documentation on sqlalchemy.

sqlalchemy-mixins/sqlalchemy_mixins/activerecord.py

Line 17 in f51e687

def fill(self, **kwargs):

I unfortunately was not able to find where this field is set on the sqlalchemy orm side. If someone can point me to where it is, I would be happy to open a PR.

Thanks!
Sara

Using existing database

I already have a database, I am using SQLAlchemy to interact with it. I have currently mapped database tables to SQLAlchemy objects as mentioned below.
Base = automap_base()
Material = Base.classes.app1_material
Customer = Base.classes.app1_customer

Can you share how to extend sqlalchemy-mixins to these classes?

Adapt to sqlalchemy 2.0?

As title

Smart query - ability to add custom data types

I've been using the SmartQueryMixin and really love how easy it is to add filters to my FastAPI based API based on this simple syntax. However I'm using the Ltree datatype from https://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/types/ltree.html and I'd like to be able to use some of the functionality from that in smart queries. Is there a way to extend the smart query syntax to support custom data types? I really like the way the mixin can traverse relationships automatically.

For this specific example I have a table with a relationship to another table that contains an ltree in a field called path. I'm wanting something like:

relationship_name___path__descendant_of: "1.2.3.5.7"

Can not be configured use .commit instead .flush

Now in project after Model.create(name="zxc") i need use db.session.commit().

SQLAlchemy 1.4 incompatible: AttributeError: 'Query' object has no attribute '_entity_zero'

smartquery fails with that error on this line:

sqlalchemy-mixins/sqlalchemy_mixins/smartquery.py

Line 87 in f51e687

root_cls = query._entity_zero().class_ # for example, User or Post

Remove travis

Since we've setup Github actions for testing PRs and deploying, we can now remove travis.

How can I use count function at smart query?

I want to get the posts which has less than 10 comments.
something like
Post.where(comments___count__lt=10).all()