mqlalchemy Package

mqlalchemy Package

mqlalchemy.__init__

Query SQLAlchemy objects using MongoDB style syntax.

Mainly useful in providing a querying interface from JavaScript.

class mqlalchemy.MqlBuilder[source]

Bases: object

Class for building queries using MQL style filters.

text_types = [<class 'sqlalchemy.sql.sqltypes.String'>, <class 'sqlalchemy.sql.sqltypes.Unicode'>, <class 'sqlalchemy.sql.sqltypes.Enum'>, <class 'sqlalchemy.sql.sqltypes.Text'>, <class 'sqlalchemy.sql.sqltypes.UnicodeText'>, <class 'sqlalchemy.sql.sqltypes.CHAR'>, <class 'sqlalchemy.sql.sqltypes.CLOB'>, <class 'sqlalchemy.sql.sqltypes.NCHAR'>, <class 'sqlalchemy.sql.sqltypes.NVARCHAR'>, <class 'sqlalchemy.sql.sqltypes.TEXT'>, <class 'sqlalchemy.sql.sqltypes.VARCHAR'>]
int_types = [<class 'sqlalchemy.sql.sqltypes.Integer'>, <class 'sqlalchemy.sql.sqltypes.BigInteger'>, <class 'sqlalchemy.sql.sqltypes.SmallInteger'>, <class 'sqlalchemy.sql.sqltypes.BIGINT'>, <class 'sqlalchemy.sql.sqltypes.INTEGER'>, <class 'sqlalchemy.sql.sqltypes.INTEGER'>, <class 'sqlalchemy.sql.sqltypes.SMALLINT'>]
bool_types = [<class 'sqlalchemy.sql.sqltypes.Boolean'>, <class 'sqlalchemy.sql.sqltypes.BOOLEAN'>]
date_types = [<class 'sqlalchemy.sql.sqltypes.Date'>, <class 'sqlalchemy.sql.sqltypes.DATE'>]
datetime_types = [<class 'sqlalchemy.sql.sqltypes.DateTime'>, <class 'sqlalchemy.sql.sqltypes.DATETIME'>, <class 'sqlalchemy.sql.sqltypes.TIMESTAMP'>]
float_types = [<class 'sqlalchemy.sql.sqltypes.Float'>, <class 'sqlalchemy.sql.sqltypes.Numeric'>, <class 'sqlalchemy.sql.sqltypes.DECIMAL'>, <class 'sqlalchemy.sql.sqltypes.FLOAT'>, <class 'sqlalchemy.sql.sqltypes.NUMERIC'>, <class 'sqlalchemy.sql.sqltypes.REAL'>]
time_types = [<class 'sqlalchemy.sql.sqltypes.Time'>, <class 'sqlalchemy.sql.sqltypes.TIME'>]
classmethod apply_mql_filters(model_class, query=None, filters=None, whitelist=None, nested_conditions=None, stack_size_limit=None, convert_key_names_func=None, gettext=None)[source]

Applies filters to a select statement and returns it.

Bulk of the work here is done by parse_filters(), more detailed documentation can be found there.

Parameters:

  • model_class (DeclarativeMeta or AliasedClass) – SQLAlchemy model class you want to query.

  • query (Select) – A select statement that directly references the provided model_class. Optional, useful if there are already some set of filters previously applied.

  • filters (dict) – Dictionary of MongoDB style query filters.

  • whitelist (callable, list, or None) – Used to determine whether it’s permissible to filter by a given field. The field being checked will be a dot notation attr name (e.g. for Album tracks.playlists.playlist_id) that has already been processed by convert_key_names_func when applicable. If a callable is provided, it should take in a dot separated field name and return True if it is acceptable to query that field, or False if not. If a list of field names is provided, field names will be checked against that list to determine whether or not it is an allowed field to be queried. If None is provided, all fields and relationships of a model will be queryable.

  • nested_conditions – Provides SQL expressions, as would be used directly by filter(), for additional filtering on any nested relationships. Can be a callable accepting a single param, or a dict, where the param/key is the relationship being filtered will be provided as a dot notation name (e.g. for Album tracks.playlists) that has already been processed by convert_key_names_func when applicable. The value returned can be a single item, list, or tuple.

  • nested_conditions – callable, dict, or None

  • convert_key_names_func (callable) – Optional function used to convert a user provided attribute name into a field name for a model. Should take one parameter, which is a dot separated name, and should return a converted string in the same dot separated format. For example, say you want to be able to query your model, which contains field names with underscores, using lowerCamelCase instead. The provided function should take a string such as "tracks.unitPrice" and convert it to "tracks.unit_price". For the sake of raising more useful exceptions, the function should return None if an invalid field name is provided, however this is not necessary.

  • stack_size_limit (int or None) – Optional parameter used to limit the allowable complexity of the provided filters. Can be useful in preventing malicious query attempts.

  • gettext (callable or None) – Supply a translation function to convert error messages to the desired language. Note that no translations are included by default, you must generate your own.

