Object Name | Description |
---|---|
A mixin that marks a type as supporting ‘concatenation’, typically strings. |
|
mixin that defines attributes and behaviors specific to third-party datatypes. |
|
A mixin that marks a type as supporting indexing operations, such as array or JSON structures. |
|
An unknown type. |
|
The ultimate base class for all SQL datatypes. |
|
A wrapping type that selects among a variety of implementations based on dialect in use. |
sqlalchemy.types.
TypeEngine
¶The ultimate base class for all SQL datatypes.
Common subclasses of TypeEngine
include
String
, Integer
, and Boolean
.
For an overview of the SQLAlchemy typing system, see Column and Data Types.
See also
Class signature
class sqlalchemy.types.TypeEngine
(sqlalchemy.sql.visitors.Traversible
)
Comparator
(expr)¶Base class for custom comparison operations defined at the
type level. See TypeEngine.comparator_factory
.
Class signature
class sqlalchemy.types.TypeEngine.Comparator
(sqlalchemy.sql.expression.ColumnOperators
)
sqlalchemy.types.TypeEngine.Comparator.
default_comparator
= None¶sqlalchemy.types.TypeEngine.Comparator.
operate
(op, *other, **kwargs)¶Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower()
to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
sqlalchemy.types.TypeEngine.Comparator.
reverse_operate
(op, other, **kwargs)¶Reverse operate on an argument.
Usage is the same as operate()
.
sqlalchemy.types.TypeEngine.
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.
sqlalchemy.types.TypeEngine.
as_generic
(allow_nulltype=False)¶Return an instance of the generic type corresponding to this type using heuristic rule. The method may be overridden if this heuristic rule is not sufficient.
>>> from sqlalchemy.dialects.mysql import INTEGER
>>> INTEGER(display_width=4).as_generic()
Integer()
>>> from sqlalchemy.dialects.mysql import NVARCHAR
>>> NVARCHAR(length=100).as_generic()
Unicode(length=100)
New in version 1.4.0b2.
See also
Reflecting with Database-Agnostic Types - describes the
use of TypeEngine.as_generic()
in conjunction with
the DDLEvents.column_reflect()
event, which is its
intended use.
sqlalchemy.types.TypeEngine.
bind_expression
(bindvalue)¶Given a bind value (i.e. a BindParameter
instance),
return a SQL expression in its place.
This is typically a SQL function that wraps the existing bound
parameter within the statement. It is used for special data types
that require literals being wrapped in some special database function
in order to coerce an application-level value into a database-specific
format. It is the SQL analogue of the
TypeEngine.bind_processor()
method.
This method is called during the SQL compilation phase of a statement, when rendering a SQL string. It is not called against specific values.
Note that this method, when implemented, should always return the exact same structure, without any conditional logic, as it may be used in an executemany() call against an arbitrary number of bound parameter sets.
Note
This method is only called relative to a dialect specific type
object, which is often private to a dialect in use and is not
the same type object as the public facing one, which means it’s not
feasible to subclass a TypeEngine
class in order to
provide an alternate TypeEngine.bind_expression()
method, unless subclassing the UserDefinedType
class explicitly.
To provide alternate behavior for
TypeEngine.bind_expression()
, implement a
TypeDecorator
class and provide an implementation
of TypeDecorator.bind_expression()
.
See also
sqlalchemy.types.TypeEngine.
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
.
Note
This method is only called relative to a dialect specific type
object, which is often private to a dialect in use and is not
the same type object as the public facing one, which means it’s not
feasible to subclass a TypeEngine
class in order to
provide an alternate TypeEngine.bind_processor()
method, unless subclassing the UserDefinedType
class explicitly.
To provide alternate behavior for
TypeEngine.bind_processor()
, implement a
TypeDecorator
class and provide an implementation
of TypeDecorator.process_bind_param()
.
See also
dialect¶ – Dialect instance in use.
sqlalchemy.types.TypeEngine.
coerce_compared_value
(op, value)¶Suggest a type for a ‘coerced’ Python value in an expression.
Given an operator and value, gives the type a chance to return a type which the value should be coerced into.
The default behavior here is conservative; if the right-hand side is already coerced into a SQL type based on its Python type, it is usually left alone.
End-user functionality extension here should generally be via
TypeDecorator
, which provides more liberal behavior in that
it defaults to coercing the other side of the expression into this
type, thus applying special Python conversions above and beyond those
needed by the DBAPI to both ides. It also provides the public method
TypeDecorator.coerce_compared_value()
which is intended for
end-user customization of this behavior.
sqlalchemy.types.TypeEngine.
column_expression
(colexpr)¶Given a SELECT column expression, return a wrapping SQL expression.
This is typically a SQL function that wraps a column expression
as rendered in the columns clause of a SELECT statement.
It is used for special data types that require
columns to be wrapped in some special database function in order
to coerce the value before being sent back to the application.
It is the SQL analogue of the TypeEngine.result_processor()
method.
This method is called during the SQL compilation phase of a statement, when rendering a SQL string. It is not called against specific values.
Note
This method is only called relative to a dialect specific type
object, which is often private to a dialect in use and is not
the same type object as the public facing one, which means it’s not
feasible to subclass a TypeEngine
class in order to
provide an alternate TypeEngine.column_expression()
method, unless subclassing the UserDefinedType
class explicitly.
To provide alternate behavior for
TypeEngine.column_expression()
, implement a
TypeDecorator
class and provide an implementation
of TypeDecorator.column_expression()
.
See also
sqlalchemy.types.TypeEngine.
comparator_factory
¶A Comparator
class which will apply
to operations performed by owning ColumnElement
objects.
The comparator_factory
attribute is a hook consulted by
the core expression system when column and SQL expression operations
are performed. When a Comparator
class is
associated with this attribute, it allows custom re-definition of
all existing operators, as well as definition of new operators.
Existing operators include those provided by Python operator overloading
such as ColumnOperators.__add__()
and
ColumnOperators.__eq__()
,
those provided as standard
attributes of ColumnOperators
such as
ColumnOperators.like()
and ColumnOperators.in_()
.
Rudimentary usage of this hook is allowed through simple subclassing
of existing types, or alternatively by using TypeDecorator
.
See the documentation section Redefining and Creating New Operators for examples.
Class signature
class sqlalchemy.types.TypeEngine.Comparator
(sqlalchemy.sql.expression.ColumnOperators
)
alias of sqlalchemy.sql.type_api.TypeEngine.Comparator
sqlalchemy.types.TypeEngine.
compare_against_backend
(dialect, conn_type)¶Compare this type against the given backend type.
This function is currently not implemented for SQLAlchemy
types, and for all built in types will return None
. However,
it can be implemented by a user-defined type
where it can be consumed by schema comparison tools such as
Alembic autogenerate.
A future release of SQLAlchemy will potentially implement this method for builtin types as well.
The function should return True if this type is equivalent to the given type; the type is typically reflected from the database so should be database specific. The dialect in use is also passed. It can also return False to assert that the type is not equivalent.
New in version 1.0.3.
sqlalchemy.types.TypeEngine.
compare_values
(x, y)¶Compare two values for equality.
sqlalchemy.types.TypeEngine.
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.
sqlalchemy.types.TypeEngine.
dialect_impl
(dialect)¶Return a dialect-specific implementation for this
TypeEngine
.
sqlalchemy.types.TypeEngine.
evaluates_none
()¶Return a copy of this type which has the should_evaluate_none
flag set to True.
E.g.:
Table(
'some_table', metadata,
Column(
String(50).evaluates_none(),
nullable=True,
server_default='no value')
)
The ORM uses this flag to indicate that a positive value of None
is passed to the column in an INSERT statement, rather than omitting
the column from the INSERT statement which has the effect of firing
off column-level defaults. It also allows for types which have
special behavior associated with the Python None value to indicate
that the value doesn’t necessarily translate into SQL NULL; a
prime example of this is a JSON type which may wish to persist the
JSON value 'null'
.
In all cases, the actual NULL SQL value can be always be
persisted in any column by using
the null
SQL construct in an INSERT statement
or associated with an ORM-mapped attribute.
Note
The “evaluates none” flag does not apply to a value
of None
passed to Column.default
or
Column.server_default
; in these cases,
None
still means “no default”.
New in version 1.1.
See also
Forcing NULL on a column with a default - in the ORM documentation
JSON.none_as_null
- PostgreSQL JSON
interaction with this flag.
TypeEngine.should_evaluate_none
- class-level flag
sqlalchemy.types.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.
sqlalchemy.types.TypeEngine.
hashable
= True¶Flag, if False, means values from this type aren’t hashable.
Used by the ORM when uniquing result lists.
sqlalchemy.types.TypeEngine.
literal_processor
(dialect)¶Return a conversion function for processing literal values that are to be rendered directly without using binds.
This function is used when the compiler makes use of the “literal_binds” flag, typically used in DDL generation as well as in certain scenarios where backends don’t accept bound parameters.
Returns a callable which will receive a literal Python value as the sole positional argument and will return a string representation to be rendered in a SQL statement.
Note
This method is only called relative to a dialect specific type
object, which is often private to a dialect in use and is not
the same type object as the public facing one, which means it’s not
feasible to subclass a TypeEngine
class in order to
provide an alternate TypeEngine.literal_processor()
method, unless subclassing the UserDefinedType
class explicitly.
To provide alternate behavior for
TypeEngine.literal_processor()
, implement a
TypeDecorator
class and provide an implementation
of TypeDecorator.process_literal_param()
.
See also
sqlalchemy.types.TypeEngine.
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.
sqlalchemy.types.TypeEngine.
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
.
Note
This method is only called relative to a dialect specific type
object, which is often private to a dialect in use and is not
the same type object as the public facing one, which means it’s not
feasible to subclass a TypeEngine
class in order to
provide an alternate TypeEngine.result_processor()
method, unless subclassing the UserDefinedType
class explicitly.
To provide alternate behavior for
TypeEngine.result_processor()
, implement a
TypeDecorator
class and provide an implementation
of TypeDecorator.process_result_value()
.
See also
sqlalchemy.types.TypeEngine.
should_evaluate_none
= False¶If True, the Python constant None
is considered to be handled
explicitly by this type.
The ORM uses this flag to indicate that a positive value of None
is passed to the column in an INSERT statement, rather than omitting
the column from the INSERT statement which has the effect of firing
off column-level defaults. It also allows types which have special
behavior for Python None, such as a JSON type, to indicate that
they’d like to handle the None value explicitly.
To set this flag on an existing type, use the
TypeEngine.evaluates_none()
method.
See also
New in version 1.1.
sqlalchemy.types.TypeEngine.
sort_key_function
= None¶A sorting function that can be passed as the key to sorted.
The default value of None
indicates that the values stored by
this type are self-sorting.
New in version 1.3.8.
sqlalchemy.types.TypeEngine.
with_variant
(type_, dialect_name)¶Produce a new type object that will utilize the given type when applied to the dialect of the given name.
e.g.:
from sqlalchemy.types import String
from sqlalchemy.dialects import mysql
s = String()
s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')
The construction of TypeEngine.with_variant()
is always
from the “fallback” type to that which is dialect specific.
The returned type is an instance of Variant
, which
itself provides a Variant.with_variant()
that can be called repeatedly.
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.)
sqlalchemy.types.
Concatenable
¶A mixin that marks a type as supporting ‘concatenation’, typically strings.
Comparator
(expr)¶Class signature
class sqlalchemy.types.Concatenable.Comparator
(sqlalchemy.types.Comparator
)
sqlalchemy.types.Concatenable.
comparator_factory
¶alias of sqlalchemy.sql.sqltypes.Concatenable.Comparator
sqlalchemy.types.
Indexable
¶A mixin that marks a type as supporting indexing operations, such as array or JSON structures.
New in version 1.1.0.
Comparator
(expr)¶Class signature
class sqlalchemy.types.Indexable.Comparator
(sqlalchemy.types.Comparator
)
sqlalchemy.types.Indexable.
comparator_factory
¶alias of sqlalchemy.sql.sqltypes.Indexable.Comparator
sqlalchemy.types.
NullType
¶An unknown type.
NullType
is used as a default type for those cases where
a type cannot be determined, including:
During table reflection, when the type of a column is not recognized
by the Dialect
When constructing SQL expressions using plain Python objects of
unknown types (e.g. somecolumn == my_special_object
)
When a new Column
is created,
and the given type is passed
as None
or is not passed at all.
The NullType
can be used within SQL expression invocation
without issue, it just has no behavior either at the expression
construction level or at the bind-parameter/result processing level.
NullType
will result in a CompileError
if the compiler
is asked to render the type itself, such as if it is used in a
cast()
operation or within a schema creation operation such as that
invoked by MetaData.create_all()
or the
CreateTable
construct.
Class signature
class sqlalchemy.types.NullType
(sqlalchemy.types.TypeEngine
)
sqlalchemy.types.
ExternalType
¶mixin that defines attributes and behaviors specific to third-party datatypes.
“Third party” refers to datatypes that are defined outside the scope of SQLAlchemy within either end-user application code or within external extensions to SQLAlchemy.
Subclasses currently include TypeDecorator
and
UserDefinedType
.
New in version 1.4.28.
sqlalchemy.types.ExternalType.
cache_ok
= None¶Indicate if statements using this ExternalType
are “safe to
cache”.
The default value None
will emit a warning and then not allow caching
of a statement which includes this type. Set to False
to disable
statements using this type from being cached at all without a warning.
When set to True
, the object’s class and selected elements from its
state will be used as part of the cache key. For example, using a
TypeDecorator
:
class MyType(TypeDecorator):
impl = String
cache_ok = True
def __init__(self, choices):
self.choices = tuple(choices)
self.internal_only = True
The cache key for the above type would be equivalent to:
>>> MyType(["a", "b", "c"])._static_cache_key
(<class '__main__.MyType'>, ('choices', ('a', 'b', 'c')))
The caching scheme will extract attributes from the type that correspond
to the names of parameters in the __init__()
method. Above, the
“choices” attribute becomes part of the cache key but “internal_only”
does not, because there is no parameter named “internal_only”.
The requirements for cacheable elements is that they are hashable and also that they indicate the same SQL rendered for expressions using this type every time for a given cache value.
To accommodate for datatypes that refer to unhashable structures such as dictionaries, sets and lists, these objects can be made “cacheable” by assigning hashable structures to the attributes whose names correspond with the names of the arguments. For example, a datatype which accepts a dictionary of lookup values may publish this as a sorted series of tuples. Given a previously un-cacheable type as:
class LookupType(UserDefinedType):
'''a custom type that accepts a dictionary as a parameter.
this is the non-cacheable version, as "self.lookup" is not
hashable.
'''
def __init__(self, lookup):
self.lookup = lookup
def get_col_spec(self, **kw):
return "VARCHAR(255)"
def bind_processor(self, dialect):
# ... works with "self.lookup" ...
Where “lookup” is a dictionary. The type will not be able to generate a cache key:
>>> type_ = LookupType({"a": 10, "b": 20})
>>> type_._static_cache_key
<stdin>:1: SAWarning: UserDefinedType LookupType({'a': 10, 'b': 20}) will not
produce a cache key because the ``cache_ok`` flag is not set to True.
Set this flag to True if this type object's state is safe to use
in a cache key, or False to disable this warning.
symbol('no_cache')
If we did set up such a cache key, it wouldn’t be usable. We would get a tuple structure that contains a dictionary inside of it, which cannot itself be used as a key in a “cache dictionary” such as SQLAlchemy’s statement cache, since Python dictionaries aren’t hashable:
>>> # set cache_ok = True
>>> type_.cache_ok = True
>>> # this is the cache key it would generate
>>> key = type_._static_cache_key
>>> key
(<class '__main__.LookupType'>, ('lookup', {'a': 10, 'b': 20}))
>>> # however this key is not hashable, will fail when used with
>>> # SQLAlchemy statement cache
>>> some_cache = {key: "some sql value"}
Traceback (most recent call last): File "<stdin>", line 1,
in <module> TypeError: unhashable type: 'dict'
The type may be made cacheable by assigning a sorted tuple of tuples to the “.lookup” attribute:
class LookupType(UserDefinedType):
'''a custom type that accepts a dictionary as a parameter.
The dictionary is stored both as itself in a private variable,
and published in a public variable as a sorted tuple of tuples,
which is hashable and will also return the same value for any
two equivalent dictionaries. Note it assumes the keys and
values of the dictionary are themselves hashable.
'''
cache_ok = True
def __init__(self, lookup):
self._lookup = lookup
# assume keys/values of "lookup" are hashable; otherwise
# they would also need to be converted in some way here
self.lookup = tuple(
(key, lookup[key]) for key in sorted(lookup)
)
def get_col_spec(self, **kw):
return "VARCHAR(255)"
def bind_processor(self, dialect):
# ... works with "self._lookup" ...
Where above, the cache key for LookupType({"a": 10, "b": 20})
will be:
>>> LookupType({"a": 10, "b": 20})._static_cache_key
(<class '__main__.LookupType'>, ('lookup', (('a', 10), ('b', 20))))
New in version 1.4.14: - added the cache_ok
flag to allow
some configurability of caching for TypeDecorator
classes.
New in version 1.4.28: - added the ExternalType
mixin which
generalizes the cache_ok
flag to both the TypeDecorator
and UserDefinedType
classes.
See also
sqlalchemy.types.
Variant
(base, mapping)¶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.
See also
TypeEngine.with_variant()
for an example of use.
Class signature
class sqlalchemy.types.Variant
(sqlalchemy.types.TypeDecorator
)
sqlalchemy.types.Variant.
__init__
(base, mapping)¶Construct a new Variant
.
base¶ – the base ‘fallback’ type
mapping¶ – dictionary of string dialect names to
TypeEngine
instances.
sqlalchemy.types.Variant.
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
.
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.)
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 3.5.4.