class ColumnOperators(Operators): (source)
Known subclasses: sqlalchemy.orm.interfaces.PropComparator, sqlalchemy.sql.elements.SQLCoreOperations, sqlalchemy.sql.lambdas.PyWrapper, sqlalchemy.sql.type_api.TypeEngine.Comparator, sqlalchemy.orm.evaluator._ExpiredObject, sqlalchemy.orm.evaluator._NoObject
Defines boolean, comparison, and other operators for :class:`_expression.ColumnElement` expressions. By default, all methods call down to :meth:`.operate` or :meth:`.reverse_operate`, passing in the appropriate operator function from the Python builtin ``operator`` module or a SQLAlchemy-specific operator function from :mod:`sqlalchemy.expression.operators`. For example the ``__eq__`` function:: def __eq__(self, other): return self.operate(operators.eq, other) Where ``operators.eq`` is essentially:: def eq(a, b): return a == b The core column expression unit :class:`_expression.ColumnElement` overrides :meth:`.Operators.operate` and others to return further :class:`_expression.ColumnElement` constructs, so that the ``==`` operation above is replaced by a clause construct. .. seealso:: :ref:`types_operators` :attr:`.TypeEngine.comparator_factory` :class:`.ColumnOperators` :class:`.PropComparator`
| Method | __add__ |
Implement the ``+`` operator. |
| Method | __contains__ |
Undocumented |
| Method | __eq__ |
Implement the ``==`` operator. |
| Method | __floordiv__ |
Implement the ``//`` operator. |
| Method | __ge__ |
Implement the ``>=`` operator. |
| Method | __getitem__ |
Implement the [] operator. |
| Method | __gt__ |
Implement the ``>`` operator. |
| Method | __le__ |
Implement the ``<=`` operator. |
| Method | __lshift__ |
implement the << operator. |
| Method | __lt__ |
Implement the ``<`` operator. |
| Method | __mod__ |
Implement the ``%`` operator. |
| Method | __mul__ |
Implement the ``*`` operator. |
| Method | __ne__ |
Implement the ``!=`` operator. |
| Method | __neg__ |
Implement the ``-`` operator. |
| Method | __radd__ |
Implement the ``+`` operator in reverse. |
| Method | __rfloordiv__ |
Implement the ``//`` operator in reverse. |
| Method | __rmod__ |
Implement the ``%`` operator in reverse. |
| Method | __rmul__ |
Implement the ``*`` operator in reverse. |
| Method | __rshift__ |
implement the >> operator. |
| Method | __rsub__ |
Implement the ``-`` operator in reverse. |
| Method | __rtruediv__ |
Implement the ``/`` operator in reverse. |
| Method | __sub__ |
Implement the ``-`` operator. |
| Method | __truediv__ |
Implement the ``/`` operator. |
| Method | all_ |
Produce an :func:`_expression.all_` clause against the parent object. |
| Method | any_ |
Produce an :func:`_expression.any_` clause against the parent object. |
| Method | asc |
Produce a :func:`_expression.asc` clause against the parent object. |
| Method | between |
Produce a :func:`_expression.between` clause against the parent object, given the lower and upper range. |
| Method | bitwise |
Produce a bitwise AND operation, typically via the ``&`` operator. |
| Method | bitwise |
Produce a bitwise LSHIFT operation, typically via the ``<<`` operator. |
| Method | bitwise |
Produce a bitwise NOT operation, typically via the ``~`` operator. |
| Method | bitwise |
Produce a bitwise OR operation, typically via the ``|`` operator. |
| Method | bitwise |
Produce a bitwise RSHIFT operation, typically via the ``>>`` operator. |
| Method | bitwise |
Produce a bitwise XOR operation, typically via the ``^`` operator, or ``#`` for PostgreSQL. |
| Method | collate |
Produce a :func:`_expression.collate` clause against the parent object, given the collation string. |
| Method | concat |
Implement the 'concat' operator. |
| Method | contains |
Implement the 'contains' operator. |
| Method | desc |
Produce a :func:`_expression.desc` clause against the parent object. |
| Method | distinct |
Produce a :func:`_expression.distinct` clause against the parent object. |
| Method | endswith |
Implement the 'endswith' operator. |
| Method | icontains |
Implement the ``icontains`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.contains`. |
| Method | iendswith |
Implement the ``iendswith`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.endswith`. |
| Method | ilike |
Implement the ``ilike`` operator, e.g. case insensitive LIKE. |
| Method | in_ |
Implement the ``in`` operator. |
| Method | is_ |
Implement the ``IS`` operator. |
| Method | is |
Implement the ``IS DISTINCT FROM`` operator. |
| Method | is |
Implement the ``IS NOT`` operator. |
| Method | is |
Implement the ``IS NOT DISTINCT FROM`` operator. |
| Method | isnot |
Undocumented |
| Method | isnot |
Undocumented |
| Method | istartswith |
Implement the ``istartswith`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.startswith`. |
| Method | like |
Implement the ``like`` operator. |
| Method | match |
Implements a database-specific 'match' operator. |
| Method | not |
implement the ``NOT ILIKE`` operator. |
| Method | not |
implement the ``NOT IN`` operator. |
| Method | not |
implement the ``NOT LIKE`` operator. |
| Method | notilike |
Undocumented |
| Method | notin_ |
Undocumented |
| Method | notlike |
Undocumented |
| Method | nulls |
Produce a :func:`_expression.nulls_first` clause against the parent object. |
| Method | nulls |
Produce a :func:`_expression.nulls_last` clause against the parent object. |
| Method | nullsfirst |
Undocumented |
| Method | nullslast |
Undocumented |
| Method | operate |
Operate on an argument. |
| Method | regexp |
Implements a database-specific 'regexp match' operator. |
| Method | regexp |
Implements a database-specific 'regexp replace' operator. |
| Method | reverse |
Reverse operate on an argument. |
| Method | startswith |
Implement the ``startswith`` operator. |
| Class Variable | __slots__ |
Undocumented |
| Class Variable | timetuple |
Hack, allows datetime objects to be compared on the LHS. |
| Method | _rconcat |
Implement an 'rconcat' operator. |
Inherited from Operators:
| Method | __and__ |
Implement the ``&`` operator. |
| Method | __invert__ |
Implement the ``~`` operator. |
| Method | __or__ |
Implement the ``|`` operator. |
| Method | bool |
Return a custom boolean operator. |
| Method | op |
Produce a generic operator function. |
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``+`` operator. In a column context, produces the clause ``a + b`` if the parent object has non-string affinity. If the parent object has a string affinity, produces the concatenation operator, ``a || b`` - see :meth:`.ColumnOperators.concat`.
sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.orm.properties.RelationshipProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``==`` operator. In a column context, produces the clause ``a = b``. If the target is ``None``, produces ``a IS NULL``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``//`` operator. In a column context, produces the clause ``a / b``, which is the same as "truediv", but considers the result type to be integer. .. versionadded:: 2.0
sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``>=`` operator. In a column context, produces the clause ``a >= b``.
sqlalchemy.sql.elements.SQLCoreOperations, sqlalchemy.sql.lambdas.PyWrapper, sqlalchemy.sql.sqltypes.Indexable.ComparatorImplement the [] operator. This can be used by some database-specific types such as PostgreSQL ARRAY and HSTORE.
sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``>`` operator. In a column context, produces the clause ``a > b``.
sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``<=`` operator. In a column context, produces the clause ``a <= b``.
implement the << operator. Not used by SQLAlchemy core, this is provided for custom operator systems which want to use << as an extension point.
sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``<`` operator. In a column context, produces the clause ``a < b``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``%`` operator. In a column context, produces the clause ``a % b``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``*`` operator. In a column context, produces the clause ``a * b``.
sqlalchemy.dialects.postgresql.ranges.AbstractRange.comparator_factory, sqlalchemy.orm.descriptor_props.CompositeProperty.Comparator, sqlalchemy.orm.properties.RelationshipProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``!=`` operator. In a column context, produces the clause ``a != b``. If the target is ``None``, produces ``a IS NOT NULL``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``-`` operator. In a column context, produces the clause ``-a``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``+`` operator in reverse. See :meth:`.ColumnOperators.__add__`.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``//`` operator in reverse. See :meth:`.ColumnOperators.__floordiv__`.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``%`` operator in reverse. See :meth:`.ColumnOperators.__mod__`.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``*`` operator in reverse. See :meth:`.ColumnOperators.__mul__`.
implement the >> operator. Not used by SQLAlchemy core, this is provided for custom operator systems which want to use >> as an extension point.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``-`` operator in reverse. See :meth:`.ColumnOperators.__sub__`.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``/`` operator in reverse. See :meth:`.ColumnOperators.__truediv__`.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``-`` operator. In a column context, produces the clause ``a - b``.
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``/`` operator. In a column context, produces the clause ``a / b``, and considers the result type to be numeric. .. versionchanged:: 2.0 The truediv operator against two integers is now considered to return a numeric value. Behavior on specific backends may vary.
sqlalchemy.sql.elements.SQLCoreOperationsProduce an :func:`_expression.all_` clause against the parent object. See the documentation for :func:`_sql.all_` for examples. .. note:: be sure to not confuse the newer :meth:`_sql.ColumnOperators.all_` method with its older :class:`_types.ARRAY`-specific counterpart, the :meth:`_types.ARRAY.Comparator.all` method, which a different calling syntax and usage pattern. .. versionadded:: 1.1
sqlalchemy.sql.elements.SQLCoreOperationsProduce an :func:`_expression.any_` clause against the parent object. See the documentation for :func:`_sql.any_` for examples. .. note:: be sure to not confuse the newer :meth:`_sql.ColumnOperators.any_` method with its older :class:`_types.ARRAY`-specific counterpart, the :meth:`_types.ARRAY.Comparator.any` method, which a different calling syntax and usage pattern. .. versionadded:: 1.1
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.asc` clause against the parent object.
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.between` clause against the parent object, given the lower and upper range.
Produce a bitwise AND operation, typically via the ``&`` operator. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
Produce a bitwise LSHIFT operation, typically via the ``<<`` operator. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
Produce a bitwise NOT operation, typically via the ``~`` operator. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
Produce a bitwise OR operation, typically via the ``|`` operator. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
Produce a bitwise RSHIFT operation, typically via the ``>>`` operator. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
Produce a bitwise XOR operation, typically via the ``^`` operator, or ``#`` for PostgreSQL. .. versionadded:: 2.0.2 .. seealso:: :ref:`operators_bitwise`
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.collate` clause against the parent object, given the collation string. .. seealso:: :func:`_expression.collate`
sqlalchemy.sql.elements.SQLCoreOperationsImplement the 'concat' operator. In a column context, produces the clause ``a || b``, or uses the ``concat()`` operator on MySQL.
sqlalchemy.dialects.postgresql.hstore.HSTORE.Comparator, sqlalchemy.dialects.postgresql.hstore.HSTORE.Comparator, sqlalchemy.dialects.postgresql.json.JSONB.Comparator, sqlalchemy.dialects.postgresql.json.JSONB.Comparator, sqlalchemy.dialects.postgresql.ranges.AbstractRange.comparator_factory, sqlalchemy.orm.properties.RelationshipProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperations, sqlalchemy.sql.sqltypes.ARRAY.Comparator, sqlalchemy.sql.sqltypes.ARRAY.ComparatorImplement 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 :paramref:`.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 :paramref:`.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. :param 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 :paramref:`.ColumnOperators.contains.autoescape` flag is set to True. :param 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"``. :param 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 :paramref:`.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. .. seealso:: :meth:`.ColumnOperators.startswith` :meth:`.ColumnOperators.endswith` :meth:`.ColumnOperators.like`
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.desc` clause against the parent object.
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.distinct` clause against the parent object.
Any, escape: Optional[ = None, autoescape: bool = False) -> ColumnOperators:
(source)
¶
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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 :paramref:`.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 :paramref:`.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. :param 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 :paramref:`.ColumnOperators.endswith.autoescape` flag is set to True. :param 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"``. :param 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 :paramref:`.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. .. seealso:: :meth:`.ColumnOperators.startswith` :meth:`.ColumnOperators.contains` :meth:`.ColumnOperators.like`
Implement the ``icontains`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.contains`. Produces a LIKE expression that tests against an insensitive match for the middle of a string value:: lower(column) LIKE '%' || lower(<other>) || '%' E.g.:: stmt = select(sometable).\ where(sometable.c.column.icontains("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 :paramref:`.ColumnOperators.icontains.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 :paramref:`.ColumnOperators.icontains.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. :param 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 :paramref:`.ColumnOperators.icontains.autoescape` flag is set to True. :param 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.icontains("foo%bar", autoescape=True) Will render as:: lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param 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.icontains("foo/%bar", escape="^") Will render as:: lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.contains.autoescape`:: somecolumn.icontains("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. .. seealso:: :meth:`.ColumnOperators.contains`
Any, escape: Optional[ = None, autoescape: bool = False) -> ColumnOperators:
(source)
¶
Implement the ``iendswith`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.endswith`. Produces a LIKE expression that tests against an insensitive match for the end of a string value:: lower(column) LIKE '%' || lower(<other>) E.g.:: stmt = select(sometable).\ where(sometable.c.column.iendswith("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 :paramref:`.ColumnOperators.iendswith.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 :paramref:`.ColumnOperators.iendswith.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. :param 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 :paramref:`.ColumnOperators.iendswith.autoescape` flag is set to True. :param 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.iendswith("foo%bar", autoescape=True) Will render as:: lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param 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.iendswith("foo/%bar", escape="^") Will render as:: lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.iendswith.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. .. seealso:: :meth:`.ColumnOperators.endswith`
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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:: a ILIKE other E.g.:: stmt = select(sometable).\ where(sometable.c.column.ilike("%foobar%")) :param other: expression to be compared :param escape: optional escape character, renders the ``ESCAPE`` keyword, e.g.:: somecolumn.ilike("foo/%bar", escape="/") .. seealso:: :meth:`.ColumnOperators.like`
sqlalchemy.orm.properties.RelationshipProperty.Comparator, sqlalchemy.sql.elements.SQLCoreOperationsImplement 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:: WHERE COL IN (?, ?, ?) * A list of tuples may be provided if the comparison is against a :func:`.tuple_` containing multiple expressions:: from sqlalchemy import tuple_ stmt.where(tuple_(col1, col2).in_([(1, 10), (2, 20), (3, 30)])) * An empty list, e.g.:: stmt.where(column.in_([])) In this calling form, the expression renders an "empty set" expression. These expressions are tailored to individual backends and are generally trying to get an empty SELECT statement as a subquery. Such as on SQLite, the expression is:: WHERE col IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1) .. versionchanged:: 1.4 empty IN expressions now use an execution-time generated SELECT subquery in all cases. * A bound parameter, e.g. :func:`.bindparam`, may be used if it includes the :paramref:`.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:: WHERE COL IN (?, ?, ?) .. versionadded:: 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) .. versionadded:: 1.3 "expanding" bound parameters now support empty lists * a :func:`_expression.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, :meth:`.ColumnOperators.in_` renders as given:: WHERE COL IN (SELECT othertable.y FROM othertable WHERE othertable.x = table.x) :param other: a list of literals, a :func:`_expression.select` construct, or a :func:`.bindparam` construct that includes the :paramref:`.bindparam.expanding` flag set to True.
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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. .. seealso:: :meth:`.ColumnOperators.is_not`
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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". .. versionadded:: 1.1
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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. .. versionchanged:: 1.4 The ``is_not()`` operator is renamed from ``isnot()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.is_`
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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". .. versionchanged:: 1.4 The ``is_not_distinct_from()`` operator is renamed from ``isnot_distinct_from()`` in previous releases. The previous name remains available for backwards compatibility. .. versionadded:: 1.1
Any, escape: Optional[ = None, autoescape: bool = False) -> ColumnOperators:
(source)
¶
Implement the ``istartswith`` operator, e.g. case insensitive version of :meth:`.ColumnOperators.startswith`. Produces a LIKE expression that tests against an insensitive match for the start of a string value:: lower(column) LIKE lower(<other>) || '%' E.g.:: stmt = select(sometable).\ where(sometable.c.column.istartswith("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 :paramref:`.ColumnOperators.istartswith.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 :paramref:`.ColumnOperators.istartswith.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. :param 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 :paramref:`.ColumnOperators.istartswith.autoescape` flag is set to True. :param 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.istartswith("foo%bar", autoescape=True) Will render as:: lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/' With the value of ``:param`` as ``"foo/%bar"``. :param 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.istartswith("foo/%bar", escape="^") Will render as:: lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^' The parameter may also be combined with :paramref:`.ColumnOperators.istartswith.autoescape`:: somecolumn.istartswith("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. .. seealso:: :meth:`.ColumnOperators.startswith`
sqlalchemy.sql.elements.SQLCoreOperationsImplement the ``like`` operator. In a column context, produces the expression:: a LIKE other E.g.:: stmt = select(sometable).\ where(sometable.c.column.like("%foobar%")) :param other: expression to be compared :param escape: optional escape character, renders the ``ESCAPE`` keyword, e.g.:: somecolumn.like("foo/%bar", escape="/") .. seealso:: :meth:`.ColumnOperators.ilike`
sqlalchemy.sql.elements.SQLCoreOperationsImplements a database-specific 'match' operator. :meth:`_sql.ColumnOperators.match` attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include: * PostgreSQL - renders ``x @@ plainto_tsquery(y)`` .. versionchanged:: 2.0 ``plainto_tsquery()`` is used instead of ``to_tsquery()`` for PostgreSQL now; for compatibility with other forms, see :ref:`postgresql_match`. * MySQL - renders ``MATCH (x) AGAINST (y IN BOOLEAN MODE)`` .. seealso:: :class:`_mysql.match` - MySQL specific construct with additional features. * 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.
sqlalchemy.sql.elements.SQLCoreOperationsimplement the ``NOT ILIKE`` operator. This is equivalent to using negation with :meth:`.ColumnOperators.ilike`, i.e. ``~x.ilike(y)``. .. versionchanged:: 1.4 The ``not_ilike()`` operator is renamed from ``notilike()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.ilike`
sqlalchemy.sql.elements.SQLCoreOperationsimplement the ``NOT IN`` operator. This is equivalent to using negation with :meth:`.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 :paramref:`_sa.create_engine.empty_in_strategy` may be used to alter this behavior. .. versionchanged:: 1.4 The ``not_in()`` operator is renamed from ``notin_()`` in previous releases. The previous name remains available for backwards compatibility. .. versionchanged:: 1.2 The :meth:`.ColumnOperators.in_` and :meth:`.ColumnOperators.not_in` operators now produce a "static" expression for an empty IN sequence by default. .. seealso:: :meth:`.ColumnOperators.in_`
sqlalchemy.sql.elements.SQLCoreOperationsimplement the ``NOT LIKE`` operator. This is equivalent to using negation with :meth:`.ColumnOperators.like`, i.e. ``~x.like(y)``. .. versionchanged:: 1.4 The ``not_like()`` operator is renamed from ``notlike()`` in previous releases. The previous name remains available for backwards compatibility. .. seealso:: :meth:`.ColumnOperators.like`
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.nulls_first` clause against the parent object. .. versionchanged:: 1.4 The ``nulls_first()`` operator is renamed from ``nullsfirst()`` in previous releases. The previous name remains available for backwards compatibility.
sqlalchemy.sql.elements.SQLCoreOperationsProduce a :func:`_expression.nulls_last` clause against the parent object. .. versionchanged:: 1.4 The ``nulls_last()`` operator is renamed from ``nullslast()`` in previous releases. The previous name remains available for backwards compatibility.
sqlalchemy.orm.interfaces.PropComparator, sqlalchemy.sql.elements.SQLCoreOperations, sqlalchemy.sql.lambdas.PyWrapper, sqlalchemy.sql.type_api.TypeEngine.Comparator, sqlalchemy.orm.evaluator._ExpiredObject, sqlalchemy.orm.evaluator._NoObjectOperate on an argument. This is the lowest level of operation, raises :class:`NotImplementedError` by default. Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding :class:`.ColumnOperators` to apply ``func.lower()`` to the left and right side:: class MyComparator(ColumnOperators): def operate(self, op, other, **kwargs): return op(func.lower(self), func.lower(other), **kwargs) :param op: Operator callable. :param \*other: the 'other' side of the operation. Will be a single scalar for most operations. :param \**kwargs: modifiers. These may be passed by special operators such as :meth:`ColumnOperators.contains`.
sqlalchemy.sql.elements.SQLCoreOperationsImplements a database-specific 'regexp match' operator. E.g.:: stmt = select(table.c.some_column).where( table.c.some_column.regexp_match('^(b|c)') ) :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to a REGEXP-like function or operator provided by the backend, however the specific regular expression syntax and flags available are **not backend agnostic**. Examples include: * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated. * Oracle - renders ``REGEXP_LIKE(x, y)`` * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into the Python ``re.match()`` builtin. * other backends may provide special implementations. * Backends without any special implementation will emit the operator as "REGEXP" or "NOT REGEXP". This is compatible with SQLite and MySQL, for example. Regular expression support is currently implemented for Oracle, PostgreSQL, MySQL and MariaDB. Partial support is available for SQLite. Support among third-party dialects may vary. :param pattern: The regular expression pattern string or column clause. :param flags: Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. When using the ignore case flag 'i' in PostgreSQL, the ignore case regexp match operator ``~*`` or ``!~*`` will be used. .. versionadded:: 1.4 .. seealso:: :meth:`_sql.ColumnOperators.regexp_replace`
Any, replacement: Any, flags: Optional[ = None) -> ColumnOperators:
(source)
¶
sqlalchemy.sql.elements.SQLCoreOperationsImplements a database-specific 'regexp replace' operator. E.g.:: stmt = select( table.c.some_column.regexp_replace( 'b(..)', 'XY', flags='g' ) ) :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to a REGEXP_REPLACE-like function provided by the backend, that usually emit the function ``REGEXP_REPLACE()``. However, the specific regular expression syntax and flags available are **not backend agnostic**. Regular expression replacement support is currently implemented for Oracle, PostgreSQL, MySQL 8 or greater and MariaDB. Support among third-party dialects may vary. :param pattern: The regular expression pattern string or column clause. :param pattern: The replacement string or column clause. :param flags: Any regular expression string flags to apply. Flags tend to be backend specific. It can be a string or a column clause. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern. .. versionadded:: 1.4 .. seealso:: :meth:`_sql.ColumnOperators.regexp_match`
OperatorType, other: Any, **kwargs: Any) -> ColumnOperators:
(source)
¶
sqlalchemy.orm.interfaces.PropComparator, sqlalchemy.sql.elements.SQLCoreOperations, sqlalchemy.sql.lambdas.PyWrapper, sqlalchemy.sql.type_api.TypeEngine.Comparator, sqlalchemy.orm.evaluator._ExpiredObject, sqlalchemy.orm.evaluator._NoObjectReverse operate on an argument. Usage is the same as :meth:`operate`.
Any, escape: Optional[ = None, autoescape: bool = False) -> ColumnOperators:
(source)
¶
sqlalchemy.sql.elements.SQLCoreOperationsImplement 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 :paramref:`.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 :paramref:`.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. :param 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 :paramref:`.ColumnOperators.startswith.autoescape` flag is set to True. :param 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"``. :param 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 :paramref:`.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. .. seealso:: :meth:`.ColumnOperators.endswith` :meth:`.ColumnOperators.contains` :meth:`.ColumnOperators.like`