Returns:

A filtered SQLAlchemy select object of the provided model_class.

Return type:

sqlalchemy.sql.selectable.Select

classmethod parse_mql_filters(model_class, filters=None, whitelist=None, nested_conditions=None, stack_size_limit=None, convert_key_names_func=None, gettext=None)[source]

Applies filters to a query and returns it.

Supported operators include:

  • $and

  • $or

  • $not

  • $nor

  • $in

  • $nin

  • $gt

  • $gte

  • $lt

  • $lte

  • $ne

  • $mod

Custom operators added for convenience:

  • $eq - Explicit equality check.

  • $like - Search a text field for the given value.

Filtering here works similarly to how MongoDB handles querying, with SQLAlchemy relationships being treated like MongoDB treats nested documents.

The whitelist and nested_conditions parameters can be used for fine grain access control, with both serving distinct purposes. whitelist can be used to apply rules regarding which fields can generally be queried, while nested_conditions can be used to apply rules regarding what nested records can be accessed in a query.

As an example, say you want a user to be able to query an album based on the playlists that album’s tracks belong to. Your whitelist would only need tracks.playlists.name to be included. If the user isn’t supposed to have access to playlists with "Private" in their name, your nested_conditions could look like:

def nested_conditions(key):
    if key == "tracks.playlists":
        return not Playlist.name.contains("Private")

Now if the user provides filters={"tracks.playlists.name": { "$like": "Private"}}, no resulting albums will be returned, even though the user does have access to the tracks.playlists.name field.

Parameters:

  • model_class – SQLAlchemy model class you want to query.

  • filters (dict) – Dictionary of MongoDB style query filters.

  • whitelist (callable, list, or None) – Used to determine whether it’s permissible to filter by a given field. The field being checked will be a dot notation attr name (e.g. for Album tracks.playlists.playlist_id) that has already been processed by convert_key_names_func when applicable. If a callable is provided, it should take in a dot separated field name and return True if it is acceptable to query that field, or False if not. If a list of field names is provided, field names will be checked against that list to determine whether or not it is an allowed field to be queried. If None is provided, all fields and relationships of a model will be queryable.

  • nested_conditions – Provides SQL expressions, as would be used directly by filter(), for additional filtering on any nested relationships. Can be a callable accepting a single param, or a dict, where the param/key is the relationship being filtered will be provided as a dot notation name (e.g. for Album tracks.playlists) that has already been processed by convert_key_names_func when applicable. The value returned can be a single item, list, or tuple.

  • nested_conditions – callable, dict, or None

  • convert_key_names_func (callable) – Optional function used to convert a provided attribute name into a field name for a model. Should take one parameter, which is a dot separated name, and should return a converted string in the same dot separated format. For example, say you want to be able to query your model, which contains field names with underscores, using lowerCamelCase instead. The provided function should take a string such as "tracks.unitPrice" and convert it to "tracks.unit_price". For the sake of raising more useful exceptions, the function should return None if an invalid field name is provided, however this is not necessary.

  • stack_size_limit (int or None) – Optional parameter used to limit the allowable complexity of the provided filters. Can be useful in preventing malicious query attempts.

  • gettext (callable or None) – Supply a translation function to convert error messages to the desired language. Note that no translations are included by default, you must generate your own.

classmethod convert_to_alchemy_type(value, alchemy_type)[source]

Convert a given value to a sqlalchemy friendly type.

As a simple example, if given the string "5" as value, for an alchemy_type of INT, this will return the int 5.

