scrachy.utils.sqltypes.TimeStampTZ

class scrachy.utils.sqltypes.TimeStampTZ(timezone: bool = False)[source]

Bases: TypeDecorator

A custom timestamp type that ensures all datetimes are timezone aware before entering or exiting the database.

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

__init__(timezone: bool = False)[source]

Construct a TypeDecorator.

Arguments sent here are passed to the constructor of the class assigned to the impl class level attribute, assuming the impl is a callable, and the resulting object is assigned to the self.impl instance attribute (thus overriding the class attribute of the same name).

If the class level impl is not a callable (the unusual case), it will be assigned to the same instance attribute ‘as-is’, ignoring those arguments passed to the constructor.

Subclasses can override this to customize the generation of self.impl entirely.

Methods

`__init__`([timezone])	Construct a `TypeDecorator`.
`adapt`(cls, **kw)	Produce an "adapted" form of this type, given an "impl" class to work with.
`as_generic`([allow_nulltype])	Return an instance of the generic type corresponding to this type using heuristic rule.
`bind_expression`(bindparam)	Given a bind value (i.e. a `BindParameter` instance), return a SQL expression which will typically wrap the given parameter.
`bind_processor`(dialect)	Provide a bound value processing function for the given `Dialect`.
`coerce_compared_value`(op, value)	Suggest a type for a 'coerced' Python value in an expression.
`column_expression`(column)	Given a SELECT column expression, return a wrapping SQL expression.
`compare_values`(x, y)	Given two values, compare them for equality.
`compile`([dialect])	Produce a string-compiled form of this `TypeEngine`.
`copy`(**kw)	Produce a copy of this `TypeDecorator` instance.
`copy_value`(value)
`dialect_impl`(dialect)	Return a dialect-specific implementation for this `TypeEngine`.
`evaluates_none`()	Return a copy of this type which has the `should_evaluate_none` flag set to True.
`get_dbapi_type`(dbapi)	Return the DBAPI type object represented by this `TypeDecorator`.
`literal_processor`(dialect)	Provide a literal processing function for the given `Dialect`.
`load_dialect_impl`(dialect)	Return a `TypeEngine` object corresponding to a dialect.
`process_bind_param`(value, dialect)	Receive a bound parameter value to be converted.
`process_literal_param`(value, dialect)	Receive a literal parameter value to be rendered inline within a statement.
`process_result_value`(value, dialect)	Receive a result-row column value to be converted.
`result_processor`(dialect, coltype)	Provide a result value processing function for the given `Dialect`.
`type_engine`(dialect)	Return a dialect-specific `TypeEngine` instance for this `TypeDecorator`.
`with_variant`(type_, *dialect_names)	Produce a copy of this type object that will utilize the given type when applied to the dialect of the given name.

Attributes

`cache_ok`	Indicate if statements using this `ExternalType` are "safe to cache".
`coerce_to_is_types`	Specify those Python types which should be coerced at the expression level to "IS <constant>" when compared using `==` (and same for `IS NOT` in conjunction with `!=`).
`comparator_factory`	Base class for custom comparison operations defined at the type level.
`dispatch`
`hashable`	Flag, if False, means values from this type aren't hashable.
`impl_instance`
`python_type`	Return the Python type object expected to be returned by instances of this type, if known.
`render_bind_cast`	Render bind casts for `BindTyping.RENDER_CASTS` mode.
`render_literal_cast`	render casts when rendering a value as an inline literal, e.g. with `TypeEngine.literal_processor()`.
`should_evaluate_none`	If True, the Python constant `None` is considered to be handled explicitly by this type.
`sort_key_function`

class Comparator(expr: ColumnElement[_CT])

Bases: Comparator[_CT]

A TypeEngine.Comparator that is specific to TypeDecorator.

User-defined TypeDecorator classes should not typically need to modify this.

all_() → ColumnOperators

Produce an _expression.all_() clause against the parent object.

See the documentation for _sql.all_() for examples.

Note

