Build Status Pythons

MongoSQL

MongoSQL is a JSON query engine that lets you query SqlAlchemy like a MongoDB database.

The main use case is the interation with the UI: every time the UI needs some sorting, filtering, pagination, or to load some related objects, you won't have to write a single line of repetitive code!

It will let the API user send a JSON Query Object along with the REST request, which will control the way the result set is generated:

$.get('/api/user?query=' + JSON.stringify({
    sort: ['first_name-'],  // sort by `first_name` DESC
    filter: { age: { $gte: 18 } },  // filter: age >= 18
    join: ['user_profile'],  // load related `user_profile`
    limit: 10,  // limit to 10 rows
}))

Tired of adding query parameters for pagination, filtering, sorting? Here is the ultimate solution.

NOTE: currently, only tested with PostgreSQL.

Table of Contents

Querying

If you know how to query documents in MongoDB, you can query your database with the same language. MongoSQL uses the familiar MongoDB Query Operators language with a few custom additions.

The Query Object, in JSON format, will let you sort, filter, paginate, and do other things. You would typically send this object in the URL query string, like this:

GET /api/user?query={"filter":{"age":{"$gte":18}}}

The name of the query argument, however, may differ from project to project.

Query Object Syntax

A Query Object is a JSON object that the API user can submit to the server to change the way the results are generated. It is an object with the following properties:

An example Query Object is:

{
  project: ['id', 'name'],  # Only fetch these columns
  sort: ['age+'],  # Sort by age, ascending
  filter: {
    # Filter condition
    sex: 'female',  # Girls
    age: { $gte: 18 },  # Age >= 18
  },
  join: ['user_profile'],  # Load the 'user_profile' relationship
  limit: 100,  # Display 100 per page
  skip: 10,  # Skip first 10 rows
}

Detailed syntax for every operation is provided in the relevant sections.

Please keep in mind that while MongoSQL provides a query language that is rich enough for most typical tasks, there would still be cases when an implementation of a custom API would be better, or even the only option available.

MongoSQL was not designed to be a complete replacement for the SQL; it was designed only to keep you from doing repetitive work :) So it's absolutely fine that some queries that you may have in mind won't be possible with MongoSQL.

Operations

Project Operation

Projection corresponds to the SELECT part of an SQL query.

In MongoDB terminology, projection is the process of selection a subset of fields from a document.

Your models have many fields, but you do not always need them all. Oftentimes, all you need is just a small number of them. That's when you use this operation that projects some fields for you.

The projéct operation lets you list the fields that you want to have in the data you get from the API endpoint. You do this by either listing the fields that you need (called include mode), or listing the fields that you do not need (called exclude mode).

The resulting data query on the back-end will only fetch the fields that you've requested, potentially saving a lot of bandwidth.

An example of a projection would look like this:

$.get('/api/user?query=' + JSON.stringify({
    // only include the following fields
    project: ['id', 'first_name', 'last_name'],
}))

Syntax

The Project operation supports the following syntaxes:

Fields Excluded by Default

Note that some fields that exist on the model may not be included by default: this is something that back-end developers may have configured with default_exclude setting on the server.

You will not receive those fields unless you explicitly require them. This may be appropriate for some field that contain a lot of data, or require some calculation.

To include those fields, you have to request them explicitly: just use their name in the list of fields that you request.

Related Models

