Release: 0.7.10 | Release Date: February 7, 2013

SQLAlchemy 0.7 Documentation

Column and Data Types

SQLAlchemy provides abstractions for most common database data types, and a mechanism for specifying your own custom data types.

The methods and attributes of type objects are rarely used directly. Type objects are supplied to Table definitions and can be supplied as type hints to functions for occasions where the database driver returns an incorrect type.

>>> users = Table('users', metadata,
...               Column('id', Integer, primary_key=True)
...               Column('login', String(32))
...              )

SQLAlchemy will use the Integer and String(32) type information when issuing a CREATE TABLE statement and will use it again when reading back rows SELECTed from the database. Functions that accept a type (such as Column()) will typically accept a type class or instance; Integer is equivalent to Integer() with no construction arguments in this case.

Generic Types

Generic types specify a column that can read, write and store a particular type of Python data. SQLAlchemy will choose the best database column type available on the target database when issuing a CREATE TABLE statement. For complete control over which column type is emitted in CREATE TABLE, such as VARCHAR see SQL Standard Types and the other sections of this chapter.

class sqlalchemy.types.BigInteger(*args, **kwargs)

Bases: sqlalchemy.types.Integer

A type for bigger int integers.

Typically generates a BIGINT in DDL, and otherwise acts like a normal Integer on the Python side.

class sqlalchemy.types.Boolean(create_constraint=True, name=None)

Bases: sqlalchemy.types.TypeEngine, sqlalchemy.types.SchemaType

A bool datatype.

Boolean typically uses BOOLEAN or SMALLINT on the DDL side, and on the Python side deals in True or False.

__init__(create_constraint=True, name=None)

Construct a Boolean.

Parameters:
  • create_constraint – defaults to True. If the boolean is generated as an int/smallint, also create a CHECK constraint on the table that ensures 1 or 0 as a value.
  • name – if a CHECK constraint is generated, specify the name of the constraint.
class sqlalchemy.types.Date(*args, **kwargs)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeEngine

A type for datetime.date() objects.

class sqlalchemy.types.DateTime(timezone=False)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeEngine

A type for datetime.datetime() objects.

Date and time types return objects from the Python datetime module. Most DBAPIs have built in support for the datetime module, with the noted exception of SQLite. In the case of SQLite, date and time types are stored as strings which are then converted back to datetime objects when rows are returned.

__init__(timezone=False)

Construct a new DateTime.

Parameters:timezone – boolean. If True, and supported by the

backend, will produce ‘TIMESTAMP WITH TIMEZONE’. For backends that don’t support timezone aware timestamps, has no effect.

class sqlalchemy.types.Enum(*enums, **kw)

Bases: sqlalchemy.types.String, sqlalchemy.types.SchemaType

Generic Enum Type.

The Enum type provides a set of possible string values which the column is constrained towards.

By default, uses the backend’s native ENUM type if available, else uses VARCHAR + a CHECK constraint.

See also:

ENUM - PostgreSQL-specific type, which has additional functionality.
__init__(*enums, **kw)

Construct an enum.

Keyword arguments which don’t apply to a specific backend are ignored by that backend.

Parameters:
  • *enums – string or unicode enumeration labels. If unicode labels are present, the convert_unicode flag is auto-enabled.
  • convert_unicode – Enable unicode-aware bind parameter and result-set processing for this Enum’s data. This is set automatically based on the presence of unicode label strings.
  • metadata – Associate this type directly with a MetaData object. For types that exist on the target database as an independent schema construct (Postgresql), this type will be created and dropped within create_all() and drop_all() operations. If the type is not associated with any MetaData object, it will associate itself with each Table in which it is used, and will be created when any of those individual tables are created, after a check is performed for it’s existence. The type is only dropped when drop_all() is called for that Table object’s metadata, however.
  • name – The name of this type. This is required for Postgresql and any future supported database which requires an explicitly named type, or an explicitly named constraint in order to generate the type and/or a table that uses it.
  • native_enum – Use the database’s native ENUM type when available. Defaults to True. When False, uses VARCHAR + check constraint for all backends.
  • schema – Schemaname of this type. For types that exist on the target database as an independent schema construct (Postgresql), this parameter specifies the named schema in which the type is present.
  • quote – Force quoting to be on or off on the type’s name. If left as the default of None, the usual schema-level “case sensitive”/”reserved name” rules are used to determine if this type’s name should be quoted.
