Custom Types — SQLAlchemy 1.2 Documentation

Release: 1.2.8 | Release Date: May 28, 2018

# SQLAlchemy 1.2 Documentation

# Custom Types

A variety of methods exist to redefine the behavior of existing types as well as to provide new ones.

## Overriding Type Compilation

A frequent need is to force the “string” version of a type, that is the one rendered in a CREATE TABLE statement or other SQL function like CAST, to be changed. For example, an application may want to force the rendering of `BINARY` for all platforms except for one, in which is wants `BLOB` to be rendered. Usage of an existing generic type, in this case `LargeBinary`, is preferred for most use cases. But to control types more accurately, a compilation directive that is per-dialect can be associated with any type:

    from sqlalchemy.ext.compiler import compiles
    from sqlalchemy.types import BINARY

    @compiles(BINARY, "sqlite")
    def compile_binary_sqlite(type_, compiler, **kw):
        return "BLOB"

The above code allows the usage of `types.BINARY`, which will produce the string `BINARY` against all backends except SQLite, in which case it will produce `BLOB`.

See the section Changing Compilation of Types, a subsection of Custom SQL Constructs and Compilation Extension, for additional examples.

## Augmenting Existing Types

The `TypeDecorator` allows the creation of custom types which add bind-parameter and result-processing behavior to an existing type object. It is used when additional in-Python marshaling of data to and from the database is required.

Note

The bind- and result-processing of `TypeDecorator` is in addition to the processing already performed by the hosted type, which is customized by SQLAlchemy on a per-DBAPI basis to perform processing specific to that DBAPI. To change the DBAPI-level processing for an existing type, see the section Replacing the Bind/Result Processing of Existing Types.