Parameters:

  • value – User supplied value for a filter.

  • alchemy_type – Target SQLAlchemy data type class to convert value to play nicely with.

Raises:

TypeError –

Returns:

A version of value that can be used in a SQLAlchemy expression involving an attr of the alchemy_type.

exception mqlalchemy.InvalidMqlException[source]

Bases: Exception

Generic exception class for invalid queries.

exception mqlalchemy.MqlTooComplex[source]

Bases: InvalidMqlException

Exception class for errors caused by overly complex queries.

exception mqlalchemy.MqlFieldError(data_key, filters, op, message, code, **kwargs)[source]

Bases: InvalidMqlException

Errors related to a specific field/attr.

__init__(data_key, filters, op, message, code, **kwargs)[source]

Initializes a new error.

Parameters:

  • data_key (str) – Dot separated field name the error applies to. This should typically be the converted, user facing data key name for ease of feedback.

  • filters – Filters being applied to the data_key. May be a dict or primitive value.

  • op (str or None) – Operation being applied to the data_key. May be None in cases where an implicit equality check or $elemMatch is being performed.

  • message (str) – Description of the error.

  • code (str) – A standardized descriptive error code, to make external reporting easier.

  • kwargs (dict) – Any additional arguments may be stored along with the message as well.

exception mqlalchemy.MqlFieldPermissionError(data_key, filters, op, message, code, **kwargs)[source]

Bases: MqlFieldError

Errors for impermissible access to a field.

mqlalchemy.apply_mql_filters(model_class, query=None, filters=None, whitelist=None, nested_conditions=None, stack_size_limit=None, convert_key_names_func=None, gettext=None)

Applies filters to a select statement and returns it.

Bulk of the work here is done by parse_filters(), more detailed documentation can be found there.

Parameters:

  • model_class (DeclarativeMeta or AliasedClass) – SQLAlchemy model class you want to query.

  • query (Select) – A select statement that directly references the provided model_class. Optional, useful if there are already some set of filters previously applied.

  • filters (dict) – Dictionary of MongoDB style query filters.

  • whitelist (callable, list, or None) – Used to determine whether it’s permissible to filter by a given field. The field being checked will be a dot notation attr name (e.g. for Album tracks.playlists.playlist_id) that has already been processed by convert_key_names_func when applicable. If a callable is provided, it should take in a dot separated field name and return True if it is acceptable to query that field, or False if not. If a list of field names is provided, field names will be checked against that list to determine whether or not it is an allowed field to be queried. If None is provided, all fields and relationships of a model will be queryable.

  • nested_conditions – Provides SQL expressions, as would be used directly by filter(), for additional filtering on any nested relationships. Can be a callable accepting a single param, or a dict, where the param/key is the relationship being filtered will be provided as a dot notation name (e.g. for Album tracks.playlists) that has already been processed by convert_key_names_func when applicable. The value returned can be a single item, list, or tuple.

  • nested_conditions – callable, dict, or None

  • convert_key_names_func (callable) – Optional function used to convert a user provided attribute name into a field name for a model. Should take one parameter, which is a dot separated name, and should return a converted string in the same dot separated format. For example, say you want to be able to query your model, which contains field names with underscores, using lowerCamelCase instead. The provided function should take a string such as "tracks.unitPrice" and convert it to "tracks.unit_price". For the sake of raising more useful exceptions, the function should return None if an invalid field name is provided, however this is not necessary.

  • stack_size_limit (int or None) – Optional parameter used to limit the allowable complexity of the provided filters. Can be useful in preventing malicious query attempts.

  • gettext (callable or None) – Supply a translation function to convert error messages to the desired language. Note that no translations are included by default, you must generate your own.

Returns:

A filtered SQLAlchemy select object of the provided model_class.

Return type:

sqlalchemy.sql.selectable.Select

mqlalchemy.convert_to_alchemy_type(value, alchemy_type)

Convert a given value to a sqlalchemy friendly type.

As a simple example, if given the string "5" as value, for an alchemy_type of INT, this will return the int 5.

Parameters:

  • value – User supplied value for a filter.

  • alchemy_type – Target SQLAlchemy data type class to convert value to play nicely with.

Raises:

TypeError –

Returns:

A version of value that can be used in a SQLAlchemy expression involving an attr of the alchemy_type.