create(bind=None, checkfirst=False)

Issue CREATE ddl for this type, if applicable.

drop(bind=None, checkfirst=False)

Issue DROP ddl for this type, if applicable.

class sqlalchemy.types.Float(precision=None, asdecimal=False, **kwargs)

Bases: sqlalchemy.types.Numeric

A type for float numbers.

Returns Python float objects by default, applying conversion as needed.

__init__(precision=None, asdecimal=False, **kwargs)

Construct a Float.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • asdecimal – the same flag as that of Numeric, but defaults to False. Note that setting this flag to True results in floating point conversion.
  • **kwargs – deprecated. Additional arguments here are ignored by the default Float type. For database specific floats that support additional arguments, see that dialect’s documentation for details, such as sqlalchemy.dialects.mysql.FLOAT.
class sqlalchemy.types.Integer(*args, **kwargs)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeEngine

A type for int integers.

class sqlalchemy.types.Interval(native=True, second_precision=None, day_precision=None)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeDecorator

A type for datetime.timedelta() objects.

The Interval type deals with datetime.timedelta objects. In PostgreSQL, the native INTERVAL type is used; for others, the value is stored as a date which is relative to the “epoch” (Jan. 1, 1970).

Note that the Interval type does not currently provide date arithmetic operations on platforms which do not support interval types natively. Such operations usually require transformation of both sides of the expression (such as, conversion of both sides into integer epoch values first) which currently is a manual procedure (such as via func).

__init__(native=True, second_precision=None, day_precision=None)

Construct an Interval object.

Parameters:
  • native – when True, use the actual INTERVAL type provided by the database, if supported (currently Postgresql, Oracle). Otherwise, represent the interval data as an epoch value regardless.
  • second_precision – For native interval types which support a “fractional seconds precision” parameter, i.e. Oracle and Postgresql
  • day_precision – for native interval types which support a “day precision” parameter, i.e. Oracle.
impl

alias of DateTime

class sqlalchemy.types.LargeBinary(length=None)

Bases: sqlalchemy.types._Binary

A type for large binary byte data.

The Binary type generates BLOB or BYTEA when tables are created, and also converts incoming values using the Binary callable provided by each DB-API.

__init__(length=None)

Construct a LargeBinary type.

Parameters:length – optional, a length for the column for use in DDL statements, for those BLOB types that accept a length (i.e. MySQL). It does not produce a small BINARY/VARBINARY type - use the BINARY/VARBINARY types specifically for those. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued.
class sqlalchemy.types.Numeric(precision=None, scale=None, asdecimal=True)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeEngine

A type for fixed precision numbers.

Typically generates DECIMAL or NUMERIC. Returns decimal.Decimal objects by default, applying conversion as needed.

Note

The cdecimal library is a high performing alternative to Python’s built-in decimal.Decimal type, which performs very poorly in high volume situations. SQLAlchemy 0.7 is tested against cdecimal and supports it fully. The type is not necessarily supported by DBAPI implementations however, most of which contain an import for plain decimal in their source code, even though some such as psycopg2 provide hooks for alternate adapters. SQLAlchemy imports decimal globally as well. While the alternate Decimal class can be patched into SQLA’s decimal module, overall the most straightforward and foolproof way to use “cdecimal” given current DBAPI and Python support is to patch it directly into sys.modules before anything else is imported:

import sys
import cdecimal
sys.modules["decimal"] = cdecimal

While the global patch is a little ugly, it’s particularly important to use just one decimal library at a time since Python Decimal and cdecimal Decimal objects are not currently compatible with each other:

