Previous Topic

Next Topic

Project Versions

Quick Search

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.

SQLAlchemy will use the Integer and String(32) type
information when issuing a CREATETABLE 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 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
CREATETABLE statement. For complete control over which column
type is emitted in CREATETABLE, such as VARCHAR see SQL
Standard Types and the other sections of this chapter.

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.

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.

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

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 CREATETABLE will be issued. Certain databases may require a
length for use in DDL, and will raise an exception when
the CREATETABLE DDL is issued.

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.

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.

Note: be sure to read the notes for MutableType regarding
ORM performance implications.

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 True; implements
AbstractType.is_mutable(). When True, incoming
objects should provide an __eq__() method which
performs the desired deep comparison of members, or the
comparator argument must be present.

comparator – optional. a 2-arg callable predicate used
to compare values of this type. Otherwise,
the == operator is used to compare values.

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

convert_unicode –

defaults to False. If True, the
type will do what is necessary in order to accept
Python Unicode objects as bind parameters, and to return
Python Unicode objects in result rows. This may
require SQLAlchemy to explicitly coerce incoming Python
unicodes into an encoding, and from an encoding
back to Unicode, or it may not require any interaction
from SQLAlchemy at all, depending on the DBAPI in use.

When SQLAlchemy performs the encoding/decoding,
the encoding used is configured via
encoding, which
defaults to utf-8.

The “convert_unicode” behavior can also be turned on
for all String types by setting
sqlalchemy.engine.base.Dialect.convert_unicode
on create_engine().

To instruct SQLAlchemy to perform Unicode encoding/decoding
even on a platform that already handles Unicode natively,
set convert_unicode=’force’. This will incur significant
performance overhead when fetching unicode result columns.

assert_unicode – Deprecated. A warning is raised in all cases
when a non-Unicode object is passed when SQLAlchemy would coerce
into an encoding (note: but not when the DBAPI handles unicode
objects natively). To suppress or raise this warning to an error,
use the Python warnings filter documented at:
http://docs.python.org/library/warnings.html

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 an absolute last resort for reading
strings from a column with varied or corrupted encodings,
which only applies to databases that accept invalid encodings
in the first place (i.e. MySQL. not PG, Sqlite, etc.)

The Unicode type is a String which converts Python
unicode objects (i.e., strings that are defined as
u'somevalue') into encoded bytestrings when passing the value
to the database driver, and similarly decodes values from the
database back into Python unicode objects.

It’s roughly equivalent to using a String object with
convert_unicode=True, however
the type has other significances in that it implies the usage
of a unicode-capable type being used on the backend, such as NVARCHAR.
This may affect what type is emitted when issuing CREATE TABLE
and also may effect some DBAPI-specific details, such as type
information passed along to setinputsizes().

When using the Unicode type, it is only appropriate to pass
Python unicode objects, and not plain str. If a
bytestring (str) is passed, a runtime warning is issued. If
you notice your application raising these warnings but you’re not
sure where, the Python warnings filter can be used to turn
these warnings into exceptions which will illustrate a stack
trace:

importwarningswarnings.simplefilter('error')

Bytestrings sent to and received from the database are encoded
using the dialect’s
encoding, which defaults
to utf-8.

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

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

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:

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:

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:

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 marshalling of data
to and from the database is required.

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:

importsqlalchemy.typesastypesclassMyType(types.TypeDecorator):'''Prefixes Unicode values with "PREFIX:" on the way in and strips it off on the way out. '''impl=types.Unicodedefprocess_bind_param(self,value,dialect):return"PREFIX:"+valuedefprocess_result_value(self,value,dialect):returnvalue[7:]defcopy(self):returnMyType(self.impl.length)

The class-level “impl” variable 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:

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:

Arguments sent here are passed to the constructor
of the class assigned to the impl class level attribute,
where the self.impl attribute is assigned an instance
of the implementation type. If impl at the class level
is already an instance, then it’s assigned to self.impl
as is.

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

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.

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

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.

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

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()
and dialect_impl() to help determine what type should ultimately be returned
for a given TypeDecorator.

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.

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:

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

Note that the base type is not “mutable”, meaning in-place changes to
the value will not be detected by the ORM - you instead would need to
replace the existing value with a new one to detect changes.
The subtype MutableJSONEncodedDict
adds “mutability” to allow this, but note that “mutable” types add
a significant performance penalty to the ORM’s flush process:

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.

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

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.

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

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.

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

A mixin that marks a TypeEngine as representing
a mutable Python object type.

“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=True to enable/disable the “mutability” flag,
represented on this class by is_mutable(). Examples
include PickleType and
ARRAY. Setting
this flag to False effectively disables any mutability-
specific behavior by the ORM.

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

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.

Note that for small numbers (< 100 in the Session at a time)
of objects with “mutable” values, the performance degradation is
negligible. In most cases it’s likely that the convenience allowed
by “mutable” change detection outweighs the performance penalty.

It is perfectly fine to represent “mutable” data types with the
“mutable” flag set to False, which eliminates any performance
issues. It means that the ORM will only reliably detect changes
for values of this type if a newly modified value is of a different
identity (i.e., id(value)) than what was present before -
i.e., instead of operations like these:

A future release of SQLAlchemy will include instrumented
collection support for mutable types, such that at least usage of
plain Python datastructures will be able to emit events for
in-place changes, removing the need for pessimistic scanning for
changes.

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
CREATETABLE statement and will raise an exception if
encountered during a create() operation.