Base Type API

• class sqlalchemy.types.TypeEngine
• Bases: sqlalchemy.sql.visitors.Visitable

The ultimate base class for all SQL datatypes.

Common subclasses of TypeEngine includeString, Integer, and Boolean.

For an overview of the SQLAlchemy typing system, seeColumn and Data Types.

See also

Column and Data Types

• class Comparator(expr)
• Bases: sqlalchemy.sql.operators.ColumnOperators

Base class for custom comparison operations defined at thetype level. See TypeEngine.comparator_factory.

- <code>operate</code>(_default_comparator_, _op_, _*other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.TypeEngine.Comparator.operate)
- 

Operate on an argument.

This is the lowest level of operation, raisesNotImplementedError by default.

Overriding this on a subclass can allow commonbehavior to be applied to all operations.For example, overriding ColumnOperatorsto apply func.lower() to the left and rightside:

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. Willbe a single scalar for most operations.

    - 

**kwargs – modifiers. These may be passed by specialoperators such as ColumnOperators.contains().

- <code>reverse_operate</code>(_default_comparator_, _op_, _other_, _**kwargs_)[](https://docs.sqlalchemy.org/en/13/core/#sqlalchemy.types.TypeEngine.Comparator.reverse_operate)
- 

Reverse operate on an argument.

Usage is the same as operate().

• adapt(cls, **kw)
• Produce an “adapted” form of this type, given an “impl” classto work with.

This method is used internally to associate generictypes with “implementation” types that are specific to a particulardialect.

• bindexpression(_bindvalue)
• “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 boundparameter within the statement. It is used for special data typesthat require literals being wrapped in some special database functionin order to coerce an application-level value into a database-specificformat. It is the SQL analogue of theTypeEngine.bind_processor() method.

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

Note that this method, when implemented, should always returnthe exact same structure, without any conditional logic, as itmay be used in an executemany() call against an arbitrary numberof bound parameter sets.

See also

Applying SQL-level Bind/Result Processing

• bindprocessor(_dialect)
• Return a conversion function for processing bind values.

Returns a callable which will receive a bind parameter valueas the sole positional argument and will return a value tosend to the DB-API.

If processing is not necessary, the method should return None.

- Parameters
- 

dialect – Dialect instance in use.

• coercecompared_value(_op, value)
• Suggest a type for a ‘coerced’ Python value in an expression.

Given an operator and value, gives the type a chanceto return a type which the value should be coerced into.

The default behavior here is conservative; if the right-handside is already coerced into a SQL type based on itsPython type, it is usually left alone.

End-user functionality extension here should generally be viaTypeDecorator, which provides more liberal behavior in thatit defaults to coercing the other side of the expression into thistype, thus applying special Python conversions above and beyond thoseneeded by the DBAPI to both ides. It also provides the public methodTypeDecorator.coerce_compared_value() which is intended forend-user customization of this behavior.

• columnexpression(_colexpr)
• Given a SELECT column expression, return a wrapping SQL expression.

This is typically a SQL function that wraps a column expressionas rendered in the columns clause of a SELECT statement.It is used for special data types that requirecolumns to be wrapped in some special database function in orderto 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 opposedto statement construction time.

See also

Applying SQL-level Bind/Result Processing

• comparator_factory
• Bases: sqlalchemy.sql.operators.ColumnOperators

A TypeEngine.Comparator class which will applyto operations performed by owning ColumnElement objects.

The comparator_factory attribute is a hook consulted bythe core expression system when column and SQL expression operationsare performed. When a TypeEngine.Comparator class isassociated with this attribute, it allows custom re-definition ofall existing operators, as well as definition of new operators.Existing operators include those provided by Python operator overloadingsuch as operators.ColumnOperators.add() andoperators.ColumnOperators.eq(),those provided as standardattributes of operators.ColumnOperators such asoperators.ColumnOperators.like()and operators.ColumnOperators.in_().

Rudimentary usage of this hook is allowed through simple subclassingof existing types, or alternatively by using TypeDecorator.See the documentation section Redefining and Creating New Operators for examples.

alias of TypeEngine.Comparator

• compareagainst_backend(_dialect, conn_type)
• Compare this type against the given backend type.

This function is currently not implemented for SQLAlchemytypes, and for all built in types will return None. However,it can be implemented by a user-defined typewhere it can be consumed by schema comparison tools such asAlembic autogenerate.

A future release of SQLAlchemy will potentially implement this methodfor builtin types as well.

The function should return True if this type is equivalent to thegiven type; the type is typically reflected from the databaseso should be database specific. The dialect in use is alsopassed. It can also return False to assert that the type isnot 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.

• comparevalues(_x, y)

• Compare two values for equality.

• compile(dialect=None)

• Produce a string-compiled form of this TypeEngine.

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

- Parameters
- 

dialect – a Dialect instance.

• dialectimpl(_dialect)

• Return a dialect-specific implementation for thisTypeEngine.

• evaluates_none()

• Return a copy of this type which has the should_evaluate_noneflag 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 Noneis passed to the column in an INSERT statement, rather than omittingthe column from the INSERT statement which has the effect of firingoff column-level defaults. It also allows for types which havespecial behavior associated with the Python None value to indicatethat the value doesn’t necessarily translate into SQL NULL; aprime example of this is a JSON type which may wish to persist theJSON value 'null'.

In all cases, the actual NULL SQL value can be always bepersisted in any column by usingthe null SQL construct in an INSERT statementor associated with an ORM-mapped attribute.

Note

The “evaluates none” flag does not apply to a valueof None passed to Column.default orColumn.server_default; in these cases, Nonestill 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 JSONinteraction with this flag.

TypeEngine.should_evaluate_none - class-level flag

• getdbapi_type(_dbapi)
• Return the corresponding type object from the underlying DB-API, ifany.

This can be useful for calling setinputsizes(), for example.

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

Used by the ORM when uniquing result lists.

• literalprocessor(_dialect)
• Return a conversion function for processing literal values that areto be rendered directly without using binds.

This function is used when the compiler makes use of the“literal_binds” flag, typically used in DDL generation as wellas in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

• property python_type
• Return the Python type object expected to be returnedby 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 commonDBAPIs (like int for example), will return that type.

If a return type is not defined, raisesNotImplementedError.

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

• resultprocessor(_dialect, coltype)
• Return a conversion function for processing result row values.

Returns a callable which will receive a result row columnvalue as the sole positional argument and will return a valueto return to the user.

If processing is not necessary, the method should return None.

- Parameters
- 
  - 

dialect – Dialect instance in use.

  - 

coltype – DBAPI coltype argument received in cursor.description.

• shouldevaluate_none = False_
• If True, the Python constant None is considered to be handledexplicitly by this type.

The ORM uses this flag to indicate that a positive value of Noneis passed to the column in an INSERT statement, rather than omittingthe column from the INSERT statement which has the effect of firingoff column-level defaults. It also allows types which have specialbehavior for Python None, such as a JSON type, to indicate thatthey’d like to handle the None value explicitly.

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

See also

TypeEngine.evaluates_none()

New in version 1.1.

• sortkey_function = None_
• A sorting function that can be passed as the key to sorted.

The default value of None indicates that the values stored bythis type are self-sorting.

New in version 1.3.8.

• withvariant(type_, _dialect_name)
• Produce a new type object that will utilize the giventype 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 alwaysfrom the “fallback” type to that which is dialect specific.The returned type is an instance of Variant, whichitself provides a Variant.with_variant()that can be called repeatedly.

- Parameters
- 
  - 

type_ – a TypeEngine that will be selectedas a variant from the originating type, when a dialectof the given name is in use.

  - 

dialect_name – base name of the dialect which usesthis type. (i.e. 'postgresql', 'mysql', etc.)

• class sqlalchemy.types.Concatenable

• A mixin that marks a type as supporting ‘concatenation’,typically strings.

  • class Comparator(expr)

  • Bases: sqlalchemy.types.Comparator

  • comparator_factory

  • alias of Concatenable.Comparator

• class sqlalchemy.types.Indexable

• A mixin that marks a type as supporting indexing operations,such as array or JSON structures.

New in version 1.1.0.

• class Comparator(expr)

• Bases: sqlalchemy.types.Comparator

• comparator_factory

• alias of Indexable.Comparator

• class sqlalchemy.types.NullType
• Bases: sqlalchemy.types.TypeEngine

An unknown type.

NullType is used as a default type for those cases wherea type cannot be determined, including:

• During table reflection, when the type of a column is not recognizedby the Dialect

• When constructing SQL expressions using plain Python objects ofunknown types (e.g. somecolumn == my_special_object)

• When a new Column is created, and the given type is passedas None or is not passed at all.

The NullType can be used within SQL expression invocationwithout issue, it just has no behavior either at the expressionconstruction level or at the bind-parameter/result processing level.NullType will result in a CompileError if the compileris asked to render the type itself, such as if it is used in acast() operation or within a schema creation operation such as thatinvoked by MetaData.create_all() or the CreateTableconstruct.

• class sqlalchemy.types.Variant(base, mapping)
• Bases: sqlalchemy.types.TypeDecorator

A wrapping type that selects among a variety ofimplementations based on dialect in use.

The Variant type is typically constructedusing the TypeEngine.with_variant() method.

See also

TypeEngine.with_variant() for an example of use.

• Members
• withvariant, _init