1
1
# sqlalchemy/types.py
2
# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file>
2
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
4
4
# This module is part of SQLAlchemy and is released under
5
5
# the MIT License: http://www.opensource.org/licenses/mit-license.php
7
7
"""defines genericized SQL types, each represented by a subclass of
8
:class:`~sqlalchemy.types.AbstractType`. Dialects define further subclasses of these
8
:class:`~sqlalchemy.types.AbstractType`. Dialects define further subclasses
11
11
For more information see the SQLAlchemy documentation on types.
14
__all__ = [ 'TypeEngine', 'TypeDecorator', 'AbstractType', 'UserDefinedType',
15
'INT', 'CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR','TEXT', 'Text',
14
__all__ = ['TypeEngine', 'TypeDecorator', 'AbstractType', 'UserDefinedType',
15
'INT', 'CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'TEXT', 'Text',
16
16
'FLOAT', 'NUMERIC', 'REAL', 'DECIMAL', 'TIMESTAMP', 'DATETIME',
17
'CLOB', 'BLOB', 'BINARY', 'VARBINARY', 'BOOLEAN', 'BIGINT', 'SMALLINT',
18
'INTEGER', 'DATE', 'TIME', 'String', 'Integer', 'SmallInteger',
19
'BigInteger', 'Numeric', 'Float', 'DateTime', 'Date', 'Time',
20
'LargeBinary', 'Binary', 'Boolean', 'Unicode', 'Concatenable',
21
'UnicodeText','PickleType', 'Interval', 'Enum', 'MutableType' ]
17
'CLOB', 'BLOB', 'BINARY', 'VARBINARY', 'BOOLEAN', 'BIGINT',
18
'SMALLINT', 'INTEGER', 'DATE', 'TIME', 'String', 'Integer',
19
'SmallInteger', 'BigInteger', 'Numeric', 'Float', 'DateTime',
20
'Date', 'Time', 'LargeBinary', 'Binary', 'Boolean', 'Unicode',
21
'Concatenable', 'UnicodeText', 'PickleType', 'Interval', 'Enum']
24
23
import datetime as dt
27
from sqlalchemy import exc, schema
28
from sqlalchemy.sql import expression, operators
29
from sqlalchemy.util import pickle
30
from sqlalchemy.util.compat import decimal
31
from sqlalchemy.sql.visitors import Visitable
32
from sqlalchemy import util
33
from sqlalchemy import processors, events, event
26
from . import exc, schema, util, processors, events, event
27
from .sql import operators
28
from .sql.expression import _DefaultColumnComparator
29
from .util import pickle
30
from .sql.visitors import Visitable
35
32
default = util.importlater("sqlalchemy.engine", "default")
37
34
NoneType = type(None)
41
39
class AbstractType(Visitable):
42
40
"""Base for all types - not needed except for backwards
45
44
class TypeEngine(AbstractType):
46
45
"""Base for built-in types."""
47
class Comparator(_DefaultColumnComparator):
48
"""Base class for custom comparison operations defined at the
49
type level. See :attr:`.TypeEngine.comparator_factory`.
51
The public base class for :class:`.TypeEngine.Comparator`
52
is :class:`.ColumnOperators`.
56
def __init__(self, expr):
60
return _reconstitute_comparator, (self.expr, )
63
"""Flag, if False, means values from this type aren't hashable.
65
Used by the ORM when uniquing result lists.
69
comparator_factory = Comparator
70
"""A :class:`.TypeEngine.Comparator` class which will apply
71
to operations performed by owning :class:`.ColumnElement` objects.
73
The :attr:`.comparator_factory` attribute is a hook consulted by
74
the core expression system when column and SQL expression operations
75
are performed. When a :class:`.TypeEngine.Comparator` class is
76
associated with this attribute, it allows custom re-definition of
77
all existing operators, as well as definition of new operators.
78
Existing operators include those provided by Python operator overloading
79
such as :meth:`.operators.ColumnOperators.__add__` and
80
:meth:`.operators.ColumnOperators.__eq__`,
81
those provided as standard
82
attributes of :class:`.operators.ColumnOperators` such as
83
:meth:`.operators.ColumnOperators.like`
84
and :meth:`.operators.ColumnOperators.in_`.
86
Rudimentary usage of this hook is allowed through simple subclassing
87
of existing types, or alternatively by using :class:`.TypeDecorator`.
88
See the documentation section :ref:`types_operators` for examples.
90
.. versionadded:: 0.8 The expression system was enhanced to support
91
customization of operators on a per-type level.
48
95
def copy_value(self, value):
128
def column_expression(self, colexpr):
129
"""Given a SELECT column expression, return a wrapping SQL expression.
131
This is typically a SQL function that wraps a column expression
132
as rendered in the columns clause of a SELECT statement.
133
It is used for special data types that require
134
columns to be wrapped in some special database function in order
135
to coerce the value before being sent back to the application.
136
It is the SQL analogue of the :meth:`.TypeEngine.result_processor`
139
The method is evaluated at statement compile time, as opposed
140
to statement construction time.
144
:ref:`types_sql_value_processing`
150
@util.memoized_property
151
def _has_column_expression(self):
152
"""memoized boolean, check if column_expression is implemented.
154
Allows the method to be skipped for the vast majority of expression
155
types that don't use this feature.
159
return self.__class__.column_expression.func_code \
160
is not TypeEngine.column_expression.func_code
162
def bind_expression(self, bindvalue):
163
""""Given a bind value (i.e. a :class:`.BindParameter` instance),
164
return a SQL expression in its place.
166
This is typically a SQL function that wraps the existing bound
167
parameter within the statement. It is used for special data types
168
that require literals being wrapped in some special database function
169
in order to coerce an application-level value into a database-specific
170
format. It is the SQL analogue of the
171
:meth:`.TypeEngine.bind_processor` method.
173
The method is evaluated at statement compile time, as opposed
174
to statement construction time.
176
Note that this method, when implemented, should always return
177
the exact same structure, without any conditional logic, as it
178
may be used in an executemany() call against an arbitrary number
179
of bound parameter sets.
183
:ref:`types_sql_value_processing`
188
@util.memoized_property
189
def _has_bind_expression(self):
190
"""memoized boolean, check if bind_expression is implemented.
192
Allows the method to be skipped for the vast majority of expression
193
types that don't use this feature.
197
return self.__class__.bind_expression.func_code \
198
is not TypeEngine.bind_expression.func_code
81
200
def compare_values(self, x, y):
82
201
"""Compare two values for equality."""
87
"""Return True if the target Python type is 'mutable'.
89
This allows systems like the ORM to know if a column value can
90
be considered 'not changed' by comparing the identity of
91
objects alone. Values such as dicts, lists which
92
are serialized into strings are examples of "mutable"
97
This functionality is now superseded by the
98
``sqlalchemy.ext.mutable`` extension described in
99
:ref:`mutable_toplevel`.
101
When this method is overridden, :meth:`copy_value` should
102
also be supplied. The :class:`.MutableType` mixin
103
is recommended as a helper.
108
205
def get_dbapi_type(self, dbapi):
109
206
"""Return the corresponding type object from the underlying DB-API, if
353
450
__visit_name__ = "user_defined"
355
def _adapt_expression(self, op, othertype):
356
"""evaluate the return type of <self> <op> <othertype>,
357
and apply any adaptations to the given operator.
360
return self.adapt_operator(op), self
362
def adapt_operator(self, op):
363
"""A hook which allows the given operator to be adapted
366
See also UserDefinedType._adapt_expression(), an as-yet-
367
semi-public method with greater capability in this regard.
452
class Comparator(TypeEngine.Comparator):
453
def _adapt_expression(self, op, other_comparator):
454
if hasattr(self.type, 'adapt_operator'):
455
util.warn_deprecated(
456
"UserDefinedType.adapt_operator is deprecated. Create "
457
"a UserDefinedType.Comparator subclass instead which "
458
"generates the desired expression constructs, given a "
459
"particular operator."
461
return self.type.adapt_operator(op), self.type
465
comparator_factory = Comparator
467
def coerce_compared_value(self, op, value):
468
"""Suggest a type for a 'coerced' Python value in an expression.
470
Default behavior for :class:`.UserDefinedType` is the
471
same as that of :class:`.TypeDecorator`; by default it returns
472
``self``, assuming the compared value should be coerced into
473
the same type as this one. See
474
:meth:`.TypeDecorator.coerce_compared_value` for more detail.
476
.. versionchanged:: 0.8 :meth:`.UserDefinedType.coerce_compared_value`
477
now returns ``self`` by default, rather than falling onto the
478
more fundamental behavior of
479
:meth:`.TypeEngine.coerce_compared_value`.
372
486
class TypeDecorator(TypeEngine):
373
487
"""Allows the creation of types which add additional functionality
477
591
"type being decorated")
478
592
self.impl = to_instance(self.__class__.impl, *args, **kwargs)
594
coerce_to_is_types = (util.NoneType, )
595
"""Specify those Python types which should be coerced at the expression
596
level to "IS <constant>" when compared using ``==`` (and same for
597
``IS NOT`` in conjunction with ``!=``.
599
For most SQLAlchemy types, this includes ``NoneType``, as well as ``bool``.
601
:class:`.TypeDecorator` modifies this list to only include ``NoneType``,
602
as typedecorator implementations that deal with boolean types are common.
604
Custom :class:`.TypeDecorator` classes can override this attribute to
605
return an empty tuple, in which case no values will be coerced to
608
..versionadded:: 0.8.2
609
Added :attr:`.TypeDecorator.coerce_to_is_types` to allow for easier
610
control of ``__eq__()`` ``__ne__()`` operations.
614
class Comparator(TypeEngine.Comparator):
615
def operate(self, op, *other, **kwargs):
616
kwargs['_python_is_types'] = self.expr.type.coerce_to_is_types
617
return super(TypeDecorator.Comparator, self).operate(
618
op, *other, **kwargs)
620
def reverse_operate(self, op, other, **kwargs):
621
kwargs['_python_is_types'] = self.expr.type.coerce_to_is_types
622
return super(TypeDecorator.Comparator, self).reverse_operate(
626
def comparator_factory(self):
627
return type("TDComparator",
628
(TypeDecorator.Comparator, self.impl.comparator_factory),
481
631
def _gen_dialect_impl(self, dialect):
705
876
def get_dbapi_type(self, dbapi):
706
"""Return the DBAPI type object represented by this :class:`.TypeDecorator`.
877
"""Return the DBAPI type object represented by this
878
:class:`.TypeDecorator`.
708
880
By default this calls upon :meth:`.TypeEngine.get_dbapi_type` of the
709
881
underlying "impl".
711
883
return self.impl.get_dbapi_type(dbapi)
713
def copy_value(self, value):
714
"""Given a value, produce a copy of it.
716
By default this calls upon :meth:`.TypeEngine.copy_value`
717
of the underlying "impl".
719
:meth:`.copy_value` will return the object
720
itself, assuming "mutability" is not enabled.
721
Only the :class:`.MutableType` mixin provides a copy
722
function that actually produces a new object.
723
The copying function is used by the ORM when
724
"mutable" types are used, to memoize the original
725
version of an object as loaded from the database,
726
which is then compared to the possibly mutated
727
version to check for changes.
729
Modern implementations should use the
730
``sqlalchemy.ext.mutable`` extension described in
731
:ref:`mutable_toplevel` for intercepting in-place
735
return self.impl.copy_value(value)
737
885
def compare_values(self, x, y):
738
886
"""Given two values, compare them for equality.
826
950
mapping[dialect_name] = type_
827
951
return Variant(self.impl, mapping)
829
class MutableType(object):
830
"""A mixin that marks a :class:`.TypeEngine` as representing
831
a mutable Python object type. This functionality is used
834
.. versionchanged:: 0.7
835
:class:`.MutableType` is superseded
836
by the ``sqlalchemy.ext.mutable`` extension described in
837
:ref:`mutable_toplevel`. This extension provides an event
838
driven approach to in-place mutation detection that does not
839
incur the severe performance penalty of the :class:`.MutableType`
842
"mutable" means that changes can occur in place to a value
843
of this type. Examples includes Python lists, dictionaries,
844
and sets, as well as user-defined objects. The primary
845
need for identification of "mutable" types is by the ORM,
846
which applies special rules to such values in order to guarantee
847
that changes are detected. These rules may have a significant
848
performance impact, described below.
850
A :class:`.MutableType` usually allows a flag called
851
``mutable=False`` to enable/disable the "mutability" flag,
852
represented on this class by :meth:`is_mutable`. Examples
853
include :class:`.PickleType` and
854
:class:`~sqlalchemy.dialects.postgresql.base.ARRAY`. Setting
855
this flag to ``True`` enables mutability-specific behavior
858
The :meth:`copy_value` and :meth:`compare_values` functions
859
represent a copy and compare function for values of this
860
type - implementing subclasses should override these
865
The usage of mutable types has significant performance
866
implications when using the ORM. In order to detect changes, the
867
ORM must create a copy of the value when it is first
868
accessed, so that changes to the current value can be compared
869
against the "clean" database-loaded value. Additionally, when the
870
ORM checks to see if any data requires flushing, it must scan
871
through all instances in the session which are known to have
872
"mutable" attributes and compare the current value of each
874
value. So for example, if the Session contains 6000 objects (a
875
fairly large amount) and autoflush is enabled, every individual
876
execution of :class:`.Query` will require a full scan of that subset of
877
the 6000 objects that have mutable attributes, possibly resulting
878
in tens of thousands of additional method calls for every query.
880
.. versionchanged:: 0.7
881
As of SQLAlchemy 0.7, the ``sqlalchemy.ext.mutable`` is provided
882
which allows an event driven approach to in-place
883
mutation detection. This approach should now be favored over
884
the usage of :class:`.MutableType` with ``mutable=True``.
885
``sqlalchemy.ext.mutable`` is described in :ref:`mutable_toplevel`.
889
def is_mutable(self):
890
"""Return True if the target Python type is 'mutable'.
892
For :class:`.MutableType`, this method is set to
898
def copy_value(self, value):
900
raise NotImplementedError()
902
def compare_values(self, x, y):
903
"""Compare *x* == *y*."""
906
954
def to_instance(typeobj, *arg, **kw):
907
955
if typeobj is None:
952
999
__visit_name__ = 'null'
954
def _adapt_expression(self, op, othertype):
955
if isinstance(othertype, NullType) or not operators.is_commutative(op):
958
return othertype._adapt_expression(op, self)
1001
class Comparator(TypeEngine.Comparator):
1002
def _adapt_expression(self, op, other_comparator):
1003
if isinstance(other_comparator, NullType.Comparator) or \
1004
not operators.is_commutative(op):
1005
return op, self.expr.type
1007
return other_comparator._adapt_expression(op, self)
1008
comparator_factory = Comparator
960
1010
NullTypeEngine = NullType
962
1013
class Concatenable(object):
963
1014
"""A mixin that marks a type as supporting 'concatenation',
964
1015
typically strings."""
966
def _adapt_expression(self, op, othertype):
967
if op is operators.add and issubclass(othertype._type_affinity,
968
(Concatenable, NullType)):
969
return operators.concat_op, self
1017
class Comparator(TypeEngine.Comparator):
1018
def _adapt_expression(self, op, other_comparator):
1019
if op is operators.add and isinstance(other_comparator,
1020
(Concatenable.Comparator, NullType.Comparator)):
1021
return operators.concat_op, self.expr.type
1023
return op, self.expr.type
1025
comparator_factory = Comparator
973
1028
class _DateAffinity(object):
974
1029
"""Mixin date/time specific expression adaptations.
1006
1065
__visit_name__ = 'string'
1008
def __init__(self, length=None, convert_unicode=False,
1009
assert_unicode=None, unicode_error=None,
1067
def __init__(self, length=None, collation=None,
1068
convert_unicode=False,
1010
1070
_warn_on_bytestring=False
1013
1073
Create a string-holding type.
1015
1075
:param length: optional, a length for the column for use in
1016
DDL statements. May be safely omitted if no ``CREATE
1076
DDL and CAST expressions. May be safely omitted if no ``CREATE
1017
1077
TABLE`` will be issued. Certain databases may require a
1018
1078
``length`` for use in DDL, and will raise an exception when
1019
1079
the ``CREATE TABLE`` DDL is issued if a ``VARCHAR``
1020
1080
with no length is included. Whether the value is
1021
1081
interpreted as bytes or characters is database specific.
1083
:param collation: Optional, a column-level collation for
1084
use in DDL and CAST expressions. Renders using the
1085
COLLATE keyword supported by SQLite, MySQL, and Postgresql.
1088
>>> from sqlalchemy import cast, select, String
1089
>>> print select([cast('some string', String(collation='utf8'))])
1090
SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
1092
.. versionadded:: 0.8 Added support for COLLATE to all
1023
1095
:param convert_unicode: When set to ``True``, the
1024
1096
:class:`.String` type will assume that
1025
1097
input is to be passed as Python ``unicode`` objects,
1896
1999
available. Defaults to True. When False, uses VARCHAR + check
1897
2000
constraint for all backends.
1899
:param schema: Schemaname of this type. For types that exist on the
2002
:param schema: Schema name of this type. For types that exist on the
1900
2003
target database as an independent schema construct (Postgresql),
1901
2004
this parameter specifies the named schema in which the type is
2009
The ``schema`` of the :class:`.Enum` type does not
2010
by default make use of the ``schema`` established on the
2011
owning :class:`.Table`. If this behavior is desired,
2012
set the ``inherit_schema`` flag to ``True``.
1904
2014
:param quote: Force quoting to be on or off on the type's name. If
1905
2015
left as the default of `None`, the usual schema-level "case
1906
2016
sensitive"/"reserved name" rules are used to determine if this
1907
2017
type's name should be quoted.
2019
:param inherit_schema: When ``True``, the "schema" from the owning
2020
:class:`.Table` will be copied to the "schema" attribute of this
2021
:class:`.Enum`, replacing whatever value was passed for the
2022
``schema`` attribute. This also takes effect when using the
2023
:meth:`.Table.tometadata` operation.
2025
.. versionadded:: 0.8
1910
2028
self.enums = enums
1911
2029
self.native_enum = kw.pop('native_enum', True)