Normally, in order to load a related model (say, user's user_profile, or some other data related to this model), you would use the Join Operation.

However, for convenience, you can now also load related models by just giving their name in the projection, as if it was a field. For example:

{ project: {
    id: 1,
    name: 1,
    user_articles: 1  // the related model will be loaded
}}

This request will load the related user_articles for you.

Note that some relationships will be disabled for security reasons.

Sort Operation

Sorting corresponds to the ORDER BY part of an SQL query.

The UI would normally require the records to be sorted by some field, or fields.

The sort operation lets the API user specify the sorting of the results, which makes sense for API endpoints that return a list of items.

An example of a sort operation would look like this:

$.get('/api/user?query=' + JSON.stringify({
    // sort by age, descending;
    // then sort by first name, alphabetically
    sort: ['age-', 'first_name+'],
}))

Syntax

Object syntax is not supported because it does not preserve the ordering of keys.

Filter Operation

Filtering corresponds to the WHERE part of an SQL query.

MongoSQL-powered API endpoints would typically return the list of all items, and leave it up to the API user to filter them the way they like.

Example of filtering:

$.get('/api/user?query=' + JSON.stringify({
    // only select grown-up females
    filter: {
        // all conditions are AND-ed together
        age: { $gte: 18, $lte: 25 },  // age 18..25
        sex: 'female',  // sex = "female"
    }
}))

Field Operators

The following MongoDB query operators operators are supported:

Supports the following MongoDB operators:

Supports the following operators on an ARRAY field, for a scalar value:

Supports the following operators on an ARRAY field, for an array value:

Boolean Operators

In addition to comparing fields to a value, the following boolean operators are supported that enable you to make complex queries:

Example usage:

$.get('/api/books?query=' + JSON.stringify({
    // either of the two options are fine
    $or: [
        // First option: sci-fi by Gardner Dozois
        { genre: 'sci-fi', editor: 'Gardner Dozois' },
        // Second option: any documentary
        { genre: 'documentary' },
    ]
}))

Related columns

You can also filter the data by the columns on a related model. This is achieved by using a dot after the relationship name:

$.get('/api/user?query=' + JSON.stringify({
    filter: {
        // Fields of the 'user' model
        first_name: 'John',
        last_name: 'Doe',
        // Field of a related 'address' model
        'address.zip': '100098',
    }
}))

Join Operation

Joining corresponds to the LEFT JOIN part of an SQL query (although implemented as a separate query).

In the back-end database, the data is often kept in a normalized form: items of different types are kept in different places. This means that whenever you need a related item, you'll have to explicitly request it.

The Join operation lets you load those related items.

Please keep in mind that most relationships would be disabled on the back-end because of security concerns about exposing sensitive data. Therefore, whenever a front-end developer needs to have a relationship loaded, it has to be manually enabled on the back-end! Please feel free to ask.

Examples follow.

Syntax

// This one will return all users
// (even those that have no articles)
$.get('/api/user?query=' + JSON.stringify({
    join: ["articles"]  // Regular Join: `join`
}))

This joinf Filtering Join operation does just the same thing that join does; however, if there were no related items, the primary one is also removed.

// This one will return *only those users that have articles*
// (users with no articles will be excluded)
$.get('/api/user?query=' + JSON.stringify({
    joinf: ["articles"]  // Filtering Join: `joinf`
}))

This feature is, quite honestly, weird, and is only available for backward-compatibility with a bug that existed in some early MongoSQL versions. It has proven to be useful in some cases, so the bug has been given a name and a place within the MongoSQL library :)