class `sqlalchemy.types.` `TypeDecorator`(*args, **kwargs)

  Bases: `sqlalchemy.sql.expression.SchemaEventTarget`, `sqlalchemy.types.TypeEngine`

  Allows the creation of types which add additional functionality to an existing type.

  This method is preferred to direct subclassing of SQLAlchemy’s built-in types as it ensures that all required functionality of the underlying type is kept in place.

  Typical usage:

      import sqlalchemy.types as types

      class MyType(types.TypeDecorator):
          '''Prefixes Unicode values with "PREFIX:" on the way in and
          strips it off on the way out.
          '''

          impl = types.Unicode

          def process_bind_param(self, value, dialect):
              return "PREFIX:" + value

          def process_result_value(self, value, dialect):
              return value[7:]

          def copy(self, **kw):
              return MyType(self.impl.length)

  The class-level “impl” attribute is required, and can reference any TypeEngine class. Alternatively, the load_dialect_impl() method can be used to provide different type classes based on the dialect given; in this case, the “impl” variable can reference `TypeEngine` as a placeholder.

  Types that receive a Python type that isn’t similar to the ultimate type used may want to define the `TypeDecorator.coerce_compared_value()` method. This is used to give the expression system a hint when coercing Python objects into bind parameters within expressions. Consider this expression:

      mytable.c.somecol + datetime.date(2009, 5, 15)

  Above, if “somecol” is an `Integer` variant, it makes sense that we’re doing date arithmetic, where above is usually interpreted by databases as adding a number of days to the given date. The expression system does the right thing by not attempting to coerce the “date()” value into an integer-oriented bind parameter.

  However, in the case of `TypeDecorator`, we are usually changing an incoming Python type to something new - `TypeDecorator` by default will “coerce” the non-typed side to be the same type as itself. Such as below, we define an “epoch” type that stores a date value as an integer:

      class MyEpochType(types.TypeDecorator):
          impl = types.Integer

          epoch = datetime.date(1970, 1, 1)

          def process_bind_param(self, value, dialect):
              return (value - self.epoch).days

          def process_result_value(self, value, dialect):
              return self.epoch + timedelta(days=value)

  Our expression of `somecol + date` with the above type will coerce the “date” on the right side to also be treated as `MyEpochType`.

  This behavior can be overridden via the `coerce_compared_value()` method, which returns a type that should be used for the value of the expression. Below we set it such that an integer value will be treated as an `Integer`, and any other value is assumed to be a date and will be treated as a `MyEpochType`:

      def coerce_compared_value(self, op, value):
          if isinstance(value, int):
              return Integer()
          else:
              return self

  Warning

  Note that the behavior of coerce_compared_value is not inherited by default from that of the base type. If the `TypeDecorator` is augmenting a type that requires special logic for certain types of operators, this method must be overridden. A key example is when decorating the `postgresql.JSON` and `postgresql.JSONB` types; the default rules of `TypeEngine.coerce_compared_value()` should be used in order to deal with operators like index operations:

      class MyJsonType(TypeDecorator):
          impl = postgresql.JSON

          def coerce_compared_value(self, op, value):
              return self.impl.coerce_compared_value(op, value)

  Without the above step, index operations such as `mycol['foo']` will cause the index value `'foo'` to be JSON encoded.

  class `Comparator`(expr)

    Bases: `sqlalchemy.types.Comparator`

    `__eq__`(other)
      inherited from the `__eq__()` method of `ColumnOperators`

      Implement the `==` operator.

      In a column context, produces the clause `a = b`. If the target is `None`, produces `a IS NULL`.

    `__le__`(other)
      inherited from the `__le__()` method of `ColumnOperators`

      Implement the `<=` operator.

      In a column context, produces the clause `a <= b`.

    `__lt__`(other)
      inherited from the `__lt__()` method of `ColumnOperators`

      Implement the `<` operator.

      In a column context, produces the clause `a < b`.

    `__ne__`(other)
      inherited from the `__ne__()` method of `ColumnOperators`

      Implement the `!=` operator.

      In a column context, produces the clause `a != b`. If the target is `None`, produces `a IS NOT NULL`.

    `all_`()
      inherited from the `all_()` method of `ColumnOperators`

      Produce a `all_()` clause against the parent object.

      This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:

          # postgresql '5 = ALL (somearray)'
          expr = 5 == mytable.c.somearray.all_()

          # mysql '5 = ALL (SELECT value FROM table)'
          expr = 5 == select([table.c.value]).as_scalar().all_()

      See also

      `all_()` - standalone version

      `any_()` - ANY operator

      New in version 1.1.

    `any_`()
      inherited from the `any_()` method of `ColumnOperators`

      Produce a `any_()` clause against the parent object.

      This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:

          # postgresql '5 = ANY (somearray)'
          expr = 5 == mytable.c.somearray.any_()

          # mysql '5 = ANY (SELECT value FROM table)'
          expr = 5 == select([table.c.value]).as_scalar().any_()

      See also

      `any_()` - standalone version

      `all_()` - ALL operator

      New in version 1.1.

    `asc`()
      inherited from the `asc()` method of `ColumnOperators`

      Produce a `asc()` clause against the parent object.

    `between`(cleft, cright, symmetric=False)
      inherited from the `between()` method of `ColumnOperators`

      Produce a `between()` clause against the parent object, given the lower and upper range.

    `bool_op`(opstring, precedence=0)
      inherited from the `bool_op()` method of `Operators`

      Return a custom boolean operator.

      This method is shorthand for calling `Operators.op()` and passing the `Operators.op.is_comparison` flag with True.

      New in version 1.2.0b3.

      See also

      `Operators.op()`

    `collate`(collation)
      inherited from the `collate()` method of `ColumnOperators`

      Produce a `collate()` clause against the parent object, given the collation string.

      See also

      `collate()`

    `concat`(other)
      inherited from the `concat()` method of `ColumnOperators`

      Implement the ‘concat’ operator.

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

    `contains`(other, **kwargs)
      inherited from the `contains()` method of `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 `ColumnOperators.contains.autoescape` flag may be set to `True` to apply escaping to occurences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the `ColumnOperators.contains.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

      - Parameters: | other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters `%` and `_` are not escaped by default unless the `ColumnOperators.contains.autoescape` flag is set to True. autoescape – boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of `"%"`, `"_"` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:
            somecolumn.contains("foo%bar", autoescape=True)
        Will render as:
            somecolumn LIKE '%' || :param || '%' ESCAPE '/'
      - With the value of :param as `"foo/%bar"`. New in version 1.2. Changed in version 1.2.0: The `ColumnOperators.contains.autoescape` parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the `ColumnOperators.contains.escape` parameter. escape – a character which when given will render with the `ESCAPE` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of `%` and `_` to allow them to act as themselves and not wildcard characters. An expression such as:
            somecolumn.contains("foo/%bar", escape="^")
        Will render as:
            somecolumn LIKE '%' || :param || '%' ESCAPE '^'
        The parameter may also be combined with `ColumnOperators.contains.autoescape`:
            somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
      Where above, the given literal parameter will be converted to `"foo^%bar^^bat"` before being passed to the database.

      See also

      `ColumnOperators.startswith()`

      `ColumnOperators.endswith()`

      `ColumnOperators.like()`

    `desc`()
      inherited from the `desc()` method of `ColumnOperators`

      Produce a `desc()` clause against the parent object.

    `distinct`()
      inherited from the `distinct()` method of `ColumnOperators`

      Produce a `distinct()` clause against the parent object.

    `endswith`(other, **kwargs)
      inherited from the `endswith()` method of `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 `ColumnOperators.endswith.autoescape` flag may be set to `True` to apply escaping to occurences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the `ColumnOperators.endswith.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

      - Parameters: | other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters `%` and `_` are not escaped by default unless the `ColumnOperators.endswith.autoescape` flag is set to True. autoescape – boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of `"%"`, `"_"` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:
            somecolumn.endswith("foo%bar", autoescape=True)
        Will render as:
            somecolumn LIKE '%' || :param ESCAPE '/'
      - With the value of :param as `"foo/%bar"`. New in version 1.2. Changed in version 1.2.0: The `ColumnOperators.endswith.autoescape` parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the `ColumnOperators.endswith.escape` parameter. escape – a character which when given will render with the `ESCAPE` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of `%` and `_` to allow them to act as themselves and not wildcard characters. An expression such as:
            somecolumn.endswith("foo/%bar", escape="^")
        Will render as:
            somecolumn LIKE '%' || :param ESCAPE '^'
        The parameter may also be combined with `ColumnOperators.endswith.autoescape`:
            somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
      Where above, the given literal parameter will be converted to `"foo^%bar^^bat"` before being passed to the database.

      See also

      `ColumnOperators.startswith()`

      `ColumnOperators.contains()`

      `ColumnOperators.like()`

    `ilike`(other, escape=None)
      inherited from the `ilike()` method of `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)
      inherited from the `in_()` method of `ColumnOperators`

      Implement the `in` operator.

      In a column context, produces the clause `a IN other`. “other” may be a tuple/list of column expressions, or a `select()` construct.

      In the case that `other` is an empty sequence, the compiler produces an “empty in” expression. This defaults to the expression “1 != 1” to produce false in all cases. The `create_engine.empty_in_strategy` may be used to alter this behavior.

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

    `is_`(other)
      inherited from the `is_()` method of `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.

      New in version 0.7.9.

      See also

      `ColumnOperators.isnot()`

    `is_distinct_from`(other)
      inherited from the `is_distinct_from()` method of `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”.

      New in version 1.1.

    `isnot`(other)
      inherited from the `isnot()` method of `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.

      New in version 0.7.9.

      See also

      `ColumnOperators.is_()`

    `isnot_distinct_from`(other)
      inherited from the `isnot_distinct_from()` method of `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”.

      New in version 1.1.

    `like`(other, escape=None)
      inherited from the `like()` method of `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, **kwargs)
      inherited from the `match()` method of `ColumnOperators`

      Implements a database-specific ‘match’ operator.

      `match()` attempts to resolve to a MATCH-like function or operator provided by the backend. Examples include:

      - PostgreSQL - renders `x @@ to_tsquery(y)`
      - MySQL - renders `MATCH (x) AGAINST (y IN BOOLEAN MODE)`
      - Oracle - renders `CONTAINS(x, y)`
      - other backends may provide special implementations.
      - Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQlite, for example.
    `notilike`(other, escape=None)
      inherited from the `notilike()` method of `ColumnOperators`

      implement the `NOT ILIKE` operator.

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

      New in version 0.8.

      See also

      `ColumnOperators.ilike()`

    `notin_`(other)
      inherited from the `notin_()` method of `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 `create_engine.empty_in_strategy` may be used to alter this behavior.

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

      See also

      `ColumnOperators.in_()`

    `notlike`(other, escape=None)
      inherited from the `notlike()` method of `ColumnOperators`

      implement the `NOT LIKE` operator.

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

      New in version 0.8.

      See also

      `ColumnOperators.like()`

    `nullsfirst`()
      inherited from the `nullsfirst()` method of `ColumnOperators`

      Produce a `nullsfirst()` clause against the parent object.

    `nullslast`()
      inherited from the `nullslast()` method of `ColumnOperators`

      Produce a `nullslast()` clause against the parent object.

    `op`(opstring, precedence=0, is_comparison=False, return_type=None)
      inherited from the `op()` method of `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: | operator – a string which will be output as the infix operator between this element and the expression passed to the generated function. precedence – precedence to apply to the operator, when parenthesizing expressions. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of `0` is lower than all operators except for the comma (`,`) and `AS` operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators. New in version 0.8: - added the ‘precedence’ argument. is_comparison – if True, the operator will be considered as a “comparison” operator, that is which evaluates to a boolean true/false value, like `==`, `>`, etc. This flag should be set so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition. New in version 0.9.2: - added the `Operators.op.is_comparison` flag. return_type – a `TypeEngine` class or object that will force the return type of an expression produced by this operator to be of that type. By default, operators that specify `Operators.op.is_comparison` will resolve to `Boolean`, and those that do not will be of the same type as the left-hand operand. New in version 1.2.0b3: - added the `Operators.op.return_type` argument.

      See also

      Redefining and Creating New Operators

      Using custom operators in join conditions

    `operate`(op, *other, **kwargs)

      Operate on an argument.

      This is the lowest level of operation, raises `NotImplementedError` by default.

      Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding `ColumnOperators` to apply `func.lower()` to the left and right side:

          class MyComparator(ColumnOperators):
              def operate(self, op, other):
                  return op(func.lower(self), func.lower(other))

      Parameters: | 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()`.

    `reverse_operate`(op, other, **kwargs)

      Reverse operate on an argument.

      Usage is the same as `operate()`.

    `startswith`(other, **kwargs)
      inherited from the `startswith()` method of `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 `ColumnOperators.startswith.autoescape` flag may be set to `True` to apply escaping to occurences of these characters within the string value so that they match as themselves and not as wildcard characters. Alternatively, the `ColumnOperators.startswith.escape` parameter will establish a given character as an escape character which can be of use when the target expression is not a literal string.

      - Parameters: | other – expression to be compared. This is usually a plain string value, but can also be an arbitrary SQL expression. LIKE wildcard characters `%` and `_` are not escaped by default unless the `ColumnOperators.startswith.autoescape` flag is set to True. autoescape – boolean; when True, establishes an escape character within the LIKE expression, then applies it to all occurrences of `"%"`, `"_"` and the escape character itself within the comparison value, which is assumed to be a literal string and not a SQL expression. An expression such as:
            somecolumn.startswith("foo%bar", autoescape=True)
        Will render as:
            somecolumn LIKE :param || '%' ESCAPE '/'
      - With the value of :param as `"foo/%bar"`. New in version 1.2. Changed in version 1.2.0: The `ColumnOperators.startswith.autoescape` parameter is now a simple boolean rather than a character; the escape character itself is also escaped, and defaults to a forwards slash, which itself can be customized using the `ColumnOperators.startswith.escape` parameter. escape – a character which when given will render with the `ESCAPE` keyword to establish that character as the escape character. This character can then be placed preceding occurrences of `%` and `_` to allow them to act as themselves and not wildcard characters. An expression such as:
            somecolumn.startswith("foo/%bar", escape="^")
        Will render as:
            somecolumn LIKE :param || '%' ESCAPE '^'
        The parameter may also be combined with `ColumnOperators.startswith.autoescape`:
            somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
      Where above, the given literal parameter will be converted to `"foo^%bar^^bat"` before being passed to the database.

      See also

      `ColumnOperators.endswith()`

      `ColumnOperators.contains()`

      `ColumnOperators.like()`

  `__eq__`
    inherited from the `__eq__` attribute of `object`

    Return self==value.

  `__init__`(*args, **kwargs)

    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.

  `__le__`
    inherited from the `__le__` attribute of `object`

    Return self<=value.

  `__lt__`
    inherited from the `__lt__` attribute of `object`

    Return self<value.

  `__ne__`
    inherited from the `__ne__` attribute of `object`

    Return self!=value.

  `adapt`(cls, **kw)
    inherited from the `adapt()` method of `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.

  `bind_expression`(bindvalue)
    inherited from the `bind_expression()` method of `TypeEngine`

    “Given a bind value (i.e. a `BindParameter` instance), return a SQL expression in its place.

    This is typically a SQL function that wraps the existing bound parameter within the statement. It is used for special data types that require literals being wrapped in some special database function in order to coerce an application-level value into a database-specific format. It is the SQL analogue of the `TypeEngine.bind_processor()` method.

    The method is evaluated at statement compile time, as opposed to statement construction time.

    Note that this method, when implemented, should always return the exact same structure, without any conditional logic, as it may be used in an executemany() call against an arbitrary number of bound parameter sets.

    See also:

    Applying SQL-level Bind/Result Processing

  `bind_processor`(dialect)

    Provide a bound value processing function for the given `Dialect`.

    This is the method that fulfills the `TypeEngine` contract for bound value conversion. `TypeDecorator` will wrap a user-defined implementation of `process_bind_param()` here.

    User-defined code can override this method directly, though its likely best to use `process_bind_param()` so that the processing provided by `self.impl` is maintained.

    Parameters: | dialect – Dialect instance in use.

    This method is the reverse counterpart to the `result_processor()` method of this class.

  `coerce_compared_value`(op, value)

    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` = (<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.

    New in version 0.8.2: Added `TypeDecorator.coerce_to_is_types` to allow for easier control of `__eq__()` `__ne__()` operations.

  `column_expression`(colexpr)
    inherited from the `column_expression()` method of `TypeEngine`

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

    This is typically a SQL function that wraps a column expression as rendered in the columns clause of a SELECT statement. It is used for special data types that require columns to be wrapped in some special database function in order to coerce the value before being sent back to the application. It is the SQL analogue of the `TypeEngine.result_processor()` method.

    The method is evaluated at statement compile time, as opposed to statement construction time.

    See also:

    Applying SQL-level Bind/Result Processing

  `comparator_factory`

    Base class for custom comparison operations defined at the type level. See `TypeEngine.comparator_factory`.

  `compare_against_backend`(dialect, conn_type)
    inherited from the `compare_against_backend()` method of `TypeEngine`

    Compare this type against the given backend type.

    This function is currently not implemented for SQLAlchemy types, and for all built in types will return `None`. However, it can be implemented by a user-defined type where it can be consumed by schema comparison tools such as Alembic autogenerate.

    A future release of SQLAlchemy will potentially impement this method for builtin types as well.

    The function should return True if this type is equivalent to the given type; the type is typically reflected from the database so should be database specific. The dialect in use is also passed. It can also return False to assert that the type is not equivalent.

    Parameters: | dialect – a `Dialect` that is involved in the comparison. conn_type – the type object reflected from the backend.

    New in version 1.0.3.

  `compare_values`(x, y)

    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=None)
    inherited from the `compile()` method of `TypeEngine`

    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)

    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)
    inherited from the `dialect_impl()` method of `TypeEngine`

    Return a dialect-specific implementation for this `TypeEngine`.

  `evaluates_none`()
    inherited from the `evaluates_none()` method of `TypeEngine`

    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 `null` SQL construct in an INSERT statement or associated with an ORM-mapped attribute.

    Note

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

    New in version 1.1.

    See also

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

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

    `TypeEngine.should_evaluate_none` - class-level flag

  `get_dbapi_type`(dbapi)

    Return the DBAPI type object represented by this `TypeDecorator`.

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

  `literal_processor`(dialect)

    Provide a literal processing function for the given `Dialect`.

    Subclasses here will typically override `TypeDecorator.process_literal_param()` instead of this method directly.

    By default, this method makes use of `TypeDecorator.process_bind_param()` if that method is implemented, where `TypeDecorator.process_literal_param()` is not. The rationale here is that `TypeDecorator` typically deals with Python conversions of data that are above the layer of database presentation. With the value converted by `TypeDecorator.process_bind_param()`, the underlying type will then handle whether it needs to be presented to the DBAPI as a bound parameter or to the database as an inline SQL value.

    New in version 0.9.0.

  `load_dialect_impl`(dialect)

    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, dialect)

    Receive a bound parameter value to be converted.

    Subclasses override this method to return the value that should be passed along to the underlying `TypeEngine` object, and from there to the DBAPI `execute()` method.

    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.

    This operation should be designed with the reverse operation in mind, which would be the process_result_value method of this class.

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

  `process_literal_param`(value, dialect)

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

    This method is used when the compiler renders a literal value without using binds, typically within DDL such as in the “server default” of a column or an expression within a CHECK constraint.

    The returned string will be rendered into the output string.

    New in version 0.9.0.

  `process_result_value`(value, dialect)

    Receive a result-row column value to be converted.

    Subclasses should implement this method to operate on data fetched from the database.

    Subclasses override this method to return the value that should be passed back to the application, given a value that is already processed by the underlying `TypeEngine` object, originally from the DBAPI cursor method `fetchone()` or similar.

    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.

    This operation should be designed to be reversible by the “process_bind_param” method of this class.

  `python_type`
    inherited from the `python_type` attribute of `TypeEngine`

    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.

  `result_processor`(dialect, coltype)

    Provide a result value processing function for the given `Dialect`.

    This is the method that fulfills the `TypeEngine` contract for result value conversion. `TypeDecorator` will wrap a user-defined implementation of `process_result_value()` here.

    User-defined code can override this method directly, though its likely best to use `process_result_value()` so that the processing provided by `self.impl` is maintained.

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

    This method is the reverse counterpart to the `bind_processor()` method of this class.

  `type_engine`(dialect)

    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()` but also traverses into wrapped `TypeDecorator` instances. Behavior can be customized here by overriding `load_dialect_impl()`.

  `with_variant`(type_, dialect_name)
    inherited from the `with_variant()` method of `TypeEngine`

    Produce a new 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

        s = String()

        s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

    The construction of `TypeEngine.with_variant()` is always from the “fallback” type to that which is dialect specific. The returned type is an instance of `Variant`, which itself provides a `Variant.with_variant()` that can be called repeatedly.

    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_name – base name of the dialect which uses this type. (i.e. `'postgresql'`, `'mysql'`, etc.)

    New in version 0.7.2.

## TypeDecorator Recipes

A few key `TypeDecorator` recipes follow.

### Coercing Encoded Strings to Unicode

A common source of confusion regarding the `Unicode` type is that it is intended to deal only with Python `unicode` objects on the Python side, meaning values passed to it as bind parameters must be of the form `u'some string'` if using Python 2 and not 3. The encoding/decoding functions it performs are only to suit what the DBAPI in use requires, and are primarily a private implementation detail.

The use case of a type that can safely receive Python bytestrings, that is strings that contain non-ASCII characters and are not `u''` objects in Python 2, can be achieved using a `TypeDecorator` which coerces as needed:

    from sqlalchemy.types import TypeDecorator, Unicode

    class CoerceUTF8(TypeDecorator):
        """Safely coerce Python bytestrings to Unicode
        before passing off to the database."""

        impl = Unicode

        def process_bind_param(self, value, dialect):
            if isinstance(value, str):
                value = value.decode('utf-8')
            return value

### Rounding Numerics

Some database connectors like those of SQL Server choke if a Decimal is passed with too many decimal places. Here’s a recipe that rounds them down:

    from sqlalchemy.types import TypeDecorator, Numeric
    from decimal import Decimal

    class SafeNumeric(TypeDecorator):
        """Adds quantization to Numeric."""

        impl = Numeric

        def __init__(self, *arg, **kw):
            TypeDecorator.__init__(self, *arg, **kw)
            self.quantize_int = - self.impl.scale
            self.quantize = Decimal(10) ** self.quantize_int

        def process_bind_param(self, value, dialect):
            if isinstance(value, Decimal) and \
                value.as_tuple()[2] < self.quantize_int:
                value = value.quantize(self.quantize)
            return value

### Backend-agnostic GUID Type

Receives and returns Python uuid() objects. Uses the PG UUID type when using PostgreSQL, CHAR(32) on other backends, storing them in stringified hex format. Can be modified to store binary in CHAR(16) if desired:

    from sqlalchemy.types import TypeDecorator, CHAR
    from sqlalchemy.dialects.postgresql import UUID
    import uuid

    class GUID(TypeDecorator):
        """Platform-independent GUID type.

        Uses PostgreSQL's UUID type, otherwise uses
        CHAR(32), storing as stringified hex values.

        """
        impl = CHAR

        def load_dialect_impl(self, dialect):
            if dialect.name == 'postgresql':
                return dialect.type_descriptor(UUID())
            else:
                return dialect.type_descriptor(CHAR(32))

        def process_bind_param(self, value, dialect):
            if value is None:
                return value
            elif dialect.name == 'postgresql':
                return str(value)
            else:
                if not isinstance(value, uuid.UUID):
                    return "%.32x" % uuid.UUID(value).int
                else:
                    # hexstring
                    return "%.32x" % value.int

        def process_result_value(self, value, dialect):
            if value is None:
                return value
            else:
                if not isinstance(value, uuid.UUID):
                    value = uuid.UUID(value)
                return value

### Marshal JSON Strings

This type uses `simplejson` to marshal Python data structures to/from JSON. Can be modified to use Python’s builtin json encoder:

    from sqlalchemy.types import TypeDecorator, VARCHAR
    import json

    class JSONEncodedDict(TypeDecorator):
        """Represents an immutable structure as a json-encoded string.

        Usage::

            JSONEncodedDict(255)

        """

        impl = VARCHAR

        def process_bind_param(self, value, dialect):
            if value is not None:
                value = json.dumps(value)

            return value

        def process_result_value(self, value, dialect):
            if value is not None:
                value = json.loads(value)
            return value

#### Adding Mutability

The ORM by default will not detect “mutability” on such a type as above - meaning, in-place changes to values will not be detected and will not be flushed. Without further steps, you instead would need to replace the existing value with a new one on each parent object to detect changes:

    obj.json_value["key"] = "value"  # will *not* be detected by the ORM

    obj.json_value = {"key": "value"}  # *will* be detected by the ORM

The above limitation may be fine, as many applications may not require that the values are ever mutated once created. For those which do have this requirement, support for mutability is best applied using the `sqlalchemy.ext.mutable` extension. For a dictionary-oriented JSON structure, we can apply this as:

    json_type = MutableDict.as_mutable(JSONEncodedDict)

    class MyClass(Base):
        #  ...

        json_data = Column(json_type)

See also

Mutation Tracking

#### Dealing with Comparison Operations

The default behavior of `TypeDecorator` is to coerce the “right hand side” of any expression into the same type. For a type like JSON, this means that any operator used must make sense in terms of JSON. For some cases, users may wish for the type to behave like JSON in some circumstances, and as plain text in others. One example is if one wanted to handle the LIKE operator for the JSON type. LIKE makes no sense against a JSON structure, but it does make sense against the underlying textual representation. To get at this with a type like `JSONEncodedDict`, we need to coerce the column to a textual form using `cast()` or `type_coerce()` before attempting to use this operator:

    from sqlalchemy import type_coerce, String

    stmt = select([my_table]).where(
        type_coerce(my_table.c.json_data, String).like('%foo%'))

`TypeDecorator` provides a built-in system for working up type translations like these based on operators. If we wanted to frequently use the LIKE operator with our JSON object interpreted as a string, we can build it into the type by overriding the `TypeDecorator.coerce_compared_value()` method:

    from sqlalchemy.sql import operators
    from sqlalchemy import String

    class JSONEncodedDict(TypeDecorator):

        impl = VARCHAR

        def coerce_compared_value(self, op, value):
            if op in (operators.like_op, operators.notlike_op):
                return String()
            else:
                return self

        def process_bind_param(self, value, dialect):
            if value is not None:
                value = json.dumps(value)

            return value

        def process_result_value(self, value, dialect):
            if value is not None:
                value = json.loads(value)
            return value

Above is just one approach to handling an operator like “LIKE”. Other applications may wish to raise `NotImplementedError` for operators that have no meaning with a JSON object such as “LIKE”, rather than automatically coercing to text.

## Replacing the Bind/Result Processing of Existing Types

Most augmentation of type behavior at the bind/result level is achieved using `TypeDecorator`. For the rare scenario where the specific processing applied by SQLAlchemy at the DBAPI level needs to be replaced, the SQLAlchemy type can be subclassed directly, and the `bind_processor()` or `result_processor()` methods can be overridden. Doing so requires that the `adapt()` method also be overridden. This method is the mechanism by which SQLAlchemy produces DBAPI-specific type behavior during statement execution. Overriding it allows a copy of the custom type to be used in lieu of a DBAPI-specific type. Below we subclass the `types.TIME` type to have custom result processing behavior. The `process()` function will receive `value` from the DBAPI cursor directly:

    class MySpecialTime(TIME):
        def __init__(self, special_argument):
            super(MySpecialTime, self).__init__()
            self.special_argument = special_argument

        def result_processor(self, dialect, coltype):
            import datetime
            time = datetime.time
            def process(value):
                if value is not None:
                    microseconds = value.microseconds
                    seconds = value.seconds
                    minutes = seconds / 60
                    return time(
                              minutes / 60,
                              minutes % 60,
                              seconds - minutes * 60,
                              microseconds)
                else:
                    return None
            return process

        def adapt(self, impltype):
            return MySpecialTime(self.special_argument)

## Applying SQL-level Bind/Result Processing

As seen in the sections Augmenting Existing Types and Replacing the Bind/Result Processing of Existing Types, SQLAlchemy allows Python functions to be invoked both when parameters are sent to a statement, as well as when result rows are loaded from the database, to apply transformations to the values as they are sent to or from the database. It is also possible to define SQL-level transformations as well. The rationale here is when only the relational database contains a particular series of functions that are necessary to coerce incoming and outgoing data between an application and persistence format. Examples include using database-defined encryption/decryption functions, as well as stored procedures that handle geographic data. The PostGIS extension to PostgreSQL includes an extensive array of SQL functions that are necessary for coercing data into particular formats.

Any `TypeEngine`, `UserDefinedType` or `TypeDecorator` subclass can include implementations of `TypeEngine.bind_expression()` and/or `TypeEngine.column_expression()`, which when defined to return a non-`None` value should return a `ColumnElement` expression to be injected into the SQL statement, either surrounding bound parameters or a column expression. For example, to build a `Geometry` type which will apply the PostGIS function `ST_GeomFromText` to all outgoing values and the function `ST_AsText` to all incoming data, we can create our own subclass of `UserDefinedType` which provides these methods in conjunction with `func`:

    from sqlalchemy import func
    from sqlalchemy.types import UserDefinedType

    class Geometry(UserDefinedType):
        def get_col_spec(self):
            return "GEOMETRY"

        def bind_expression(self, bindvalue):
            return func.ST_GeomFromText(bindvalue, type_=self)

        def column_expression(self, col):
            return func.ST_AsText(col, type_=self)

We can apply the `Geometry` type into `Table` metadata and use it in a `select()` construct:

    geometry = Table('geometry', metadata,
                  Column('geom_id', Integer, primary_key=True),
                  Column('geom_data', Geometry)
                )

    print(select([geometry]).where(
      geometry.c.geom_data == 'LINESTRING(189412 252431,189631 259122)'))

The resulting SQL embeds both functions as appropriate. `ST_AsText` is applied to the columns clause so that the return value is run through the function before passing into a result set, and `ST_GeomFromText` is run on the bound parameter so that the passed-in value is converted:

    SELECT geometry.geom_id, ST_AsText(geometry.geom_data) AS geom_data_1
    FROM geometry
    WHERE geometry.geom_data = ST_GeomFromText(:geom_data_2)

The `TypeEngine.column_expression()` method interacts with the mechanics of the compiler such that the SQL expression does not interfere with the labeling of the wrapped expression. Such as, if we rendered a `select()` against a `label()` of our expression, the string label is moved to the outside of the wrapped expression:

    print(select([geometry.c.geom_data.label('my_data')]))

Output:

    SELECT ST_AsText(geometry.geom_data) AS my_data
    FROM geometry

For an example of subclassing a built in type directly, we subclass `postgresql.BYTEA` to provide a `PGPString`, which will make use of the PostgreSQL `pgcrypto` extension to encrypt/decrypt values transparently:

    from sqlalchemy import create_engine, String, select, func, \
            MetaData, Table, Column, type_coerce

    from sqlalchemy.dialects.postgresql import BYTEA

    class PGPString(BYTEA):
        def __init__(self, passphrase, length=None):
            super(PGPString, self).__init__(length)
            self.passphrase = passphrase

        def bind_expression(self, bindvalue):
            # convert the bind's type from PGPString to
            # String, so that it's passed to psycopg2 as is without
            # a dbapi.Binary wrapper
            bindvalue = type_coerce(bindvalue, String)
            return func.pgp_sym_encrypt(bindvalue, self.passphrase)

        def column_expression(self, col):
            return func.pgp_sym_decrypt(col, self.passphrase)

    metadata = MetaData()
    message = Table('message', metadata,
                    Column('username', String(50)),
                    Column('message',
                        PGPString("this is my passphrase", length=1000)),
                )

    engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
    with engine.begin() as conn:
        metadata.create_all(conn)

        conn.execute(message.insert(), username="some user",
                                    message="this is my message")

        print(conn.scalar(
                select([message.c.message]).\
                    where(message.c.username == "some user")
            ))

The `pgp_sym_encrypt` and `pgp_sym_decrypt` functions are applied to the INSERT and SELECT statements:

    INSERT INTO message (username, message)
      VALUES (%(username)s, pgp_sym_encrypt(%(message)s, %(pgp_sym_encrypt_1)s))
      {'username': 'some user', 'message': 'this is my message',
        'pgp_sym_encrypt_1': 'this is my passphrase'}

    SELECT pgp_sym_decrypt(message.message, %(pgp_sym_decrypt_1)s) AS message_1
      FROM message
      WHERE message.username = %(username_1)s
      {'pgp_sym_decrypt_1': 'this is my passphrase', 'username_1': 'some user'}

New in version 0.8: Added the `TypeEngine.bind_expression()` and `TypeEngine.column_expression()` methods.

See also:

PostGIS Integration

## Redefining and Creating New Operators

SQLAlchemy Core defines a fixed set of expression operators available to all column expressions. Some of these operations have the effect of overloading Python’s built in operators; examples of such operators include `ColumnOperators.__eq__()` (`table.c.somecolumn == 'foo'`), `ColumnOperators.__invert__()` (`~table.c.flag`), and `ColumnOperators.__add__()` (`table.c.x + table.c.y`). Other operators are exposed as explicit methods on column expressions, such as `ColumnOperators.in_()` (`table.c.value.in_(['x', 'y'])`) and `ColumnOperators.like()` (`table.c.value.like('%ed%')`).

The Core expression constructs in all cases consult the type of the expression in order to determine the behavior of existing operators, as well as to locate additional operators that aren’t part of the built in set. The `TypeEngine` base class defines a root “comparison” implementation `TypeEngine.Comparator`, and many specific types provide their own sub-implementations of this class. User-defined `TypeEngine.Comparator` implementations can be built directly into a simple subclass of a particular type in order to override or define new operations. Below, we create a `Integer` subclass which overrides the `ColumnOperators.__add__()` operator:

    from sqlalchemy import Integer

    class MyInt(Integer):
        class comparator_factory(Integer.Comparator):
            def __add__(self, other):
                return self.op("goofy")(other)

The above configuration creates a new class `MyInt`, which establishes the `TypeEngine.comparator_factory` attribute as referring to a new class, subclassing the `TypeEngine.Comparator` class associated with the `Integer` type.

Usage:

    >>> sometable = Table("sometable", metadata, Column("data", MyInt))
    >>> print(sometable.c.data + 5)
    sometable.data goofy :data_1

The implementation for `ColumnOperators.__add__()` is consulted by an owning SQL expression, by instantiating the `TypeEngine.Comparator` with itself as the `expr` attribute. The mechanics of the expression system are such that operations continue recursively until an expression object produces a new SQL expression construct. Above, we could just as well have said `self.expr.op("goofy")(other)` instead of `self.op("goofy")(other)`.

When using `Operators.op()` for comparison operations that return a boolean result, the `Operators.op.is_comparison` flag should be set to `True`:

    class MyInt(Integer):
        class comparator_factory(Integer.Comparator):
            def is_frobnozzled(self, other):
                return self.op("--is_frobnozzled->", is_comparison=True)(other)

New methods added to a `TypeEngine.Comparator` are exposed on an owning SQL expression using a `__getattr__` scheme, which exposes methods added to `TypeEngine.Comparator` onto the owning `ColumnElement`. For example, to add a `log()` function to integers:

    from sqlalchemy import Integer, func

    class MyInt(Integer):
        class comparator_factory(Integer.Comparator):
            def log(self, other):
                return func.log(self.expr, other)

Using the above type:

    >>> print(sometable.c.data.log(5))
    log(:log_1, :log_2)

Unary operations are also possible. For example, to add an implementation of the PostgreSQL factorial operator, we combine the `UnaryExpression` construct along with a `custom_op` to produce the factorial expression:

    from sqlalchemy import Integer
    from sqlalchemy.sql.expression import UnaryExpression
    from sqlalchemy.sql import operators

    class MyInteger(Integer):
        class comparator_factory(Integer.Comparator):
            def factorial(self):
                return UnaryExpression(self.expr,
                            modifier=operators.custom_op("!"),
                            type_=MyInteger)

Using the above type:

    >>> from sqlalchemy.sql import column
    >>> print(column('x', MyInteger).factorial())
    x !

See also

`Operators.op()`

`TypeEngine.comparator_factory`

## Creating New Types

The `UserDefinedType` class is provided as a simple base class for defining entirely new database types. Use this to represent native database types not known by SQLAlchemy. If only Python translation behavior is needed, use `TypeDecorator` instead.

class `sqlalchemy.types.` `UserDefinedType`

  Bases: `sqlalchemy.types.TypeEngine`

  Base for user defined types.

  This should be the base of new types. Note that for most cases, `TypeDecorator` is probably more appropriate:

      import sqlalchemy.types as types

      class MyType(types.UserDefinedType):
          def __init__(self, precision = 8):
              self.precision = precision

          def get_col_spec(self, **kw):
              return "MYTYPE(%s)" % self.precision

          def bind_processor(self, dialect):
              def process(value):
                  return value
              return process

          def result_processor(self, dialect, coltype):
              def process(value):
                  return value
              return process

  Once the type is made, it’s immediately usable:

      table = Table('foo', meta,
          Column('id', Integer, primary_key=True),
          Column('data', MyType(16))
          )

  The `get_col_spec()` method will in most cases receive a keyword argument `type_expression` which refers to the owning expression of the type as being compiled, such as a `Column` or `cast()` construct. This keyword is only sent if the method accepts keyword arguments (e.g. `**kw`) in its argument signature; introspection is used to check for this in order to support legacy forms of this function.

  New in version 1.0.0: the owning expression is passed to the `get_col_spec()` method via the keyword argument `type_expression`, if it receives `**kw` in its signature.

  class `Comparator`(expr)

    Bases: `sqlalchemy.types.Comparator`

  `coerce_compared_value`(op, value)

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

    Default behavior for `UserDefinedType` is the same as that of `TypeDecorator`; by default it returns `self`, assuming the compared value should be coerced into the same type as this one. See `TypeDecorator.coerce_compared_value()` for more detail.

    Changed in version 0.8: `UserDefinedType.coerce_compared_value()` now returns `self` by default, rather than falling onto the more fundamental behavior of `TypeEngine.coerce_compared_value()`.

  `comparator_factory`

    alias of `UserDefinedType.Comparator`