SQLAlchemy-boolean-search¶

SQLAlchemy-boolean-search translates a boolean search string such as:

"field1=*something* and not (field2==1 or parent.field3<=10.0)"

into its corresponding SQLAlchemy query filter:

and_(DataModel.field1.ilike('%something%'),
     not_(or_(DataModel.field2.__eq__(2),
              DataModel.parent.field3.__le__(10.0))))

Relationship field names such as ‘parent.grandparent.name’ are accepted.

The code is stable, is used in production, and enjoys a test coverage of 100%.

Install¶

pip install sqlalchemy-boolean-search

Usage example¶

from sqlalchemy_boolean_search import parse_boolean_search

# DataModel defined elsewhere (with field1, field2 and field3)
from app.models import DataModel

# Parse boolean search into a parsed expression
boolean_search = 'field1=*something* and not (field2==1 or field3<=10.0)'
parsed_expression = parse_boolean_search(boolean_search)

# Retrieve records using a filter generated by the parsed expression
records = DataModel.query.filter(parsed_expression.filter(DataModel))

Order of precedence¶

The boolean operands have the following order of precedence:

not
and
or

So the following expression:

a=1 or not b=2 and c=3

will be interpreted as:

a=1 or ((not b=2) and c=3)

Search criteria¶

A search criteria must be in the form of: ‘name’ ‘operator’ ‘value’.

‘name’ must match an existing element field name.

Hierarchical dotted field names such as ‘parent.grandparent.name’ are accepted as long as the ‘parent’ and ‘grandparent’ relationships have been defined.

‘operator’ must be one of: ‘<’, ‘<=’, ‘=’, ‘==’, ‘!=’, ‘>=’ or ‘>’.
‘value’ is an alphanumeric string. If the value contains spaces it must be enclosed by quotes. For example: “string with spaces”.

For element field types that map to a float or an integer, a number comparison will be performed. That is: 11 > 2.

For other element field types, a string comparison will be performed. That is: “11” < “2”. All string comparisons are case INsensitive.

Wildcard values¶

The expression ‘name==a’ performs an exact match (notice the double equal signs).

It matches ‘a’ but not ‘Xa’, ‘XaX’ or ‘aX’.

The expression ‘name=a’ returns elements whose name contains the value ‘a’.

It matches ‘a’, ‘A’, ‘Xa’, ‘aX’ and ‘XaX’.

You can specify the wildcard character ‘*’ to perform ‘starts-with’ and ‘ends-with’ searches like so:
‘name=a*’ matches ‘a’ and ‘aX’ but not ‘Xa’.
‘name=*a’ matches ‘a’ and ‘Xa’ but not ‘aX’.

Note that ‘name=a’ is shorthand for ‘name=*a*’.

Exceptions¶

SQLAlchemy-boolean-search defines the exception BooleanSearchException.

parse_boolean_search() may raise a BooleanSearchException with the following message:

“Syntax error at offset <offset>.”

parsed_expression.filter() may raise a BooleanSearchException with one of the following messages:

“Table ‘<table-name>’ does not have a field named ‘<field-name>’.”
“Field ‘<field-name>’ expects an integer value. Received value ‘<value>’ instead.”
“Field ‘<field-name>’ expects a float value. Received value ‘<value’ instead.”