be sure to not confuse the newer _sql.ColumnOperators.all_() method with its older _types.ARRAY-specific counterpart, the _types.ARRAY.Comparator.all() method, which a different calling syntax and usage pattern.

any_() → ColumnOperators

Produce an _expression.any_() clause against the parent object.

See the documentation for _sql.any_() for examples.

Note

be sure to not confuse the newer _sql.ColumnOperators.any_() method with its older _types.ARRAY-specific counterpart, the _types.ARRAY.Comparator.any() method, which a different calling syntax and usage pattern.

asc() → ColumnOperators: Produce a _expression.asc() clause against the parent object.

between(cleft: Any, cright: Any, symmetric: bool = False) → ColumnOperators: Produce a _expression.between() clause against the parent object, given the lower and upper range.

bitwise_and(other: Any) → ColumnOperators: Produce a bitwise AND operation, typically via the & operator.

New in version 2.0.2.

See also

Bitwise Operators

bitwise_lshift(other: Any) → ColumnOperators: Produce a bitwise LSHIFT operation, typically via the << operator.

New in version 2.0.2.

See also

Bitwise Operators

bitwise_not() → ColumnOperators: Produce a bitwise NOT operation, typically via the ~ operator.

New in version 2.0.2.

See also

Bitwise Operators

bitwise_or(other: Any) → ColumnOperators: Produce a bitwise OR operation, typically via the | operator.

New in version 2.0.2.

See also

Bitwise Operators

bitwise_rshift(other: Any) → ColumnOperators: Produce a bitwise RSHIFT operation, typically via the >> operator.

New in version 2.0.2.

See also

Bitwise Operators

bitwise_xor(other: Any) → ColumnOperators: Produce a bitwise XOR operation, typically via the ^ operator, or # for PostgreSQL.

New in version 2.0.2.

See also

Bitwise Operators

bool_op(opstring: str, precedence: int = 0, python_impl: Callable[[...], Any] | None = None) → Callable[[Any], Operators]

Return a custom boolean operator.

This method is shorthand for calling Operators.op() and passing the :paramref:`.Operators.op.is_comparison` flag with True. A key advantage to using Operators.bool_op() is that when using column constructs, the “boolean” nature of the returned expression will be present for PEP 484 purposes.

See also

Operators.op()

collate(collation: str) → ColumnOperators: Produce a _expression.collate() clause against the parent object, given the collation string.

See also

_expression.collate()

concat(other: Any) → ColumnOperators

Implement the ‘concat’ operator.

In a column context, produces the clause a || b, or uses the concat() operator on MySQL.

contains(other: Any, **kw: Any) → ColumnOperators

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 :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.

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 :paramref:`.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 :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.

See also

ColumnOperators.startswith()

ColumnOperators.endswith()

ColumnOperators.like()

desc() → ColumnOperators: Produce a _expression.desc() clause against the parent object.

distinct() → ColumnOperators: Produce a _expression.distinct() clause against the parent object.

endswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

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 :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.

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 :paramref:`.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 :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.

See also

ColumnOperators.startswith()

ColumnOperators.contains()

ColumnOperators.like()

icontains(other: Any, **kw: Any) → ColumnOperators

