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¶
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.”