>>> import cdecimal
>>> import decimal
>>> decimal.Decimal("10") == cdecimal.Decimal("10")
False

SQLAlchemy will provide more natural support of cdecimal if and when it becomes a standard part of Python installations and is supported by all DBAPIs.

__init__(precision=None, scale=None, asdecimal=True)

Construct a Numeric.

Parameters:
  • precision – the numeric precision for use in DDL CREATE TABLE.
  • scale – the numeric scale for use in DDL CREATE TABLE.
  • asdecimal – default True. Return whether or not values should be sent as Python Decimal objects, or as floats. Different DBAPIs send one or the other based on datatypes - the Numeric type will ensure that return values are one or the other across DBAPIs consistently.

When using the Numeric type, care should be taken to ensure that the asdecimal setting is apppropriate for the DBAPI in use - when Numeric applies a conversion from Decimal->float or float-> Decimal, this conversion incurs an additional performance overhead for all result columns received.

DBAPIs that return Decimal natively (e.g. psycopg2) will have better accuracy and higher performance with a setting of True, as the native translation to Decimal reduces the amount of floating- point issues at play, and the Numeric type itself doesn’t need to apply any further conversions. However, another DBAPI which returns floats natively will incur an additional conversion overhead, and is still subject to floating point data loss - in which case asdecimal=False will at least remove the extra conversion overhead.

class sqlalchemy.types.PickleType(protocol=2, pickler=None, mutable=False, comparator=None)

Bases: sqlalchemy.types.MutableType, sqlalchemy.types.TypeDecorator

Holds Python objects, which are serialized using pickle.

PickleType builds upon the Binary type to apply Python’s pickle.dumps() to incoming objects, and pickle.loads() on the way out, allowing any pickleable Python object to be stored as a serialized binary field.

__init__(protocol=2, pickler=None, mutable=False, comparator=None)

Construct a PickleType.