Implement the icontains operator, e.g. case insensitive version of 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.

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 :paramref:`.ColumnOperators.icontains.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.icontains("foo%bar", autoescape=True)
```
Will render as:
```
lower(somecolumn) LIKE '%' || lower(: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.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.

See also

ColumnOperators.contains()

iendswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

Implement the iendswith operator, e.g. case insensitive version of 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.

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 :paramref:`.ColumnOperators.iendswith.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.iendswith("foo%bar", autoescape=True)
```
Will render as:
```
lower(somecolumn) LIKE '%' || lower(: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.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.

See also

ColumnOperators.endswith()

ilike(other: Any, escape: str | None = None) → ColumnOperators

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:

a ILIKE other

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="/")
```

See also

ColumnOperators.like()

in_(other: Any) → ColumnOperators

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:
```
WHERE COL IN (?, ?, ?)
```

A list of tuples may be provided if the comparison is against a 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)
```
Changed in version 1.4: empty IN expressions now use an execution-time generated SELECT subquery in all cases.
A bound parameter, e.g. 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 (?, ?, ?)
```
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 _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, 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 _expression.select() construct, or a bindparam() construct that includes the :paramref:`.bindparam.expanding` flag set to True.

is_(other: Any) → ColumnOperators

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.

See also

ColumnOperators.is_not()

is_distinct_from(other: Any) → ColumnOperators

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

is_not(other: Any) → ColumnOperators

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.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

is_not_distinct_from(other: Any) → ColumnOperators

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

Changed in version 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.

isnot(other: Any) → ColumnOperators

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.

Changed in version 1.4: The is_not() operator is renamed from isnot() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.is_()

isnot_distinct_from(other: Any) → ColumnOperators

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

Changed in version 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.

istartswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

Implement the istartswith operator, e.g. case insensitive version of 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.

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 :paramref:`.ColumnOperators.istartswith.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.istartswith("foo%bar", autoescape=True)
```
Will render as:
```
lower(somecolumn) LIKE lower(: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.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.

See also

ColumnOperators.startswith()

like(other: Any, escape: str | None = None) → ColumnOperators

Implement the like operator.

In a column context, produces the expression:

a LIKE other

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="/")
```

See also

ColumnOperators.ilike()

match(other: Any, **kwargs: Any) → ColumnOperators

Implements a database-specific ‘match’ operator.

_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)

Changed in version 2.0: plainto_tsquery() is used instead of to_tsquery() for PostgreSQL now; for compatibility with other forms, see Full Text Search.
MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)

See also

_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.

not_ilike(other: Any, escape: str | None = None) → ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

not_in(other: Any) → ColumnOperators

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 :paramref:`_sa.create_engine.empty_in_strategy` may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

not_like(other: Any, escape: str | None = None) → ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

notilike(other: Any, escape: str | None = None) → ColumnOperators

implement the NOT ILIKE operator.

This is equivalent to using negation with ColumnOperators.ilike(), i.e. ~x.ilike(y).

Changed in version 1.4: The not_ilike() operator is renamed from notilike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.ilike()

notin_(other: Any) → ColumnOperators

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 :paramref:`_sa.create_engine.empty_in_strategy` may be used to alter this behavior.

Changed in version 1.4: The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

Changed in version 1.2: The ColumnOperators.in_() and ColumnOperators.not_in() operators now produce a “static” expression for an empty IN sequence by default.

See also

ColumnOperators.in_()

notlike(other: Any, escape: str | None = None) → ColumnOperators

implement the NOT LIKE operator.

This is equivalent to using negation with ColumnOperators.like(), i.e. ~x.like(y).

Changed in version 1.4: The not_like() operator is renamed from notlike() in previous releases. The previous name remains available for backwards compatibility.

See also

ColumnOperators.like()

nulls_first() → ColumnOperators: Produce a _expression.nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

nulls_last() → ColumnOperators: Produce a _expression.nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

nullsfirst() → ColumnOperators: Produce a _expression.nulls_first() clause against the parent object.

Changed in version 1.4: The nulls_first() operator is renamed from nullsfirst() in previous releases. The previous name remains available for backwards compatibility.

nullslast() → ColumnOperators: Produce a _expression.nulls_last() clause against the parent object.

Changed in version 1.4: The nulls_last() operator is renamed from nullslast() in previous releases. The previous name remains available for backwards compatibility.

op(opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Type[TypeEngine[Any]] | TypeEngine[Any] | None = None, python_impl: Callable[..., Any] | None = None) → Callable[[Any], Operators]

Produce a generic operator function.

e.g.:

somecolumn.op("*")(5)

produces:

somecolumn * 5

This function can also be used to make bitwise operators explicit. For example:

somecolumn.op('&')(0xff)

is a bitwise AND of the value in somecolumn.

Parameters:

opstring – a string which will be output as the infix operator between this element and the expression passed to the generated function.
precedence –
precedence which the database is expected to apply to the operator in SQL expressions. This integer value acts as a hint for the SQL compiler to know when explicit parenthesis should be rendered around a particular operation. 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.

See also

I’m using op() to generate a custom operator and my parenthesis are not coming out correctly - detailed description of how the SQLAlchemy SQL compiler renders parenthesis
is_comparison –
legacy; 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 is provided so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition.

Using the is_comparison parameter is superseded by using the Operators.bool_op() method instead; this more succinct operator sets this parameter automatically, but also provides correct PEP 484 typing support as the returned object will express a “boolean” datatype, i.e. BinaryExpression[bool].
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 :paramref:`.Operators.op.is_comparison` will resolve to Boolean, and those that do not will be of the same type as the left-hand operand.
python_impl –
an optional Python function that can evaluate two Python values in the same way as this operator works when run on the database server. Useful for in-Python SQL expression evaluation functions, such as for ORM hybrid attributes, and the ORM “evaluator” used to match objects in a session after a multi-row update or delete.

e.g.:
```
>>> expr = column('x').op('+', python_impl=lambda a, b: a + b)('y')
```
The operator for the above expression will also work for non-SQL left and right objects:
```
>>> expr.operator(5, 10)
15
```
New in version 2.0.

See also

Operators.bool_op()

Redefining and Creating New Operators

Using custom operators in join conditions

operate(op: OperatorType, *other: Any, **kwargs: Any) → ColumnElement[_CT]

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, **kwargs):
        return op(func.lower(self), func.lower(other), **kwargs)

Parameters:

op – Operator callable.
*other – the ‘other’ side of the operation. Will be a single scalar for most operations.
**kwargs – modifiers. These may be passed by special operators such as ColumnOperators.contains().

regexp_match(pattern: Any, flags: str | None = None) → ColumnOperators

Implements a database-specific ‘regexp match’ operator.

E.g.:

stmt = select(table.c.some_column).where(
    table.c.some_column.regexp_match('^(b|c)')
)

_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.

Parameters:

pattern – The regular expression pattern string or column clause.
flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. 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.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

_sql.ColumnOperators.regexp_replace()

regexp_replace(pattern: Any, replacement: Any, flags: str | None = None) → ColumnOperators

Implements a database-specific ‘regexp replace’ operator.

E.g.:

stmt = select(
    table.c.some_column.regexp_replace(
        'b(..)',
        'XY',
        flags='g'
    )
)

_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.

Parameters:

pattern – The regular expression pattern string or column clause.
pattern – The replacement string or column clause.
flags – Any regular expression string flags to apply, passed as plain Python string only. These flags are backend specific. Some backends, like PostgreSQL and MariaDB, may alternatively specify the flags as part of the pattern.

New in version 1.4.

Changed in version 1.4.48,: 2.0.18 Note that due to an implementation error, the “flags” parameter previously accepted SQL expression objects such as column expressions in addition to plain Python strings. This implementation did not work correctly with caching and was removed; strings only should be passed for the “flags” parameter, as these flags are rendered as literal inline values within SQL expressions.

See also

_sql.ColumnOperators.regexp_match()

reverse_operate(op: OperatorType, other: Any, **kwargs: Any) → ColumnElement[_CT]

Reverse operate on an argument.

Usage is the same as operate().

startswith(other: Any, escape: str | None = None, autoescape: bool = False) → ColumnOperators

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 :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.

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 :paramref:`.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 :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.