Note that joinf`` does not supportskipandlimit` on nested entities because of the way it's implemented with Postgres.

Aggregate Operation

Aggregation corresponds to the SELECT ... part of an SQL query with aggregation functions.

Sometimes the API user wouldn't need the data itself, but rather some statistics on that data: the smallest value, the largest value, the average value, the sum total of all values.

This is what aggregation does: lets the API user execute statistical queries on the data. Its features are limited, but in the spirit of MongoSQL, will save some routine work for back-end developers.

Example:

$.get('/api/user?query=' + JSON.stringify({
    // The youngest and the oldest
    min_age: { $min: 'age' },
    max_age: { $max: 'age' },

    // SUM(1) for every user produces the total number of users
    number_of_users: { $sum: 1 },

    // Count the number of youngsters: age < 18
    // This is a SUM() of a boolean expression, which gives 1 for every matching row.
    youngster_count: { $sum: { age: { $lt: 18 } } },
}))

Note that for security reasons, aggregation must be manually enabled for every field on the back-end.

Syntax

The syntax is an object that declares custom field names to be used for keeping results:

aggregate: { computed-field-name: <expression> }

The expression can be:

Note that aggregation often makes sense only when used together with the Group Operation.

Group Operation

Grouping corresponds to the GROUP BY part of an SQL query.

By default, the Aggregate Operation gives statistical results over all rows.

For instance, if you've asked for { avg_age: { $avg: 'age' } }, you'll get the average age of all users.

Oftentimes this is not enough, and you'll want statistics calculated over groups of items. This is what the Group Operation does: specifies which field to use as the "group" indicator.

Better start with a few examples.

Example #1: calculate the number of users of every specific age.

We use the age field as the group discriminator, and the total number of users is therefore calculated per group. The result would be: something like:

age 18: 25 users
age 19: 20 users
age 21: 35 users
...

The code:

$.get('/api/user?query=' + JSON.stringify({
    // The statistics
    aggregate: {
        age: 'age',  // Get the unadulterated column value
        count: { $sum: 1 },  // The count
    },
    // The discriminator
    group: ['age'],  // we do not discriminate by sex this time... :)
}))

Example #2: calculate teh average salary per profession

$.get('/api/user?query=' + JSON.stringify({
        prof: 'profession',
        salary: { '$avg': 'salary' }
    },
    group: ['profession_id'],
}))

Syntax

The Group Operator, as you have seen, receives an array of column names.

The Slice operation consists of two optional parts:

Together, these two elements implement pagination.

Example:

$.get('/api/user?query=' + JSON.stringify({
    limit: 100, // 100 items per page
    skip: 200,  // skip 200 items, meaning, we're on the third page
}))

Values: can be a number, or a null.

Count Operation

Slicing corresponds to the SELECT COUNT(*) part of an SQL query.

Simply, return the number of items, without returning the items themselves. Just a number. That's it.

Example:

$.get('/api/user?query=' + JSON.stringify({
    count: 1,
}))

The 1 is the on switch. Replace it with 0 to stop counting.

NOTE: In MongoSQL 2.0, there is a way to get both the list of items, and their count simultaneously. This would have way better performance than two separate queries. Please have a look: CountingQuery and MongoQuery.end_count().

JSON Column Support

A JSON (or JSONB) field is a column that contains an embedded object, which itself has fields too. You can access these fields using a dot.

Given a model fields:

model.data = { rating: 5.5, list: [1, 2, 3], obj: {a: 1} }

You can reference JSON field's internals:

'data.rating'
'data.list.0'
'data.obj.a'
'data.obj.z'  // gives NULL when a field does not exist

Operations that support it:

MongoSQL Programming Interface

MongoQuery

Creating a MongoQuery

MongoQuery is the main tool that lets you execute JSON Query Objects against an SqlAlchemy-handled database.

There are two ways to use it:

  1. Construct MongoQuery manually, giving it your model:

    from mongosql import MongoQuery
from .models import User  # Your model

ssn = Session()

# Create a MongoQuery, using an initial Query (possibly, with some initial filtering applied)
mq = MongoQuery(User).from_query(ssn.query(User))

  2. Use the convenience mixin for your Base:

    from sqlalchemy.ext.declarative import declarative_base
from mongosql import MongoSqlBase

Base = declarative_base(cls=(MongoSqlBase,))

class User(Base):
    #...

    Using this Base, your models will have a shortcut method which returns a MongoQuery:

    User.mongoquery(session)
User.mongoquery(query)

    With mongoquery(), you can construct a query from a session:

    mq = User.mongoquery(session)

    .. or from an sqlalchemy.orm.Query, which allows you to apply some initial filtering:

    mq = User.mongoquery(
    session.query(User).filter_by(active=True)  # Only query active users
)

Reusable

A MongoQuery object itself is not reusable: it can make just one query.

However, it makes sense to save some initialization and keep it ready for new requests. For performance reasons, this has to be done manually with the Reusable wrapper:

mq_factory = Reusable(User.mongoquery(session))

The wrapped object has all the methods of a MongoQuery, but will make a proper copy when used. Think of it as a factory.

Querying: MongoQuery.query()

Once a MongoQuery is prepared, you can give it a QueryObject:

# QueryObject
query_object = {
  'filter': {
    'sex': 'f',
    'age': { '$gte': 18, '$lte': 25 },  # 18..25 years
  },
  'order': ['weight+'],  #  slims first
  'limit': 50,  # just enough :)
}

# MongoQuery
mq = User.mongoquery(ssn).query(**query_object)

Getting Results: MongoQuery.end()

Because MongoQuery is just a wrapper around an SqlAlchemy's Query, you can get that Query and get results out of it:

# By calling the `MongoQuery.end()` method, you get an SqlAlchemy `Query`:
q = mq.end()  # SqlALchemy Query

# Execute the query and fetch results
girls = q.all()

Getting All Sorts of Results

Let's remember that the Query generated by MongoQuery can return three sorts of results:

  1. Entities. When the API user has requested an entity of a list of them.
  2. Integer. When the API user has used {count: 1}.
  3. Tuples. This is what you get when the API user has used the Aggregate Operation.

MongoQuery has three methods that help you detect what you get:

  1. MongoQuery.result_contains_entities()
  2. MongoQuery.result_is_scalar()
  3. MongoQuery.result_is_tuples()

Here's how to use it:

def get_result(mq: MongoQuery, query: Query):
    # Handle: Query Object has count
    if mq.result_is_scalar():
        return {'count': query.scalar()}

    # Handle: Query Object has group_by and yields tuples
    if mq.result_is_tuples():
        # zip() column names together with the values, and make it into a dict
        return {
            'results': [dict(zip(row.keys(), row))
                        for row in query]
        }

    # Regular result: entities
    return {
        'users': query.all()
    }

Most likely, you won't need to handle that at all: just use CRUD Helpers that implement most of this logic for you.

MongoQuery Configuration

MongoQuery has plenty of settings that lets you configure the way queries are made, to fine-tune their security limitations, and to implement some custom behaviors.

These settings can be nicely kept in a MongoQuerySettingsDict and given to MongoQuery as the second argument.

Example:

from mongosql import MongoQuery, MongoQuerySettingsDict

mq = MongoQuery(models.User, MongoQuerySettingsDict(
    bundled_project=dict(
        # can only join to the following relations
        allowed_relations=('articles', 'comments'),
        # configure nested queries
        related=dict(
            manager=dict(
                force_exclude=('password',),
            )
        ),
        # enable aggregation for columns
        aggregate_columns=('age',),
    ),
))

The available settings are:

More settings are available through the CRUD helper settings, which is an extension of MongoQuery Configuration:

MongoQuery API

MongoQuery(model, handler_settings=None)

MongoQuery is a wrapper around SqlAlchemy's Query that can safely execute JSON Query Objects

MongoQuery.from_query(query) -> MongoQuery

Specify a custom sqlalchemy query to work with.

It can have, say, initial filtering already applied to it. It no default query is provided, _from_query() will use the default.

Arguments:

Returns MongoQuery

MongoQuery.with_session(ssn) -> MongoQuery

Query with the given sqlalchemy Session

Arguments:

Returns MongoQuery

MongoQuery.query(**query_object) -> MongoQuery

Build a MongoSql query from an object

Arguments:

Returns MongoQuery

Exceptions:

MongoQuery.end() -> Query

Get the resulting sqlalchemy Query object

Returns Query

MongoQuery.end_count() -> CountingQuery

Get the result, and also count the total number of rows.

Be aware that the cost will be substantially higher than without the total number, but still cheaper than two separate queries.

Numbers: this gives about 50% boost to small result sets, and about 15% boost to larger result sets.

See CountingQuery for more details.

Returns CountingQuery

Example:

q = User.mongoquery(ssn).query(...).end_count()

# Get the count
q.count  # -> 127

# Get results
list(q)  # -> [User, ...]

# (!) only one actual SQL query was made

MongoQuery.result_contains_entities() -> bool

Test whether the result will contain entities.

This is normally the case in the absence of 'aggregate', 'group', and 'count' queries.

Returns bool

MongoQuery.result_is_scalar() -> bool

Test whether the result is a scalar value, like with count

In this case, you'll fetch it like this:

MongoQuery(...).end().scalar()

Returns bool

MongoQuery.result_is_tuples() -> bool

Test whether the result is a list of keyed tuples, like with group_by

In this case, you might fetch it like this:

res = MongoQuery(...).end()
return [dict(zip(row.keys(), row)) for row in res], None

Returns bool

MongoQuery.ensure_loaded(*cols) -> MongoQuery

Ensure the given columns, relationships, and related columns are loaded

Despite any projections and joins the user may be doing, make sure that the given cols are loaded. This will ensure that every column is loaded, every relationship is joined, and none of those is included into projection and pluck_instance.

This method is to be used by the application code to handle the following situation:

Limitations:

  1. If the user has requested filtering on a relationship, you can't use ensure_loaded() on it. This method will raise an InvalidQueryError(). This makes sense, because if your application code relies on the presence of a certain relationship, it certainly needs it fully loaded, and unfiltered.
  2. If the request contains no entities (e.g. 'group' or 'aggregate' handlers are used), this method would throw an AssertionError

If all you need is just to know whether something is loaded or not, use MongoQuery.contains() instead.

Remember that every time you use ensure_loaded() on a relationship, you disable the possibility of filtering for it!

Arguments:

Returns MongoQuery

Exceptions:

MongoQuery.get_final_query_object() -> dict

Get the final Query Object dict (after all handlers have applied their defaults).

This Query Object will contain the name of every single handler, including those that were not given any input.

Returns dict

MongoQuery.get_projection_tree() -> dict

Get a projection-like dict that maps every included column to 1, and every relationship to a nested projection dict.

Returns dict: the projection

Example:

MongoQuery(User).query(join={'articles': dict(project=('id',))}).handler_join.projection
#-> {'articles': {'id': 1}}

This is mainly useful for debugging nested Query Objects.

MongoQuery.get_full_projection_tree() -> dict

Get a full projection tree that mentions every column, but only those relationships that are loaded

Returns dict

MongoQuery.pluck_instance(instance) -> dict

Pluck an sqlalchemy instance and make it into a dict

This method should be used to prepare an object for JSON encoding. This makes sure that only the properties explicitly requested by the user get included into the result, and not the properties that your code may have loaded.

Projection and Join properties are considered.

Arguments:

Returns dict

Handlers

In addition to this, MongoQuery lets you inspect the internals of the MongoQuery. Every handler is available as a property of the MongoQuery:

Some of them have methods which may be useful for the application you're building, especially if you need to get some information out of MongoQuery.

CRUD Helpers

MongoSql is designed to help with data selection for the APIs. To ease the pain of implementing CRUD for all of your models, MongoSQL comes with a CRUD helper that exposes MongoSQL capabilities for querying to the API user. Together with RestfulView from flask-jsontools, CRUD controllers are extremely easy to build.

CrudHelper(model, writable_properties=True, **handler_settings)

Crud helper: an object that helps implement CRUD operations for an API endpoint:

Source: mongosql/crud/crudhelper.py

This object is supposed to be initialized only once; don't do it for every query, keep it at the class level!

Most likely, you'll want to keep it at the class level of your view:

from .models import User
from mongosql import CrudHelper

class UserView:
    crudhelper = CrudHelper(
        # The model to work with
        User,
        # Settings for MongoQuery
        **MongoQuerySettingsDict(
            allowed_relations=('user_profile',),
        )
    )
    # ...

Note that during "create" and "update" operations, this class lets you write values to column attributes, and also to @property that are writable (have a setter). If this behavior (with writable properties) is undesirable, set writable_properties=False

The following methods are available:

CrudHelper.query_model(query_obj=None, from_query=None) -> MongoQuery

Make a MongoQuery using the provided Query Object

Note that you have to provide the MongoQuery yourself. This is because it has to be properly configured with handler_settings.

Arguments:

Returns MongoQuery

Exceptions:

CrudHelper.create_model(entity_dict) -> object

Create an instance from entity dict.

This method lets you set the value of columns and writable properties, but not relations. Use @saves_relations to handle additional fields.

Arguments:

Returns object: Created instance

Exceptions:

CrudHelper.update_model(entity_dict, instance) -> object

Update an instance from an entity dict by merging the fields

Note that because properties are copied over, this operation does not replace the entity; it merely updates the entity.

In other words, this method does a partial update: only updates the fields that were provided by the client, leaving all the rest intact.

Arguments:

Returns object: New instance, updated

Exceptions:

StrictCrudHelper

A Strict Crud Helper imposes defaults and limitations on the API user:

Source: mongosql/crud/crudhelper.py

The following behavior is implemented:

StrictCrudHelper(model, writable_properties=True, ro_fields=None, rw_fields=None, const_fields=None, query_defaults=None, **handler_settings)

Initializes a strict CRUD helper

Note: use a **StrictCrudHelperSettingsDict() to help you with the argument names and their docs!

Arguments:

Example:

from .models import User
from mongosql import StrictCrudHelper, StrictCrudHelperSettingsDict

class UserView:
    crudhelper = StrictCrudHelper(
        # The model to work with
        User,
        # Settings for MongoQuery and StrictCrudHelper
        **StrictCrudHelperSettingsDict(
            # Can never be set of modified
            ro_fields=('id',),
            # Can only be set once
            const_fields=('login',),
            # Relations that can be `join`ed
            allowed_relations=('user_profile',),
        )
    )
    # ...

CrudViewMixin()

A mixin class for implementations of CRUD views.

This class is supposed to be re-initialized for every request.

To implement a CRUD view:

  1. Implement some method to extract the Query Object from the request
  2. Set crudhelper at the class level, initialize it with the proper settings
  3. Implement the _get_db_session() and the _get_query_object() methods
  4. If necessary, implement the _save_hook() to customize new & updated entities
  5. Override _method_list() and _method_get() to customize its output
  6. Override _method_create(), _method_update(), _method_delete() and implement saving to the DB
  7. Use @saves_relations method decorator to handle custom fields in the input dict

For an example on how to use CrudViewMixin, see this implementation: tests/crud_view.py

Attrs: _mongoquery (MongoQuery): The MongoQuery object used to process this query.

CrudViewMixin._get_db_session() -> Session

(Abstract method) Get a DB session to be used for queries made in this view

Returns Session: sqlalchemy.orm.Session

CrudViewMixin._get_query_object() -> Mapping

(Abstract method) Get the Query Object for the current query.

Note that the Query Object is not only supported for get() and list() methods, but also for create(), update(), and delete(). This enables the API use to request a relationship right away.

Returns Mapping

CrudViewMixin._method_get(*filter, **filter_by) -> object

(CRUD method) Fetch a single entity: as in READ, single entity

Normally, used when the user has supplied a primary key:

GET /users/1

Arguments:

Returns object

Exceptions:

CrudViewMixin._method_list(*filter, **filter_by) -> Iterable[object]

(CRUD method) Fetch a list of entities: as in READ, list of entities

Normally, used when the user has supplied no primary key:

GET /users/

NOTE: Be careful! This methods does not always return a list of entities! It can actually return:

  1. A scalar value: in case of a 'count' query
  2. A list of dicts: in case of an 'aggregate' or a 'group' query
  3. A list or entities: otherwise

Please use the following MongoQuery methods to tell what's going on: MongoQuery.result_contains_entities(), MongoQuery.result_is_scalar(), MongoQuery.result_is_tuples()

Or, else, override the following sub-methods: _method_list_resultentities(), _method_list_result__groups(), _method_list_resultcount()

Arguments:

Returns Iterable[object]

Exceptions:

CrudViewMixin._method_create(entity_dict) -> object

(CRUD method) Create a new entity: as in CREATE

Normally, used when the user has supplied no primary key:

POST /users/
{'name': 'Hakon'}

Arguments:

Returns object: The created instance (to be saved)

Exceptions:

CrudViewMixin._method_update(entity_dict, *filter, **filter_by) -> object

(CRUD method) Update an existing entity by merging the fields: as in UPDATE

Normally, used when the user has supplied a primary key:

POST /users/1
{'id': 1, 'name': 'Hakon'}

Arguments:

Returns object: The updated instance (to be saved)

Exceptions:

CrudViewMixin._method_delete(*filter, **filter_by) -> object

(CRUD method) Delete an existing entity: as in DELETE

Normally, used when the user has supplied a primary key:

DELETE /users/1

Note that it will load the entity from the database prior to deletion.

Arguments:

Returns object: The instance to be deleted

Exceptions:

CrudViewMixin._mongoquery_hook(mongoquery) -> MongoQuery

(Hook) A hook invoked in _mquery() to modify MongoQuery, if necessary

This is the last chance to modify a MongoQuery. Right after this hook, it end()s, and generates an sqlalchemy Query.

Use self._current_crud_method to tell what is going on: create, read, update, delete?

Arguments:

Returns MongoQuery

CrudViewMixin._save_hook(new, prev=None)

(Hook) Hooks into create(), update() methods, before an entity is saved.

This allows to make some changes to the instance before it's actually saved. The hook is provided with both the old and the new versions of the instance (!).

Note that it is executed before flush(), so DB defaults are not available yet.

Arguments:

CrudViewMixin._method_create_or_update_many(entity_dicts, *filter, **filter_by) -> Iterable[mongosql.util.bulk.EntityDictWrapper]

(CRUD method) Create-or-update many objects (aka upsert): create if no PK, update with PK

This smart method can be used to save (upsert: insert & update) many objects at once.

It will load those objects that have primary key fields set and update them with _method_update(). It will create objects that do not have primary key fields with _method_create() It will delegate to _method_create_or_update_many__create_arbitrary_pk() that have primary key fields but were not found in the database.

Note that the method uses EntityDictWrapper to preserve the order of entity dicts and return results associated with them:

Note that you may wrap entity dicts with EntityDictWrapper yourself. In this case, you may:

Arguments:

Returns Iterable[mongosql.util.bulk.EntityDictWrapper]

@saves_relations(*field_names)

A decorator that marks a method that handles saving some related models (or any other custom values)

Whenever a relationship is marked for saving with the help of this decorator, it is plucked out of the incoming JSON dict, and after an entity is created, it is passed to the method that this decorator decorates.

In addition to saving relationships, a decorated mthod can be used to save any custom properties: they're plucked out of the incoming entity dict, and handled manually anyway. Note that all attributes that do not exist on the model are plucked out, and the only way to handle them is through this method.

NOTE: this method is executed before _save_hook() is.

Example usage:

from mongosql import saves_relations
from mongosql import ABSENT  # unique marker used to detect values not provided

class UserView(CrudViewMixin):
    @saves_relations('articles')
    def save_articles(self, new: object, prev: object = None, articles = ABSENT):
        if articles is not ABSENT:
            ...  # articles-saving logic

NOTE: the handler method is called with two positional arguments, and the rest being keyword arguments:

save_articles(new_instance, prev_instance, **relations_to_be_saved)

NOTE: If the user did not submit any related entity, the method is still called, with relationship argument = None.

Multiple relations can be provided: in this case, all of them are handled with one method.

Other Useful Tools

ModelPropertyBags(model)

Model Property Bags is the class that lets you get information about the model's columns.

This is the class that binds them all together: Columns, Relationships, PKs, etc. All the meta-information about a certain Model is stored here:

Whenever it's too much to inspect several properties, use a CombinedBag() over them, which lets you get a column from a number of bags.

CombinedBag(**bags)

A bag that combines elements from multiple bags.

This one is used when something can handle both columns and relationships, or properties and columns. Because this depends on what you're doing, this generalized implementation is used.

In order to initialize it, you give them the bags you need as a dict:

cbag = CombinedBag(
    col=bags.columns,
    rel=bags.related_columns,
)

Now, when you get an item, you get the aliased name that you have used:

bag_name, bag, col = cbag['id']
bag_name  #-> 'col'
bag  #-> bags.columns
col  #-> User.id

This way, you can always tell which bag has the column come from, and handle it appropriately.

CountingQuery(query)

Query object wrapper that can count the rows while returning results

This is achieved by SELECTing like this:

SELECT *, count(*) OVER() AS full_count

In order to be transparent, this class eliminates all those tuples in results and still returns objects like a normal query would. The total count is available through a property.