operators
is a callable within the sqlalchemy.sql
module of the SQLAlchemy project.
ClauseElement,
Select,
column,
expression,
extract,
functions,
schema,
select,
sqltypes,
and table
are several other callables with code examples from the same sqlalchemy.sql
package.
GeoAlchemy2 (project documentation and PyPI package information) extends SQLAlchemy with new data types for working with geospatial databases, particularly PostGIS, which is a spatial database extender for PostgreSQL. The project is provided as open source under the MIT license.
GeoAlchemy2 / geoalchemy2 / comparator.py
# comparator.py
from sqlalchemy import types as sqltypes
from sqlalchemy.types import UserDefinedType
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from sqlalchemy.sql import operators
try:
from sqlalchemy.sql.functions import _FunctionGenerator
except ImportError: # SQLA < 0.9 # pragma: no cover
from sqlalchemy.sql.expression import _FunctionGenerator
INTERSECTS = operators.custom_op('&&')
OVERLAPS_OR_TO_LEFT = operators.custom_op('&<')
OVERLAPS_OR_TO_RIGHT = operators.custom_op('&>')
OVERLAPS_OR_BELOW = operators.custom_op('&<|')
TO_LEFT = operators.custom_op('<<')
BELOW = operators.custom_op('<<|')
TO_RIGHT = operators.custom_op('>>')
CONTAINED = operators.custom_op('@')
OVERLAPS_OR_ABOVE = operators.custom_op('|&>')
ABOVE = operators.custom_op('|>>')
CONTAINS = operators.custom_op('-')
SAME = operators.custom_op('-=')
DISTANCE_CENTROID = operators.custom_op('<->')
DISTANCE_BOX = operators.custom_op('<#>')
class BaseComparator(UserDefinedType.Comparator):
key = None
def __getattr__(self, name):
if not name.lower().startswith('st_'):
raise AttributeError
func_ = _FunctionGenerator(expr=self.expr)
return getattr(func_, name)
def intersects(self, other):
return self.operate(INTERSECTS, other, result_type=sqltypes.Boolean)
def overlaps_or_to_left(self, other):
return self.operate(OVERLAPS_OR_TO_LEFT, other,
result_type=sqltypes.Boolean)
def overlaps_or_to_right(self, other):
## ... source file continues with no further operators examples...
SQLAlchemy Mixins (PyPI package information) is a collection of mixins useful for extending SQLAlchemy and simplifying your database-interacting code for some common use cases. SQLAlchemy Mixins is open sourced under the MIT license.
SQLAlchemy Mixins / sqlalchemy_mixins / smartquery.py
# smartquery.py
try:
from typing import List
except ImportError: # pragma: no cover
pass
from collections import OrderedDict
import sqlalchemy
from sqlalchemy import asc, desc, inspect
from sqlalchemy.orm import aliased, contains_eager
from sqlalchemy.orm.util import AliasedClass
from sqlalchemy.sql import operators, extract
from .eagerload import _flatten_schema, _eager_expr_from_flat_schema, \
EagerLoadMixin
from .inspection import InspectionMixin
from .utils import classproperty
RELATION_SPLITTER = '___'
OPERATOR_SPLITTER = '__'
DESC_PREFIX = '-'
def _parse_path_and_make_aliases(entity, entity_path, attrs, aliases):
relations = {}
for attr in attrs:
if RELATION_SPLITTER in attr:
relation_name, nested_attr = attr.split(RELATION_SPLITTER, 1)
if relation_name in relations:
relations[relation_name].append(nested_attr)
else:
relations[relation_name] = [nested_attr]
for relation_name, nested_attrs in relations.items():
path = entity_path + RELATION_SPLITTER + relation_name \
## ... source file abbreviated to get to operators examples ...
attr = attr.lstrip(DESC_PREFIX)
parts = attr.rsplit(RELATION_SPLITTER, 1)
entity, attr_name = aliases[parts[0]][0], prefix + parts[1]
else:
entity, attr_name = root_cls, attr
try:
query = query.order_by(*entity.order_expr(attr_name))
except KeyError as e:
raise KeyError("Incorrect order path `{}`: {}".format(attr, e))
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))
return query
class SmartQueryMixin(InspectionMixin, EagerLoadMixin):
__abstract__ = True
_operators = {
'isnull': lambda c, v: (c == None) if v else (c != None),
'exact': operators.eq,
'ne': operators.ne, # not equal or is not (for None)
'gt': operators.gt, # greater than , >
'ge': operators.ge, # greater than or equal, >=
'lt': operators.lt, # lower than, <
'le': operators.le, # lower than or equal, <=
'in': operators.in_op,
'notin': operators.notin_op,
'between': lambda c, v: c.between(v[0], v[1]),
'like': operators.like_op,
'ilike': operators.ilike_op,
'startswith': operators.startswith_op,
'istartswith': lambda c, v: c.ilike(v + '%'),
'endswith': operators.endswith_op,
'iendswith': lambda c, v: c.ilike('%' + v),
'contains': lambda c, v: c.ilike('%{v}%'.format(v=v)),
'year': lambda c, v: extract('year', c) == v,
'year_ne': lambda c, v: extract('year', c) != v,
'year_gt': lambda c, v: extract('year', c) > v,
'year_ge': lambda c, v: extract('year', c) >= v,
'year_lt': lambda c, v: extract('year', c) < v,
'year_le': lambda c, v: extract('year', c) <= v,
'month': lambda c, v: extract('month', c) == v,
'month_ne': lambda c, v: extract('month', c) != v,
'month_gt': lambda c, v: extract('month', c) > v,
'month_ge': lambda c, v: extract('month', c) >= v,
'month_lt': lambda c, v: extract('month', c) < v,
'month_le': lambda c, v: extract('month', c) <= v,
'day': lambda c, v: extract('day', c) == v,
'day_ne': lambda c, v: extract('day', c) != v,
'day_gt': lambda c, v: extract('day', c) > v,
'day_ge': lambda c, v: extract('day', c) >= v,
'day_lt': lambda c, v: extract('day', c) < v,
'day_le': lambda c, v: extract('day', c) <= v,
}
## ... source file abbreviated to get to operators examples ...
@classproperty
def sortable_attributes(cls):
return cls.columns + cls.hybrid_properties
@classmethod
def filter_expr(cls_or_alias, **filters):
if isinstance(cls_or_alias, AliasedClass):
mapper, cls = cls_or_alias, inspect(cls_or_alias).mapper.class_
else:
mapper = cls = cls_or_alias
expressions = []
valid_attributes = cls.filterable_attributes
for attr, value in filters.items():
if attr in cls.hybrid_methods:
method = getattr(cls, attr)
expressions.append(method(value, mapper=mapper))
else:
if OPERATOR_SPLITTER in attr:
attr_name, op_name = attr.rsplit(OPERATOR_SPLITTER, 1)
if op_name not in cls._operators:
raise KeyError('Expression `{}` has incorrect '
'operator `{}`'.format(attr, op_name))
op = cls._operators[op_name]
else:
attr_name, op = attr, operators.eq
if attr_name not in valid_attributes:
raise KeyError('Expression `{}` '
'has incorrect attribute `{}`'
.format(attr, attr_name))
column = getattr(mapper, attr_name)
expressions.append(op(column, value))
return expressions
@classmethod
def order_expr(cls_or_alias, *columns):
if isinstance(cls_or_alias, AliasedClass):
mapper, cls = cls_or_alias, inspect(cls_or_alias).mapper.class_
else:
mapper = cls = cls_or_alias
expressions = []
for attr in columns:
fn, attr = (desc, attr[1:]) if attr.startswith(DESC_PREFIX) \
else (asc, attr)
if attr not in cls.sortable_attributes:
raise KeyError('Cant order {} by {}'.format(cls, attr))
## ... source file continues with no further operators examples...