See also

ColumnOperators.endswith()

ColumnOperators.contains()

ColumnOperators.like()

timetuple: Literal[None] = None: Hack, allows datetime objects to be compared on the LHS.

adapt(cls: Type[TypeEngine | TypeEngineMixin], **kw: Any) → TypeEngine

Produce an “adapted” form of this type, given an “impl” class to work with.

This method is used internally to associate generic types with “implementation” types that are specific to a particular dialect.

as_generic(allow_nulltype: bool = False) → TypeEngine

Return an instance of the generic type corresponding to this type using heuristic rule. The method may be overridden if this heuristic rule is not sufficient.

>>> from sqlalchemy.dialects.mysql import INTEGER
>>> INTEGER(display_width=4).as_generic()
Integer()

>>> from sqlalchemy.dialects.mysql import NVARCHAR
>>> NVARCHAR(length=100).as_generic()
Unicode(length=100)

New in version 1.4.0b2.

See also

Reflecting with Database-Agnostic Types - describes the use of _types.TypeEngine.as_generic() in conjunction with the _sql.DDLEvents.column_reflect() event, which is its intended use.

bind_expression(bindparam: BindParameter[_T]) → ColumnElement[_T] | None

Given a bind value (i.e. a BindParameter instance), return a SQL expression which will typically wrap the given parameter.

