Bases: sqlalchemy.sql.operators.ColumnOperators
Defines SQL operators for MapperProperty objects.
SQLAlchemy allows for operators to
be redefined at both the Core and ORM level. PropComparator
is the base class of operator redefinition for ORM-level operations,
including those of ColumnProperty,
RelationshipProperty, and CompositeProperty.
User-defined subclasses of PropComparator may be created. The
built-in Python comparison and math operator methods, such as
operators.ColumnOperators.__eq__(),
operators.ColumnOperators.__lt__(), and
operators.ColumnOperators.__add__(), can be overridden to provide
new operator behavior. The custom PropComparator is passed to
the MapperProperty instance via the comparator_factory
argument. In each case,
the appropriate subclass of PropComparator should be used:
# definition of custom PropComparator subclasses
from sqlalchemy.orm.properties import \
ColumnProperty,\
CompositeProperty,\
RelationshipProperty
class MyColumnComparator(ColumnProperty.Comparator):
def __eq__(self, other):
return self.__clause_element__() == other
class MyRelationshipComparator(RelationshipProperty.Comparator):
def any(self, expression):
"define the 'any' operation"
# ...
class MyCompositeComparator(CompositeProperty.Comparator):
def __gt__(self, other):
"redefine the 'greater than' operation"
return sql.and_(*[a>b for a, b in
zip(self.__clause_element__().clauses,
other.__composite_values__())])
# application of custom PropComparator subclasses
from sqlalchemy.orm import column_property, relationship, composite
from sqlalchemy import Column, String
class SomeMappedClass(Base):
some_column = column_property(Column("some_column", String),
comparator_factory=MyColumnComparator)
some_relationship = relationship(SomeOtherClass,
comparator_factory=MyRelationshipComparator)
some_composite = composite(
Column("a", String), Column("b", String),
comparator_factory=MyCompositeComparator
)
Note that for column-level operator redefinition, it’s usually
simpler to define the operators at the Core level, using the
TypeEngine.comparator_factory attribute. See
Redefining and Creating New Operators for more detail.
-
__eq__(other)
-
Implement the == operator.
In a column context, produces the clause a = b.
If the target is None, produces a IS NULL.
-
__le__(other)
-
Implement the <= operator.
In a column context, produces the clause a <= b.
-
__lt__(other)
-
Implement the < operator.
In a column context, produces the clause a < b.
-
__ne__(other)
-
Implement the != operator.
In a column context, produces the clause a != b.
If the target is None, produces a IS NOT NULL.
-
adapt_to_entity(adapt_to_entity)
Return a copy of this PropComparator which will use the given
AliasedInsp to produce corresponding expressions.
-
property
adapter
Produce a callable that adapts column expressions
to suit an aliased version of this comparator.
-
all_()
-
Produce a all_() clause against the
parent object.
This operator is only appropriate against a scalar subquery
object, or for some backends an column expression that is
against the ARRAY type, e.g.:
# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()
-
any(criterion=None, **kwargs)
Return true if this collection contains any member that meets the
given criterion.
The usual implementation of any() is
RelationshipProperty.Comparator.any().
- Parameters
criterion – an optional ClauseElement formulated against the
member class’ table or attributes.
**kwargs – key/value pairs corresponding to member class
attribute names which will be compared via equality to the
corresponding values.
-
any_()
-
Produce a any_() clause against the
parent object.
This operator is only appropriate against a scalar subquery
object, or for some backends an column expression that is
against the ARRAY type, e.g.:
# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()
-
asc()
-
Produce a asc() clause against the
parent object.
-
between(cleft, cright, symmetric=False)
-
Produce a between() clause against
the parent object, given the lower and upper range.
-
bool_op(opstring, precedence=0)
-
Return a custom boolean operator.
This method is shorthand for calling
Operators.op() and passing the
Operators.op.is_comparison
flag with True.
-
collate(collation)
-
Produce a collate() clause against
the parent object, given the collation string.
-
concat(other)
-
Implement the ‘concat’ operator.
In a column context, produces the clause a || b,
or uses the concat() operator on MySQL.
-
contains(other, **kwargs)
-
Implement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle
of a string value:
column LIKE '%' || <other> || '%'
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.contains("foobar"))
Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.contains.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.contains.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
- Parameters
other – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters % and _ are not escaped by default unless
the ColumnOperators.contains.autoescape flag is
set to True.
autoescape –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%", "_" and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.contains("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
With the value of :param as "foo/%bar".
escape –
a character which when given will render with the
ESCAPE keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of % and _ to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.contains("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.contains.autoescape:
somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat" before being passed to the database.
-
desc()
-
Produce a desc() clause against the
parent object.
-
distinct()
-
Produce a distinct() clause against the
parent object.
-
endswith(other, **kwargs)
-
Implement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end
of a string value:
column LIKE '%' || <other>
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.endswith("foobar"))
Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.endswith.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.endswith.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
- Parameters
other – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters % and _ are not escaped by default unless
the ColumnOperators.endswith.autoescape flag is
set to True.
autoescape –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%", "_" and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.endswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param ESCAPE '/'
With the value of :param as "foo/%bar".
escape –
a character which when given will render with the
ESCAPE keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of % and _ to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.endswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param ESCAPE '^'
The parameter may also be combined with
ColumnOperators.endswith.autoescape:
somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat" before being passed to the database.
-
has(criterion=None, **kwargs)
Return true if this element references a member which meets the
given criterion.
The usual implementation of has() is
RelationshipProperty.Comparator.has().
- Parameters
criterion – an optional ClauseElement formulated against the
member class’ table or attributes.
**kwargs – key/value pairs corresponding to member class
attribute names which will be compared via equality to the
corresponding values.
-
ilike(other, escape=None)
-
Implement the ilike operator, e.g. case insensitive LIKE.
In a column context, produces an expression either of the form:
lower(a) LIKE lower(other)
Or on backends that support the ILIKE operator:
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.ilike("%foobar%"))
- Parameters
other – expression to be compared
escape –
optional escape character, renders the ESCAPE
keyword, e.g.:
somecolumn.ilike("foo/%bar", escape="/")
-
in_(other)
-
Implement the in operator.
In a column context, produces the clause column IN <other>.
The given parameter other may be:
A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))
In this calling form, the list of items is converted to a set of
bound parameters the same length as the list given:
An empty list, e.g.:
stmt.where(column.in_([]))
In this calling form, the expression renders a “false” expression,
e.g.:
This “false” expression has historically had different behaviors
in older SQLAlchemy versions, see
create_engine.empty_in_strategy for behavioral options.
Changed in version 1.2: simplified the behavior of “empty in”
expressions
A bound parameter, e.g. bindparam(), may be used if it
includes the bindparam.expanding flag:
stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL
placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution
time to be converted into the variable number of bound parameter
form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})
The database would be passed a bound parameter for each value:
New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression,
which is specific to the database in use, is rendered. On
SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support
empty lists
a select() construct, which is usually a correlated
scalar select:
stmt.where(
column.in_(
select([othertable.c.y]).
where(table.c.x == othertable.c.x)
)
)
In this calling form, ColumnOperators.in_() renders as given:
WHERE COL IN (SELECT othertable.y
FROM othertable WHERE othertable.x = table.x)
- Parameters
other – a list of literals, a select() construct,
or a bindparam() construct that includes the
bindparam.expanding flag set to True.
-
is_(other)
-
Implement the IS operator.
Normally, IS is generated automatically when comparing to a
value of None, which resolves to NULL. However, explicit
usage of IS may be desirable if comparing to boolean values
on certain platforms.
-
is_distinct_from(other)
-
Implement the IS DISTINCT FROM operator.
Renders “a IS DISTINCT FROM b” on most platforms;
on some such as SQLite may render “a IS NOT b”.
-
isnot(other)
-
Implement the IS NOT operator.
Normally, IS NOT is generated automatically when comparing to a
value of None, which resolves to NULL. However, explicit
usage of IS NOT may be desirable if comparing to boolean values
on certain platforms.
-
isnot_distinct_from(other)
-
Implement the IS NOT DISTINCT FROM operator.
Renders “a IS NOT DISTINCT FROM b” on most platforms;
on some such as SQLite may render “a IS b”.
-
like(other, escape=None)
-
Implement the like operator.
In a column context, produces the expression:
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.like("%foobar%"))
- Parameters
other – expression to be compared
escape –
optional escape character, renders the ESCAPE
keyword, e.g.:
somecolumn.like("foo/%bar", escape="/")
-
match(other, **kwargs)
-
Implements a database-specific ‘match’ operator.
match() attempts to resolve to
a MATCH-like function or operator provided by the backend.
Examples include:
PostgreSQL - renders x @@ to_tsquery(y)
MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)
Oracle - renders CONTAINS(x, y)
other backends may provide special implementations.
Backends without any special implementation will emit
the operator as “MATCH”. This is compatible with SQLite, for
example.
-
notilike(other, escape=None)
-
implement the NOT ILIKE operator.
This is equivalent to using negation with
ColumnOperators.ilike(), i.e. ~x.ilike(y).
-
notin_(other)
-
implement the NOT IN operator.
This is equivalent to using negation with
ColumnOperators.in_(), i.e. ~x.in_(y).
In the case that other is an empty sequence, the compiler
produces an “empty not in” expression. This defaults to the
expression “1 = 1” to produce true in all cases. The
create_engine.empty_in_strategy may be used to
alter this behavior.
-
notlike(other, escape=None)
-
implement the NOT LIKE operator.
This is equivalent to using negation with
ColumnOperators.like(), i.e. ~x.like(y).
-
nullsfirst()
-
Produce a nullsfirst() clause against the
parent object.
-
nullslast()
-
Produce a nullslast() clause against the
parent object.
-
of_type(class_)
Redefine this object in terms of a polymorphic subclass.
Returns a new PropComparator from which further criterion can be
evaluated.
e.g.:
query.join(Company.employees.of_type(Engineer)).\
filter(Engineer.name=='foo')
- Parameters
class_ – a class or mapper indicating that criterion will be
against this specific subclass.
-
op(opstring, precedence=0, is_comparison=False, return_type=None)
-
produce a generic operator function.
e.g.:
produces:
This function can also be used to make bitwise operators explicit. For
example:
is a bitwise AND of the value in somecolumn.
- Parameters
operator – a string which will be output as the infix operator
between this element and the expression passed to the
generated function.
precedence – precedence to apply to the operator, when
parenthesizing expressions. A lower number will cause the expression
to be parenthesized when applied against another operator with
higher precedence. The default value of 0 is lower than all
operators except for the comma (,) and AS operators.
A value of 100 will be higher or equal to all operators, and -100
will be lower than or equal to all operators.
is_comparison –
if True, the operator will be considered as a
“comparison” operator, that is which evaluates to a boolean
true/false value, like ==, >, etc. This flag should be set
so that ORM relationships can establish that the operator is a
comparison operator when used in a custom join condition.
return_type –
a TypeEngine class or object that will
force the return type of an expression produced by this operator
to be of that type. By default, operators that specify
Operators.op.is_comparison will resolve to
Boolean, and those that do not will be of the same
type as the left-hand operand.
-
operate(op, *other, **kwargs)
-
Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower() to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
- Parameters
-
-
reverse_operate(op, other, **kwargs)
-
Reverse operate on an argument.
Usage is the same as operate().
-
startswith(other, **kwargs)
-
Implement the startswith operator.
Produces a LIKE expression that tests against a match for the start
of a string value:
column LIKE <other> || '%'
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.startswith("foobar"))
Since the operator uses LIKE, wildcard characters
"%" and "_" that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.startswith.autoescape flag
may be set to True to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.startswith.escape parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
- Parameters
other – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters % and _ are not escaped by default unless
the ColumnOperators.startswith.autoescape flag is
set to True.
autoescape –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%", "_" and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.startswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE :param || '%' ESCAPE '/'
With the value of :param as "foo/%bar".
escape –
a character which when given will render with the
ESCAPE keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of % and _ to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.startswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.startswith.autoescape:
somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat" before being passed to the database.