Parameters:
  • protocol – defaults to pickle.HIGHEST_PROTOCOL.
  • pickler – defaults to cPickle.pickle or pickle.pickle if cPickle is not available. May be any object with pickle-compatible dumps` and ``loads methods.
  • mutable

    defaults to False; implements AbstractType.is_mutable(). When True, incoming objects will be compared against copies of themselves using the Python “equals” operator, unless the comparator argument is present. See MutableType for details on “mutable” type behavior.

    Changed in version 0.7.0: Default changed from True.

    Note

    This functionality is now superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking.

  • comparator – a 2-arg callable predicate used to compare values of this type. If left as None, the Python “equals” operator is used to compare values.
impl

alias of LargeBinary

is_mutable()

Return True if the target Python type is ‘mutable’.

When this method is overridden, copy_value() should also be supplied. The MutableType mixin is recommended as a helper.

class sqlalchemy.types.SchemaType(**kw)

Bases: sqlalchemy.events.SchemaEventTarget

Mark a type as possibly requiring schema-level DDL for usage.

Supports types that must be explicitly created/dropped (i.e. PG ENUM type) as well as types that are complimented by table or schema level constraints, triggers, and other rules.

SchemaType classes can also be targets for the DDLEvents.before_parent_attach() and DDLEvents.after_parent_attach() events, where the events fire off surrounding the association of the type object with a parent Column.

bind
create(bind=None, checkfirst=False)

Issue CREATE ddl for this type, if applicable.

drop(bind=None, checkfirst=False)

Issue DROP ddl for this type, if applicable.

class sqlalchemy.types.SmallInteger(*args, **kwargs)

Bases: sqlalchemy.types.Integer

A type for smaller int integers.

Typically generates a SMALLINT in DDL, and otherwise acts like a normal Integer on the Python side.

class sqlalchemy.types.String(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.Concatenable, sqlalchemy.types.TypeEngine

The base for all string and character types.

In SQL, corresponds to VARCHAR. Can also take Python unicode objects and encode to the database’s encoding in bind params (and the reverse for result sets.)

The length field is usually required when the String type is used within a CREATE TABLE statement, as VARCHAR requires a length on most databases.

__init__(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Create a string-holding type.

Parameters:
  • length – optional, a length for the column for use in DDL statements. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific.
  • convert_unicode

    When set to True, the String type will assume that input is to be passed as Python unicode objects, and results returned as Python unicode objects. If the DBAPI in use does not support Python unicode (which is fewer and fewer these days), SQLAlchemy will encode/decode the value, using the value of the encoding parameter passed to create_engine() as the encoding.

    When using a DBAPI that natively supports Python unicode objects, this flag generally does not need to be set. For columns that are explicitly intended to store non-ASCII data, the Unicode or UnicodeText types should be used regardless, which feature the same behavior of convert_unicode but also indicate an underlying column type that directly supports unicode, such as NVARCHAR.

    For the extremely rare case that Python unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python unicode, the value force can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally.

  • assert_unicode – Deprecated. A warning is emitted when a non-unicode object is passed to the Unicode subtype of String, or the UnicodeText subtype of Text. See Unicode for information on how to control this warning.
  • unicode_error – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions. This flag requires that convert_unicode is set to force - otherwise, SQLAlchemy is not guaranteed to handle the task of unicode conversion. Note that this flag adds significant performance overhead to row-fetching operations for backends that already return unicode objects natively (which most DBAPIs do). This flag should only be used as a last resort for reading strings from a column with varied or corrupted encodings.
class sqlalchemy.types.Text(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.String

A variably sized string type.

In SQL, usually corresponds to CLOB or TEXT. Can also take Python unicode objects and encode to the database’s encoding in bind params (and the reverse for result sets.)

class sqlalchemy.types.Time(timezone=False)

Bases: sqlalchemy.types._DateAffinity, sqlalchemy.types.TypeEngine

A type for datetime.time() objects.

class sqlalchemy.types.Unicode(length=None, **kwargs)

Bases: sqlalchemy.types.String

A variable length Unicode string type.

The Unicode type is a String subclass that assumes input and output as Python unicode data, and in that regard is equivalent to the usage of the convert_unicode flag with the String type. However, unlike plain String, it also implies an underlying column type that is explicitly supporting of non-ASCII data, such as NVARCHAR on Oracle and SQL Server. This can impact the output of CREATE TABLE statements and CAST functions at the dialect level, and can also affect the handling of bound parameters in some specific DBAPI scenarios.

The encoding used by the Unicode type is usually determined by the DBAPI itself; most modern DBAPIs feature support for Python unicode objects as bound values and result set values, and the encoding should be configured as detailed in the notes for the target DBAPI in the Dialects section.

For those DBAPIs which do not support, or are not configured to accommodate Python unicode objects directly, SQLAlchemy does the encoding and decoding outside of the DBAPI. The encoding in this scenario is determined by the encoding flag passed to create_engine().

When using the Unicode type, it is only appropriate to pass Python unicode objects, and not plain str. If a plain str is passed under Python 2, a warning is emitted. If you notice your application emitting these warnings but you’re not sure of the source of them, the Python warnings filter, documented at http://docs.python.org/library/warnings.html, can be used to turn these warnings into exceptions which will illustrate a stack trace:

import warnings
warnings.simplefilter('error')

For an application that wishes to pass plain bytestrings and Python unicode objects to the Unicode type equally, the bytestrings must first be decoded into unicode. The recipe at Coercing Encoded Strings to Unicode illustrates how this is done.

See also:

UnicodeText - unlengthed textual counterpart to Unicode.
__init__(length=None, **kwargs)

Create a Unicode object.

Parameters are the same as that of String, with the exception that convert_unicode defaults to True.

class sqlalchemy.types.UnicodeText(length=None, **kwargs)

Bases: sqlalchemy.types.Text

An unbounded-length Unicode string type.

See Unicode for details on the unicode behavior of this object.

Like Unicode, usage the UnicodeText type implies a unicode-capable type being used on the backend, such as NCLOB, NTEXT.

__init__(length=None, **kwargs)

Create a Unicode-converting Text type.

Parameters are the same as that of Text, with the exception that convert_unicode defaults to True.

SQL Standard Types

The SQL standard types always create database column types of the same name when CREATE TABLE is issued. Some types may not be supported on all databases.

class sqlalchemy.types.BIGINT(*args, **kwargs)

Bases: sqlalchemy.types.BigInteger

The SQL BIGINT type.

class sqlalchemy.types.BINARY(length=None)

Bases: sqlalchemy.types._Binary

The SQL BINARY type.

class sqlalchemy.types.BLOB(length=None)

Bases: sqlalchemy.types.LargeBinary

The SQL BLOB type.

class sqlalchemy.types.BOOLEAN(create_constraint=True, name=None)

Bases: sqlalchemy.types.Boolean

The SQL BOOLEAN type.

class sqlalchemy.types.CHAR(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.String

The SQL CHAR type.

class sqlalchemy.types.CLOB(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.Text

The CLOB type.

This type is found in Oracle and Informix.

class sqlalchemy.types.DATE(*args, **kwargs)

Bases: sqlalchemy.types.Date

The SQL DATE type.

class sqlalchemy.types.DATETIME(timezone=False)

Bases: sqlalchemy.types.DateTime

The SQL DATETIME type.

class sqlalchemy.types.DECIMAL(precision=None, scale=None, asdecimal=True)

Bases: sqlalchemy.types.Numeric

The SQL DECIMAL type.

class sqlalchemy.types.FLOAT(precision=None, asdecimal=False, **kwargs)

Bases: sqlalchemy.types.Float

The SQL FLOAT type.

sqlalchemy.types.INT

alias of INTEGER

class sqlalchemy.types.INTEGER(*args, **kwargs)

Bases: sqlalchemy.types.Integer

The SQL INT or INTEGER type.

class sqlalchemy.types.NCHAR(length=None, **kwargs)

Bases: sqlalchemy.types.Unicode

The SQL NCHAR type.

class sqlalchemy.types.NVARCHAR(length=None, **kwargs)

Bases: sqlalchemy.types.Unicode

The SQL NVARCHAR type.

class sqlalchemy.types.NUMERIC(precision=None, scale=None, asdecimal=True)

Bases: sqlalchemy.types.Numeric

The SQL NUMERIC type.

class sqlalchemy.types.REAL(precision=None, asdecimal=False, **kwargs)

Bases: sqlalchemy.types.Float

The SQL REAL type.

class sqlalchemy.types.SMALLINT(*args, **kwargs)

Bases: sqlalchemy.types.SmallInteger

The SQL SMALLINT type.

class sqlalchemy.types.TEXT(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.Text

The SQL TEXT type.

class sqlalchemy.types.TIME(timezone=False)

Bases: sqlalchemy.types.Time

The SQL TIME type.

class sqlalchemy.types.TIMESTAMP(timezone=False)

Bases: sqlalchemy.types.DateTime

The SQL TIMESTAMP type.

class sqlalchemy.types.VARBINARY(length=None)

Bases: sqlalchemy.types._Binary

The SQL VARBINARY type.

class sqlalchemy.types.VARCHAR(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False)

Bases: sqlalchemy.types.String

The SQL VARCHAR type.

Vendor-Specific Types

Database-specific types are also available for import from each database’s dialect module. See the sqlalchemy.dialects_toplevel reference for the database you’re interested in.

For example, MySQL has a BIGINT type and PostgreSQL has an INET type. To use these, import them from the module explicitly:

from sqlalchemy.dialects import mysql

table = Table('foo', metadata,
    Column('id', mysql.BIGINT),
    Column('enumerates', mysql.ENUM('a', 'b', 'c'))
)

Or some PostgreSQL types:

from sqlalchemy.dialects import postgresql

table = Table('foo', metadata,
    Column('ipaddress', postgresql.INET),
    Column('elements', postgresql.ARRAY(String))
)

Each dialect provides the full set of typenames supported by that backend within its __all__ collection, so that a simple import * or similar will import all supported types as implemented for that backend:

from sqlalchemy.dialects.postgresql import *

t = Table('mytable', metadata,
           Column('id', INTEGER, primary_key=True),
           Column('name', VARCHAR(300)),
           Column('inetaddr', INET)
)

Where above, the INTEGER and VARCHAR types are ultimately from sqlalchemy.types, and INET is specific to the Postgresql dialect.

Some dialect level types have the same name as the SQL standard type, but also provide additional arguments. For example, MySQL implements the full range of character and string types including additional arguments such as collation and charset:

from sqlalchemy.dialects.mysql import VARCHAR, TEXT

table = Table('foo', meta,
    Column('col1', VARCHAR(200, collation='binary')),
    Column('col2', TEXT(charset='latin1'))
)

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.

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

Bases: 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):
        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
__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.

adapt(cls, **kw)
inherited from the TypeEngine.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_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.

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

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.

copy_value(value)

Given a value, produce a copy of it.

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

copy_value() will return the object itself, assuming “mutability” is not enabled. Only the MutableType mixin provides a copy function that actually produces a new object. The copying function is used by the ORM when “mutable” types are used, to memoize the original version of an object as loaded from the database, which is then compared to the possibly mutated version to check for changes.

Modern implementations should use the sqlalchemy.ext.mutable extension described in Mutation Tracking for intercepting in-place changes to values.

dialect_impl(dialect)
inherited from the TypeEngine.dialect_impl() method of TypeEngine

Return a dialect-specific implementation for this TypeEngine.

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

is_mutable()

Return True if the target Python type is ‘mutable’.

This allows systems like the ORM to know if a column value can be considered ‘not changed’ by comparing the identity of objects alone. Values such as dicts, lists which are serialized into strings are examples of “mutable” column structures.

Note

This functionality is now superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking.

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_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 TypeEngine.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 – An 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 TypeEngine.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 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.precision - 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)
            else:
                # hexstring
                return "%.32x" % value

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(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

Note that the ORM by default will not detect “mutability” on such a type - 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. Note that there’s nothing wrong with this, as many applications may not require that the values are ever mutated once created. For those which do have this requirment, support for mutability is best applied using the sqlalchemy.ext.mutable extension - see the example in Mutation Tracking.

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(*args, **kwargs)

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):
        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))
    )
__init__(*args, **kwargs)
inherited from the TypeEngine.__init__() method of TypeEngine

Support implementations that were passing arguments

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

adapt_operator(op)

A hook which allows the given operator to be adapted to something new.

See also UserDefinedType._adapt_expression(), an as-yet- semi-public method with greater capability in this regard.

bind_processor(dialect)
inherited from the TypeEngine.bind_processor() method of TypeEngine

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.
compare_values(x, y)
inherited from the TypeEngine.compare_values() method of TypeEngine

Compare two values for equality.

compile(dialect=None)
inherited from the TypeEngine.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_value(value)
inherited from the TypeEngine.copy_value() method of TypeEngine
dialect_impl(dialect)
inherited from the TypeEngine.dialect_impl() method of TypeEngine

Return a dialect-specific implementation for this TypeEngine.

get_dbapi_type(dbapi)
inherited from the TypeEngine.get_dbapi_type() method of TypeEngine

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

This can be useful for calling setinputsizes(), for example.
is_mutable()
inherited from the TypeEngine.is_mutable() method of TypeEngine

Return True if the target Python type is ‘mutable’.

This allows systems like the ORM to know if a column value can be considered ‘not changed’ by comparing the identity of objects alone. Values such as dicts, lists which are serialized into strings are examples of “mutable” column structures.

Note

This functionality is now superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking.

When this method is overridden, copy_value() should also be supplied. The MutableType mixin is recommended as a helper.

python_type
inherited from the TypeEngine.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)
inherited from the TypeEngine.result_processor() method of TypeEngine

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)
inherited from the TypeEngine.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 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.

Base Type API

class sqlalchemy.types.AbstractType

Bases: sqlalchemy.sql.visitors.Visitable

Base for all types - not needed except for backwards compatibility.

__init__
inherited from the object.__init__ attribute of object

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

class sqlalchemy.types.TypeEngine(*args, **kwargs)

Bases: sqlalchemy.types.AbstractType

Base for built-in types.

__init__(*args, **kwargs)

Support implementations that were passing arguments

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_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.
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.
copy_value(value)
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.
is_mutable()

Return True if the target Python type is ‘mutable’.

This allows systems like the ORM to know if a column value can be considered ‘not changed’ by comparing the identity of objects alone. Values such as dicts, lists which are serialized into strings are examples of “mutable” column structures.

Note

This functionality is now superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking.

When this method is overridden, copy_value() should also be supplied. The MutableType mixin is recommended as a helper.

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

Bases: object

A mixin that marks a TypeEngine as representing a mutable Python object type. This functionality is used only by the ORM.

Changed in version 0.7: MutableType is superseded by the sqlalchemy.ext.mutable extension described in Mutation Tracking. This extension provides an event driven approach to in-place mutation detection that does not incur the severe performance penalty of the MutableType approach.

“mutable” means that changes can occur in place to a value of this type. Examples includes Python lists, dictionaries, and sets, as well as user-defined objects. The primary need for identification of “mutable” types is by the ORM, which applies special rules to such values in order to guarantee that changes are detected. These rules may have a significant performance impact, described below.

A MutableType usually allows a flag called mutable=False to enable/disable the “mutability” flag, represented on this class by is_mutable(). Examples include PickleType and ARRAY. Setting this flag to True enables mutability-specific behavior by the ORM.

The copy_value() and compare_values() functions represent a copy and compare function for values of this type - implementing subclasses should override these appropriately.

Warning

The usage of mutable types has significant performance implications when using the ORM. In order to detect changes, the ORM must create a copy of the value when it is first accessed, so that changes to the current value can be compared against the “clean” database-loaded value. Additionally, when the ORM checks to see if any data requires flushing, it must scan through all instances in the session which are known to have “mutable” attributes and compare the current value of each one to its “clean” value. So for example, if the Session contains 6000 objects (a fairly large amount) and autoflush is enabled, every individual execution of Query will require a full scan of that subset of the 6000 objects that have mutable attributes, possibly resulting in tens of thousands of additional method calls for every query.

Changed in version 0.7: As of SQLAlchemy 0.7, the sqlalchemy.ext.mutable is provided which allows an event driven approach to in-place mutation detection. This approach should now be favored over the usage of MutableType with mutable=True. sqlalchemy.ext.mutable is described in Mutation Tracking.

__init__
inherited from the object.__init__ attribute of object

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

compare_values(x, y)

Compare x == y.

copy_value(value)

Unimplemented.

is_mutable()

Return True if the target Python type is ‘mutable’.

For MutableType, this method is set to return True.

class sqlalchemy.types.Concatenable

Bases: object

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

__init__
inherited from the object.__init__ attribute of object

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

class sqlalchemy.types.NullType(*args, **kwargs)

Bases: sqlalchemy.types.TypeEngine

An unknown type.

NullTypes will stand in if Table reflection encounters a column data type unknown to SQLAlchemy. The resulting columns are nearly fully usable: the DB-API adapter will handle all translation to and from the database data type.

NullType does not have sufficient information to particpate in a CREATE TABLE statement and will raise an exception if encountered during a create() operation.

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.

__init__(base, mapping)

Construct a new Variant.

Parameters:
  • base – the base ‘fallback’ type
  • mapping – dictionary of string dialect names to TypeEngine instances.
with_variant(type_, dialect_name)

Return a new Variant which adds the given type + dialect name to the mapping, in addition to the mapping present in this Variant.

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