Note

This method is called during the SQL compilation phase of a statement, when rendering a SQL string. It is not necessarily called against specific values, and should not be confused with the _types.TypeDecorator.process_bind_param() method, which is the more typical method that processes the actual value passed to a particular parameter at statement execution time.

Subclasses of _types.TypeDecorator can override this method to provide custom bind expression behavior for the type. This implementation will replace that of the underlying implementation type.

bind_processor(dialect: Dialect) → _BindProcessorType[_T] | None

Provide a bound value processing function for the given Dialect.

This is the method that fulfills the TypeEngine contract for bound value conversion which normally occurs via the _types.TypeEngine.bind_processor() method.

Note

User-defined subclasses of _types.TypeDecorator should not implement this method, and should instead implement _types.TypeDecorator.process_bind_param() so that the “inner” processing provided by the implementing type is maintained.

Parameters:: dialect – Dialect instance in use.

cache_ok: bool | None = True

Indicate if statements using this ExternalType are “safe to cache”.

The default value None will emit a warning and then not allow caching of a statement which includes this type. Set to False to disable statements using this type from being cached at all without a warning. When set to True, the object’s class and selected elements from its state will be used as part of the cache key. For example, using a TypeDecorator:

class MyType(TypeDecorator):
    impl = String

    cache_ok = True

    def __init__(self, choices):
        self.choices = tuple(choices)
        self.internal_only = True

The cache key for the above type would be equivalent to:

>>> MyType(["a", "b", "c"])._static_cache_key
(<class '__main__.MyType'>, ('choices', ('a', 'b', 'c')))

The caching scheme will extract attributes from the type that correspond to the names of parameters in the __init__() method. Above, the “choices” attribute becomes part of the cache key but “internal_only” does not, because there is no parameter named “internal_only”.

The requirements for cacheable elements is that they are hashable and also that they indicate the same SQL rendered for expressions using this type every time for a given cache value.

To accommodate for datatypes that refer to unhashable structures such as dictionaries, sets and lists, these objects can be made “cacheable” by assigning hashable structures to the attributes whose names correspond with the names of the arguments. For example, a datatype which accepts a dictionary of lookup values may publish this as a sorted series of tuples. Given a previously un-cacheable type as:

class LookupType(UserDefinedType):
    '''a custom type that accepts a dictionary as a parameter.

    this is the non-cacheable version, as "self.lookup" is not
    hashable.

    '''

    def __init__(self, lookup):
        self.lookup = lookup

    def get_col_spec(self, **kw):
        return "VARCHAR(255)"

    def bind_processor(self, dialect):
        # ...  works with "self.lookup" ...

Where “lookup” is a dictionary. The type will not be able to generate a cache key:

>>> type_ = LookupType({"a": 10, "b": 20})
>>> type_._static_cache_key
<stdin>:1: SAWarning: UserDefinedType LookupType({'a': 10, 'b': 20}) will not
produce a cache key because the ``cache_ok`` flag is not set to True.
Set this flag to True if this type object's state is safe to use
in a cache key, or False to disable this warning.
symbol('no_cache')

If we did set up such a cache key, it wouldn’t be usable. We would get a tuple structure that contains a dictionary inside of it, which cannot itself be used as a key in a “cache dictionary” such as SQLAlchemy’s statement cache, since Python dictionaries aren’t hashable:

