Release: 0.9.10 legacy version | Release Date: July 22, 2015

SQLAlchemy 0.9 Documentation

Base Type API

class sqlalchemy.types.TypeEngine

Bases: sqlalchemy.sql.visitors.Visitable

The ultimate base class for all SQL datatypes.

Common subclasses of TypeEngine include String, Integer, and Boolean.

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

class Comparator(expr)

Bases: sqlalchemy.types.Comparator, sqlalchemy.sql.operators.ColumnOperators

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

adapt(cls, **kw)

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)

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

Return a conversion function for processing bind values.

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

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

Parameters:dialect – Dialect instance in use.
coerce_compared_value(op, value)

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

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

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

End-user functionality extension here should generally be via TypeDecorator, which provides more liberal behavior in that it defaults to coercing the other side of the expression into this type, thus applying special Python conversions above and beyond those needed by the DBAPI to both ides. It also provides the public method TypeDecorator.coerce_compared_value() which is intended for end-user customization of this behavior.

column_expression(colexpr)

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

Bases: sqlalchemy.types.Comparator, sqlalchemy.sql.operators.ColumnOperators

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

The comparator_factory attribute is a hook consulted by the core expression system when column and SQL expression operations are performed. When a TypeEngine.Comparator class is associated with this attribute, it allows custom re-definition of all existing operators, as well as definition of new operators. Existing operators include those provided by Python operator overloading such as operators.ColumnOperators.__add__() and operators.ColumnOperators.__eq__(), those provided as standard attributes of operators.ColumnOperators such as operators.ColumnOperators.like() and operators.ColumnOperators.in_().

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

New in version 0.8: The expression system was enhanced to support customization of operators on a per-type level.

alias of Comparator

compare_values(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” dialect to produce a string result.

Parameters:dialect – a Dialect instance.
dialect_impl(dialect)

Return a dialect-specific implementation for this TypeEngine.

get_dbapi_type(dbapi)

Return the corresponding type object from the underlying DB-API, if any.

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.

literal_processor(dialect)

Return a conversion function for processing literal values that are to 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 well as in certain scenarios where backends don’t accept bound parameters.

New in version 0.9.0.

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.

result_processor(dialect, coltype)

Return a conversion function for processing result row values.

Returns a callable which will receive a result row column value as the sole positional argument and will return a value to 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.
with_variant(type_, dialect_name)

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.

class sqlalchemy.types.Concatenable

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

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.

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.

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

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

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.

In a column context, produces the clause LIKE '%<other>%'

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.

In a column context, produces the clause LIKE '%<other>'

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

Implement the ilike operator.

In a column context, produces the clause a ILIKE other.

E.g.:

select([sometable]).where(sometable.c.column.ilike("%foobar%"))
Parameters:
  • other – expression to be compared
  • escape

    optional escape character, renders the ESCAPE keyword, e.g.:

    somecolumn.ilike("foo/%bar", escape="/")
in_(other)
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.

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.

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.

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

Implement the like operator.

In a column context, produces the clause a LIKE other.

E.g.:

select([sometable]).where(sometable.c.column.like("%foobar%"))
Parameters:
  • other – expression to be compared
  • escape

    optional escape character, renders the ESCAPE keyword, e.g.:

    somecolumn.like("foo/%bar", escape="/")
match(other, **kwargs)
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.

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

New in version 0.8.

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.

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

operate(op, *other, **kwargs)
inherited from the operate() method of _DefaultColumnComparator

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)
inherited from the reverse_operate() method of _DefaultColumnComparator

Reverse operate on an argument.

Usage is the same as operate().

startswith(other, **kwargs)
inherited from the startswith() method of ColumnOperators

Implement the startwith operator.

In a column context, produces the clause LIKE '<other>%'

__init__
inherited from the __init__ attribute of object

x.__init__(…) initializes x; see help(type(x)) for signature

comparator_factory

alias of Comparator

class sqlalchemy.types.NullType

Bases: sqlalchemy.types.TypeEngine

An unknown type.

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

  • During table reflection, when the type of a column is not recognized by the Dialect
  • When constructing SQL expressions using plain Python objects of unknown types (e.g. somecolumn == my_special_object)
  • When a new Column is created, and the given type is passed as None or is not passed at all.

The NullType can be used within SQL expression invocation without issue, it just has no behavior either at the expression construction level or at the bind-parameter/result processing level. NullType will result in a CompileError if the compiler is asked to render the type itself, such as if it is used in a cast() operation or within a schema creation operation such as that invoked by MetaData.create_all() or the CreateTable construct.

class sqlalchemy.types.Variant(base, mapping)

Bases: sqlalchemy.types.TypeDecorator

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

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

New in version 0.7.2.

See also

TypeEngine.with_variant() for an example of use.

Members:with_variant, __init__
Previous: Custom Types Next: Engine and Connection Use