>>> # set cache_ok = True
>>> type_.cache_ok = True

>>> # this is the cache key it would generate
>>> key = type_._static_cache_key
>>> key
(<class '__main__.LookupType'>, ('lookup', {'a': 10, 'b': 20}))

>>> # however this key is not hashable, will fail when used with
>>> # SQLAlchemy statement cache
>>> some_cache = {key: "some sql value"}
Traceback (most recent call last): File "<stdin>", line 1,
in <module> TypeError: unhashable type: 'dict'

The type may be made cacheable by assigning a sorted tuple of tuples to the “.lookup” attribute:

class LookupType(UserDefinedType):
    '''a custom type that accepts a dictionary as a parameter.

    The dictionary is stored both as itself in a private variable,
    and published in a public variable as a sorted tuple of tuples,
    which is hashable and will also return the same value for any
    two equivalent dictionaries.  Note it assumes the keys and
    values of the dictionary are themselves hashable.

    '''

    cache_ok = True

    def __init__(self, lookup):
        self._lookup = lookup

        # assume keys/values of "lookup" are hashable; otherwise
        # they would also need to be converted in some way here
        self.lookup = tuple(
            (key, lookup[key]) for key in sorted(lookup)
        )

    def get_col_spec(self, **kw):
        return "VARCHAR(255)"

    def bind_processor(self, dialect):
        # ...  works with "self._lookup" ...

Where above, the cache key for LookupType({"a": 10, "b": 20}) will be:

>>> LookupType({"a": 10, "b": 20})._static_cache_key
(<class '__main__.LookupType'>, ('lookup', (('a', 10), ('b', 20))))

New in version 1.4.14: - added the cache_ok flag to allow some configurability of caching for TypeDecorator classes.

New in version 1.4.28: - added the ExternalType mixin which generalizes the cache_ok flag to both the TypeDecorator and UserDefinedType classes.

See also

SQL Compilation Caching

coerce_compared_value(op: OperatorType | None, value: Any) → Any

Suggest a type for a ‘coerced’ Python value in an expression.

By default, returns self. This method is called by the expression system when an object using this type is on the left or right side of an expression against a plain Python object which does not yet have a SQLAlchemy type assigned:

expr = table.c.somecolumn + 35

Where above, if somecolumn uses this type, this method will be called with the value operator.add and 35. The return value is whatever SQLAlchemy type should be used for 35 for this particular operation.

coerce_to_is_types: Sequence[Type[Any]] = (<class 'NoneType'>,)

Specify those Python types which should be coerced at the expression level to “IS <constant>” when compared using == (and same for IS NOT in conjunction with !=).

For most SQLAlchemy types, this includes NoneType, as well as bool.

TypeDecorator modifies this list to only include NoneType, as typedecorator implementations that deal with boolean types are common.

Custom TypeDecorator classes can override this attribute to return an empty tuple, in which case no values will be coerced to constants.

column_expression(column: ColumnElement[_T]) → ColumnElement[_T] | None

Given a SELECT column expression, return a wrapping SQL expression.

Note

This method is called during the SQL compilation phase of a statement, when rendering a SQL string. It is not called against specific values, and should not be confused with the _types.TypeDecorator.process_result_value() method, which is the more typical method that processes the actual value returned in a result row subsequent to statement execution time.

Subclasses of _types.TypeDecorator can override this method to provide custom column expression behavior for the type. This implementation will replace that of the underlying implementation type.

See the description of _types.TypeEngine.column_expression() for a complete description of the method’s use.

property comparator_factory: _ComparatorFactory[Any]: Base class for custom comparison operations defined at the type level. See TypeEngine.comparator_factory.

compare_values(x: Any, y: Any) → bool

Given two values, compare them for equality.

By default this calls upon TypeEngine.compare_values() of the underlying “impl”, which in turn usually uses the Python equals operator ==.

This function is used by the ORM to compare an original-loaded value with an intercepted “changed” value, to determine if a net change has occurred.

compile(dialect: Dialect | None = None) → str

Produce a string-compiled form of this TypeEngine.

When called with no arguments, uses a “default” dialect to produce a string result.

Parameters:: dialect – a Dialect instance.

copy(**kw: Any) → Self

Produce a copy of this TypeDecorator instance.

This is a shallow copy and is provided to fulfill part of the TypeEngine contract. It usually does not need to be overridden unless the user-defined TypeDecorator has local state that should be deep-copied.

dialect_impl(dialect: Dialect) → TypeEngine[_T]: Return a dialect-specific implementation for this TypeEngine.

evaluates_none() → Self

Return a copy of this type which has the should_evaluate_none flag set to True.

E.g.:

Table(
    'some_table', metadata,
    Column(
        String(50).evaluates_none(),
        nullable=True,
        server_default='no value')
)

The ORM uses this flag to indicate that a positive value of None is passed to the column in an INSERT statement, rather than omitting the column from the INSERT statement which has the effect of firing off column-level defaults. It also allows for types which have special behavior associated with the Python None value to indicate that the value doesn’t necessarily translate into SQL NULL; a prime example of this is a JSON type which may wish to persist the JSON value 'null'.

In all cases, the actual NULL SQL value can be always be persisted in any column by using the _expression.null SQL construct in an INSERT statement or associated with an ORM-mapped attribute.

Note

The “evaluates none” flag does not apply to a value of None passed to :paramref:`_schema.Column.default` or :paramref:`_schema.Column.server_default`; in these cases, None still means “no default”.

See also

Forcing NULL on a column with a default - in the ORM documentation

:paramref:`.postgresql.JSON.none_as_null` - PostgreSQL JSON interaction with this flag.

TypeEngine.should_evaluate_none - class-level flag

get_dbapi_type(dbapi: module) → Any | None

Return the DBAPI type object represented by this TypeDecorator.

By default this calls upon TypeEngine.get_dbapi_type() of the underlying “impl”.

hashable = True

Flag, if False, means values from this type aren’t hashable.

Used by the ORM when uniquing result lists.

impl: alias of TIMESTAMP

literal_processor(dialect: Dialect) → _LiteralProcessorType[_T] | None

Provide a literal processing function for the given Dialect.

This is the method that fulfills the TypeEngine contract for literal value conversion which normally occurs via the _types.TypeEngine.literal_processor() method.

Note

User-defined subclasses of _types.TypeDecorator should not implement this method, and should instead implement _types.TypeDecorator.process_literal_param() so that the “inner” processing provided by the implementing type is maintained.

load_dialect_impl(dialect: Dialect) → TypeEngine[Any]

Return a TypeEngine object corresponding to a dialect.

This is an end-user override hook that can be used to provide differing types depending on the given dialect. It is used by the TypeDecorator implementation of type_engine() to help determine what type should ultimately be returned for a given TypeDecorator.

By default returns self.impl.

process_bind_param(value: <module 'datetime' from '/home/docs/.asdf/installs/python/3.11.6/lib/python3.11/datetime.py'>, dialect)[source]

Receive a bound parameter value to be converted.

Custom subclasses of _types.TypeDecorator should override this method to provide custom behaviors for incoming data values. This method is called at statement execution time and is passed the literal Python data value which is to be associated with a bound parameter in the statement.

The operation could be anything desired to perform custom behavior, such as transforming or serializing data. This could also be used as a hook for validating logic.

Parameters:

value – Data to operate upon, of any type expected by this method in the subclass. Can be None.
dialect – the Dialect in use.

See also

Augmenting Existing Types

_types.TypeDecorator.process_result_value()

process_literal_param(value, dialect)[source]

Receive a literal parameter value to be rendered inline within a statement.

Note

This method is called during the SQL compilation phase of a statement, when rendering a SQL string. Unlike other SQL compilation methods, it is passed a specific Python value to be rendered as a string. However it should not be confused with the _types.TypeDecorator.process_bind_param() method, which is the more typical method that processes the actual value passed to a particular parameter at statement execution time.

Custom subclasses of _types.TypeDecorator should override this method to provide custom behaviors for incoming data values that are in the special case of being rendered as literals.

The returned string will be rendered into the output string.

process_result_value(value, dialect)[source]

Receive a result-row column value to be converted.

Custom subclasses of _types.TypeDecorator should override this method to provide custom behaviors for data values being received in result rows coming from the database. This method is called at result fetching time and is passed the literal Python data value that’s extracted from a database result row.

The operation could be anything desired to perform custom behavior, such as transforming or deserializing data.

Parameters:

value – Data to operate upon, of any type expected by this method in the subclass. Can be None.
dialect – the Dialect in use.

See also

Augmenting Existing Types

_types.TypeDecorator.process_bind_param()

property python_type

Return the Python type object expected to be returned by instances of this type, if known.

Basically, for those types which enforce a return type, or are known across the board to do such for all common DBAPIs (like int for example), will return that type.

If a return type is not defined, raises NotImplementedError.

Note that any type also accommodates NULL in SQL which means you can also get back None from any type in practice.

render_bind_cast = False

Render bind casts for BindTyping.RENDER_CASTS mode.

If True, this type (usually a dialect level impl type) signals to the compiler that a cast should be rendered around a bound parameter for this type.

New in version 2.0.

See also

BindTyping

render_literal_cast = False: render casts when rendering a value as an inline literal, e.g. with TypeEngine.literal_processor().

New in version 2.0.

result_processor(dialect: Dialect, coltype: Any) → _ResultProcessorType[_T] | None

Provide a result value processing function for the given Dialect.

This is the method that fulfills the TypeEngine contract for bound value conversion which normally occurs via the _types.TypeEngine.result_processor() method.

Note

User-defined subclasses of _types.TypeDecorator should not implement this method, and should instead implement _types.TypeDecorator.process_result_value() so that the “inner” processing provided by the implementing type is maintained.

Parameters:

dialect – Dialect instance in use.
coltype – A SQLAlchemy data type

should_evaluate_none: bool = False

If True, the Python constant None is considered to be handled explicitly by this type.

The ORM uses this flag to indicate that a positive value of None is passed to the column in an INSERT statement, rather than omitting the column from the INSERT statement which has the effect of firing off column-level defaults. It also allows types which have special behavior for Python None, such as a JSON type, to indicate that they’d like to handle the None value explicitly.

To set this flag on an existing type, use the TypeEngine.evaluates_none() method.

See also

TypeEngine.evaluates_none()

property sort_key_function: Callable[[Any], Any] | None

type_engine(dialect: Dialect) → TypeEngine[Any]

Return a dialect-specific TypeEngine instance for this TypeDecorator.

In most cases this returns a dialect-adapted form of the TypeEngine type represented by self.impl. Makes usage of dialect_impl(). Behavior can be customized here by overriding load_dialect_impl().

with_variant(type_: _TypeEngineArgument[Any], *dialect_names: str) → Self

Produce a copy of this type object that will utilize the given type when applied to the dialect of the given name.

e.g.:

from sqlalchemy.types import String
from sqlalchemy.dialects import mysql

string_type = String()

string_type = string_type.with_variant(
    mysql.VARCHAR(collation='foo'), 'mysql', 'mariadb'
)

The variant mapping indicates that when this type is interpreted by a specific dialect, it will instead be transmuted into the given type, rather than using the primary type.

Changed in version 2.0: the _types.TypeEngine.with_variant() method now works with a _types.TypeEngine object “in place”, returning a copy of the original type rather than returning a wrapping object; the Variant class is no longer used.

Parameters:

type_ – a TypeEngine that will be selected as a variant from the originating type, when a dialect of the given name is in use.
*dialect_names –
one or more base names of the dialect which uses this type. (i.e. 'postgresql', 'mysql', etc.)

Changed in version 2.0: multiple dialect names can be specified for one variant.

See also

Using “UPPERCASE” and Backend-specific types for multiple backends - illustrates the use of _types.TypeEngine.with_variant().