A variety of methods exist to redefine the behavior of existing types as well as to provide new ones.
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.
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.
Note
The bind- and result-processing of TypeDecorator
is in addition to the processing already performed by the hosted
type, which is customized by SQLAlchemy on a per-DBAPI basis to perform
processing specific to that DBAPI. While it is possible to replace this
handling for a given type through direct subclassing, it is never needed in
practice and SQLAlchemy no longer supports this as a public use case.
sqlalchemy.types.
TypeDecorator
(*args, **kwargs)¶Bases: sqlalchemy.sql.expression.SchemaEventTarget
, 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, **kw):
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
Warning
Note that the behavior of coerce_compared_value is not inherited
by default from that of the base type.
If the TypeDecorator
is augmenting a
type that requires special logic for certain types of operators,
this method must be overridden. A key example is when decorating
the postgresql.JSON
and postgresql.JSONB
types;
the default rules of TypeEngine.coerce_compared_value()
should
be used in order to deal with operators like index operations:
class MyJsonType(TypeDecorator):
impl = postgresql.JSON
def coerce_compared_value(self, op, value):
return self.impl.coerce_compared_value(op, value)
Without the above step, index operations such as mycol['foo']
will cause the index value 'foo'
to be JSON encoded.
Comparator
(expr)¶Bases: sqlalchemy.types.Comparator
__eq__
(other)¶inherited from the __eq__()
method of ColumnOperators
Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
__le__
(other)¶inherited from the __le__()
method of ColumnOperators
Implement the <=
operator.
In a column context, produces the clause a <= b
.
__lt__
(other)¶inherited from the __lt__()
method of ColumnOperators
Implement the <
operator.
In a column context, produces the clause a < b
.
__ne__
(other)¶inherited from the __ne__()
method of ColumnOperators
Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
all_
()¶inherited from the all_()
method of ColumnOperators
Produce a all_()
clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:
# postgresql '5 = ALL (somearray)'
expr = 5 == mytable.c.somearray.all_()
# mysql '5 = ALL (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().all_()
New in version 1.1.
any_
()¶inherited from the any_()
method of ColumnOperators
Produce a any_()
clause against the
parent object.
This operator is only appropriate against a scalar subquery object, or for some backends an column expression that is against the ARRAY type, e.g.:
# postgresql '5 = ANY (somearray)'
expr = 5 == mytable.c.somearray.any_()
# mysql '5 = ANY (SELECT value FROM table)'
expr = 5 == select([table.c.value]).as_scalar().any_()
New in version 1.1.
asc
()¶inherited from the asc()
method of ColumnOperators
Produce a asc()
clause against the
parent object.
between
(cleft, cright, symmetric=False)¶inherited from the between()
method of ColumnOperators
Produce a between()
clause against
the parent object, given the lower and upper range.
bool_op
(opstring, precedence=0)¶Return a custom boolean operator.
This method is shorthand for calling
Operators.op()
and passing the
Operators.op.is_comparison
flag with True.
New in version 1.2.0b3.
See also
collate
(collation)¶inherited from the collate()
method of ColumnOperators
Produce a collate()
clause against
the parent object, given the collation string.
See also
concat
(other)¶inherited from the concat()
method of ColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
contains
(other, **kwargs)¶inherited from the contains()
method of ColumnOperators
Implement the ‘contains’ operator.
Produces a LIKE expression that tests against a match for the middle of a string value:
column LIKE '%' || <other> || '%'
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.contains("foobar"))
Since the operator uses LIKE
, wildcard characters
"%"
and "_"
that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.contains.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.contains.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.contains.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.contains("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '/'
With the value of :param as "foo/%bar"
.
New in version 1.2.
Changed in version 1.2.0: The
ColumnOperators.contains.autoescape
parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
ColumnOperators.contains.escape
parameter.
escape¶ –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.contains("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.contains.autoescape
:
somecolumn.contains("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
desc
()¶inherited from the desc()
method of ColumnOperators
Produce a desc()
clause against the
parent object.
distinct
()¶inherited from the distinct()
method of ColumnOperators
Produce a distinct()
clause against the
parent object.
endswith
(other, **kwargs)¶inherited from the endswith()
method of ColumnOperators
Implement the ‘endswith’ operator.
Produces a LIKE expression that tests against a match for the end of a string value:
column LIKE '%' || <other>
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.endswith("foobar"))
Since the operator uses LIKE
, wildcard characters
"%"
and "_"
that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.endswith.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.endswith.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.endswith.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.endswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE '%' || :param ESCAPE '/'
With the value of :param as "foo/%bar"
.
New in version 1.2.
Changed in version 1.2.0: The
ColumnOperators.endswith.autoescape
parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
ColumnOperators.endswith.escape
parameter.
escape¶ –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.endswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE '%' || :param ESCAPE '^'
The parameter may also be combined with
ColumnOperators.endswith.autoescape
:
somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
ilike
(other, escape=None)¶inherited from the ilike()
method of ColumnOperators
Implement the ilike
operator, e.g. case insensitive LIKE.
In a column context, produces an expression either of the form:
lower(a) LIKE lower(other)
Or on backends that support the ILIKE operator:
a ILIKE other
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.ilike("%foobar%"))
See also
in_
(other)¶inherited from the in_()
method of ColumnOperators
Implement the in
operator.
In a column context, produces the clause column IN <other>
.
The given parameter other
may be:
A list of literal values, e.g.:
stmt.where(column.in_([1, 2, 3]))
In this calling form, the list of items is converted to a set of bound parameters the same length as the list given:
WHERE COL IN (?, ?, ?)
An empty list, e.g.:
stmt.where(column.in_([]))
In this calling form, the expression renders a “false” expression, e.g.:
WHERE 1 != 1
This “false” expression has historically had different behaviors
in older SQLAlchemy versions, see
create_engine.empty_in_strategy
for behavioral options.
Changed in version 1.2: simplified the behavior of “empty in” expressions
A bound parameter, e.g. bindparam()
, may be used if it
includes the bindparam.expanding
flag:
stmt.where(column.in_(bindparam('value', expanding=True)))
In this calling form, the expression renders a special non-SQL placeholder expression that looks like:
WHERE COL IN ([EXPANDING_value])
This placeholder expression is intercepted at statement execution time to be converted into the variable number of bound parameter form illustrated earlier. If the statement were executed as:
connection.execute(stmt, {"value": [1, 2, 3]})
The database would be passed a bound parameter for each value:
WHERE COL IN (?, ?, ?)
New in version 1.2: added “expanding” bound parameters
If an empty list is passed, a special “empty list” expression, which is specific to the database in use, is rendered. On SQLite this would be:
WHERE COL IN (SELECT 1 FROM (SELECT 1) WHERE 1!=1)
New in version 1.3: “expanding” bound parameters now support empty lists
a select()
construct, which is usually a correlated
scalar select:
stmt.where(
column.in_(
select([othertable.c.y]).
where(table.c.x == othertable.c.x)
)
)
In this calling form, ColumnOperators.in_()
renders as given:
WHERE COL IN (SELECT othertable.y
FROM othertable WHERE othertable.x = table.x)
other¶ – a list of literals, a select()
construct,
or a bindparam()
construct that includes the
bindparam.expanding
flag set to True.
is_
(other)¶inherited from the is_()
method of ColumnOperators
Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
See also
is_distinct_from
(other)¶inherited from the is_distinct_from()
method of ColumnOperators
Implement the IS DISTINCT FROM
operator.
Renders “a IS DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS NOT b”.
New in version 1.1.
isnot
(other)¶inherited from the isnot()
method of ColumnOperators
Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
See also
isnot_distinct_from
(other)¶inherited from the isnot_distinct_from()
method of ColumnOperators
Implement the IS NOT DISTINCT FROM
operator.
Renders “a IS NOT DISTINCT FROM b” on most platforms; on some such as SQLite may render “a IS b”.
New in version 1.1.
like
(other, escape=None)¶inherited from the like()
method of ColumnOperators
Implement the like
operator.
In a column context, produces the expression:
a LIKE other
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.like("%foobar%"))
See also
match
(other, **kwargs)¶inherited from the match()
method of ColumnOperators
Implements a database-specific ‘match’ operator.
match()
attempts to resolve to
a MATCH-like function or operator provided by the backend.
Examples include:
PostgreSQL - renders x @@ to_tsquery(y)
MySQL - renders MATCH (x) AGAINST (y IN BOOLEAN MODE)
Oracle - renders CONTAINS(x, y)
other backends may provide special implementations.
Backends without any special implementation will emit the operator as “MATCH”. This is compatible with SQLite, for example.
notilike
(other, escape=None)¶inherited from the notilike()
method of ColumnOperators
implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
See also
notin_
(other)¶inherited from the notin_()
method of ColumnOperators
implement the NOT IN
operator.
This is equivalent to using negation with
ColumnOperators.in_()
, i.e. ~x.in_(y)
.
In the case that other
is an empty sequence, the compiler
produces an “empty not in” expression. This defaults to the
expression “1 = 1” to produce true in all cases. The
create_engine.empty_in_strategy
may be used to
alter this behavior.
Changed in version 1.2: The ColumnOperators.in_()
and
ColumnOperators.notin_()
operators
now produce a “static” expression for an empty IN sequence
by default.
See also
notlike
(other, escape=None)¶inherited from the notlike()
method of ColumnOperators
implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
See also
nullsfirst
()¶inherited from the nullsfirst()
method of ColumnOperators
Produce a nullsfirst()
clause against the
parent object.
nullslast
()¶inherited from the nullslast()
method of ColumnOperators
Produce a nullslast()
clause against the
parent object.
op
(opstring, precedence=0, is_comparison=False, return_type=None)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
operator¶ – a string which will be output as the infix operator between this element and the expression passed to the generated function.
precedence¶ – precedence to apply to the operator, when
parenthesizing expressions. A lower number will cause the expression
to be parenthesized when applied against another operator with
higher precedence. The default value of 0
is lower than all
operators except for the comma (,
) and AS
operators.
A value of 100 will be higher or equal to all operators, and -100
will be lower than or equal to all operators.
is_comparison¶ –
if True, the operator will be considered as a
“comparison” operator, that is which evaluates to a boolean
true/false value, like ==
, >
, etc. This flag should be set
so that ORM relationships can establish that the operator is a
comparison operator when used in a custom join condition.
New in version 0.9.2: - added the
Operators.op.is_comparison
flag.
return_type¶ –
a TypeEngine
class or object that will
force the return type of an expression produced by this operator
to be of that type. By default, operators that specify
Operators.op.is_comparison
will resolve to
Boolean
, and those that do not will be of the same
type as the left-hand operand.
New in version 1.2.0b3: - added the
Operators.op.return_type
argument.
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))
reverse_operate
(op, other, **kwargs)¶Reverse operate on an argument.
Usage is the same as operate()
.
startswith
(other, **kwargs)¶inherited from the startswith()
method of ColumnOperators
Implement the startswith
operator.
Produces a LIKE expression that tests against a match for the start of a string value:
column LIKE <other> || '%'
E.g.:
stmt = select([sometable]).\
where(sometable.c.column.startswith("foobar"))
Since the operator uses LIKE
, wildcard characters
"%"
and "_"
that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the ColumnOperators.startswith.autoescape
flag
may be set to True
to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
ColumnOperators.startswith.escape
parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.
other¶ – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters %
and _
are not escaped by default unless
the ColumnOperators.startswith.autoescape
flag is
set to True.
autoescape¶ –
boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
"%"
, "_"
and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.
An expression such as:
somecolumn.startswith("foo%bar", autoescape=True)
Will render as:
somecolumn LIKE :param || '%' ESCAPE '/'
With the value of :param as "foo/%bar"
.
New in version 1.2.
Changed in version 1.2.0: The
ColumnOperators.startswith.autoescape
parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
ColumnOperators.startswith.escape
parameter.
escape¶ –
a character which when given will render with the
ESCAPE
keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of %
and _
to allow them to act as themselves and not
wildcard characters.
An expression such as:
somecolumn.startswith("foo/%bar", escape="^")
Will render as:
somecolumn LIKE :param || '%' ESCAPE '^'
The parameter may also be combined with
ColumnOperators.startswith.autoescape
:
somecolumn.startswith("foo%bar^bat", escape="^", autoescape=True)
Where above, the given literal parameter will be converted to
"foo^%bar^^bat"
before being passed to the database.
__eq__
¶inherited from the __eq__
attribute of object
Return self==value.
__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.
__le__
¶inherited from the __le__
attribute of object
Return self<=value.
__lt__
¶inherited from the __lt__
attribute of object
Return self<value.
__ne__
¶inherited from the __ne__
attribute of object
Return self!=value.
adapt
(cls, **kw)¶inherited from the 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_expression
(bindparam)¶“Given a bind value (i.e. a BindParameter
instance),
return a SQL expression in its place.
This is typically a SQL function that wraps the existing bound
parameter within the statement. It is used for special data types
that require literals being wrapped in some special database function
in order to coerce an application-level value into a database-specific
format. It is the SQL analogue of the
TypeEngine.bind_processor()
method.
The method is evaluated at statement compile time, as opposed to statement construction time.
Note that this method, when implemented, should always return the exact same structure, without any conditional logic, as it may be used in an executemany() call against an arbitrary number of bound parameter sets.
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.
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.
coerce_to_is_types
= (<class 'NoneType'>,)¶Specify those Python types which should be coerced at the expression
level to “IS <constant>” when compared using ==
(and same for
IS NOT
in conjunction with !=
.
For most SQLAlchemy types, this includes NoneType
, as well as
bool
.
TypeDecorator
modifies this list to only include NoneType
,
as typedecorator implementations that deal with boolean types are common.
Custom TypeDecorator
classes can override this attribute to
return an empty tuple, in which case no values will be coerced to
constants.
column_expression
(column)¶Given a SELECT column expression, return a wrapping SQL expression.
This is typically a SQL function that wraps a column expression
as rendered in the columns clause of a SELECT statement.
It is used for special data types that require
columns to be wrapped in some special database function in order
to coerce the value before being sent back to the application.
It is the SQL analogue of the TypeEngine.result_processor()
method.
The method is evaluated at statement compile time, as opposed to statement construction time.
comparator_factory
¶Base class for custom comparison operations defined at the
type level. See TypeEngine.comparator_factory
.
compare_against_backend
(dialect, conn_type)¶inherited from the compare_against_backend()
method of TypeEngine
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.
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 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.
copy
(**kw)¶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.
dialect_impl
(dialect)¶inherited from the dialect_impl()
method of TypeEngine
Return a dialect-specific implementation for this
TypeEngine
.
evaluates_none
()¶inherited from the evaluates_none()
method of TypeEngine
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
postgresql.JSON.none_as_null
- PostgreSQL JSON
interaction with this flag.
TypeEngine.should_evaluate_none
- class-level flag
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”.
literal_processor
(dialect)¶Provide a literal processing function for the given
Dialect
.
Subclasses here will typically override
TypeDecorator.process_literal_param()
instead of this method
directly.
By default, this method makes use of
TypeDecorator.process_bind_param()
if that method is
implemented, where TypeDecorator.process_literal_param()
is
not. The rationale here is that TypeDecorator
typically
deals with Python conversions of data that are above the layer of
database presentation. With the value converted by
TypeDecorator.process_bind_param()
, the underlying type will
then handle whether it needs to be presented to the DBAPI as a bound
parameter or to the database as an inline SQL value.
New in version 0.9.0.
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.
process_literal_param
(value, dialect)¶Receive a literal parameter value to be rendered inline within a statement.
This method is used when the compiler renders a literal value without using binds, typically within DDL such as in the “server default” of a column or an expression within a CHECK constraint.
The returned string will be rendered into the output string.
New in version 0.9.0.
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.
This operation should be designed to be reversible by the “process_bind_param” method of this class.
python_type
¶inherited from the 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.
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 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 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.)
A few key TypeDecorator
recipes follow.
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
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.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
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).int
else:
# hexstring
return "%.32x" % value.int
def process_result_value(self, value, dialect):
if value is None:
return value
else:
if not isinstance(value, uuid.UUID):
value = uuid.UUID(value)
return value
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
The ORM by default will not detect “mutability” on such a type as above - 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:
obj.json_value["key"] = "value" # will *not* be detected by the ORM
obj.json_value = {"key": "value"} # *will* be detected by the ORM
The above limitation may be
fine, as many applications may not require that the values are ever mutated
once created. For those which do have this requirement, support for mutability
is best applied using the sqlalchemy.ext.mutable
extension. For a
dictionary-oriented JSON structure, we can apply this as:
json_type = MutableDict.as_mutable(JSONEncodedDict)
class MyClass(Base):
# ...
json_data = Column(json_type)
See also
The default behavior of TypeDecorator
is to coerce the “right hand side”
of any expression into the same type. For a type like JSON, this means that
any operator used must make sense in terms of JSON. For some cases,
users may wish for the type to behave like JSON in some circumstances, and
as plain text in others. One example is if one wanted to handle the
LIKE operator for the JSON type. LIKE makes no sense against a JSON structure,
but it does make sense against the underlying textual representation. To
get at this with a type like JSONEncodedDict
, we need to
coerce the column to a textual form using cast()
or
type_coerce()
before attempting to use this operator:
from sqlalchemy import type_coerce, String
stmt = select([my_table]).where(
type_coerce(my_table.c.json_data, String).like('%foo%'))
TypeDecorator
provides a built-in system for working up type
translations like these based on operators. If we wanted to frequently use the
LIKE operator with our JSON object interpreted as a string, we can build it
into the type by overriding the TypeDecorator.coerce_compared_value()
method:
from sqlalchemy.sql import operators
from sqlalchemy import String
class JSONEncodedDict(TypeDecorator):
impl = VARCHAR
def coerce_compared_value(self, op, value):
if op in (operators.like_op, operators.notlike_op):
return String()
else:
return self
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
Above is just one approach to handling an operator like “LIKE”. Other
applications may wish to raise NotImplementedError
for operators that
have no meaning with a JSON object such as “LIKE”, rather than automatically
coercing to text.
As seen in the section Augmenting Existing Types, SQLAlchemy allows Python functions to be invoked both when parameters are sent to a statement, as well as when result rows are loaded from the database, to apply transformations to the values as they are sent to or from the database. It is also possible to define SQL-level transformations as well. The rationale here is when only the relational database contains a particular series of functions that are necessary to coerce incoming and outgoing data between an application and persistence format. Examples include using database-defined encryption/decryption functions, as well as stored procedures that handle geographic data. The PostGIS extension to PostgreSQL includes an extensive array of SQL functions that are necessary for coercing data into particular formats.
Any TypeEngine
, UserDefinedType
or TypeDecorator
subclass
can include implementations of
TypeEngine.bind_expression()
and/or TypeEngine.column_expression()
, which
when defined to return a non-None
value should return a ColumnElement
expression to be injected into the SQL statement, either surrounding
bound parameters or a column expression. For example, to build a Geometry
type which will apply the PostGIS function ST_GeomFromText
to all outgoing
values and the function ST_AsText
to all incoming data, we can create
our own subclass of UserDefinedType
which provides these methods
in conjunction with func
:
from sqlalchemy import func
from sqlalchemy.types import UserDefinedType
class Geometry(UserDefinedType):
def get_col_spec(self):
return "GEOMETRY"
def bind_expression(self, bindvalue):
return func.ST_GeomFromText(bindvalue, type_=self)
def column_expression(self, col):
return func.ST_AsText(col, type_=self)
We can apply the Geometry
type into Table
metadata
and use it in a select()
construct:
geometry = Table('geometry', metadata,
Column('geom_id', Integer, primary_key=True),
Column('geom_data', Geometry)
)
print(select([geometry]).where(
geometry.c.geom_data == 'LINESTRING(189412 252431,189631 259122)'))
The resulting SQL embeds both functions as appropriate. ST_AsText
is applied to the columns clause so that the return value is run through
the function before passing into a result set, and ST_GeomFromText
is run on the bound parameter so that the passed-in value is converted:
SELECT geometry.geom_id, ST_AsText(geometry.geom_data) AS geom_data_1
FROM geometry
WHERE geometry.geom_data = ST_GeomFromText(:geom_data_2)
The TypeEngine.column_expression()
method interacts with the
mechanics of the compiler such that the SQL expression does not interfere
with the labeling of the wrapped expression. Such as, if we rendered
a select()
against a label()
of our expression, the string
label is moved to the outside of the wrapped expression:
print(select([geometry.c.geom_data.label('my_data')]))
Output:
SELECT ST_AsText(geometry.geom_data) AS my_data
FROM geometry
Another example is we decorate
postgresql.BYTEA
to provide a PGPString
, which will make use of the
PostgreSQL pgcrypto
extension to encrypt/decrypt values
transparently:
from sqlalchemy import create_engine, String, select, func, \
MetaData, Table, Column, type_coerce, TypeDecorator
from sqlalchemy.dialects.postgresql import BYTEA
class PGPString(TypeDecorator):
impl = BYTEA
def __init__(self, passphrase):
super(PGPString, self).__init__()
self.passphrase = passphrase
def bind_expression(self, bindvalue):
# convert the bind's type from PGPString to
# String, so that it's passed to psycopg2 as is without
# a dbapi.Binary wrapper
bindvalue = type_coerce(bindvalue, String)
return func.pgp_sym_encrypt(bindvalue, self.passphrase)
def column_expression(self, col):
return func.pgp_sym_decrypt(col, self.passphrase)
metadata = MetaData()
message = Table('message', metadata,
Column('username', String(50)),
Column('message',
PGPString("this is my passphrase")),
)
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
with engine.begin() as conn:
metadata.create_all(conn)
conn.execute(message.insert(), username="some user",
message="this is my message")
print(conn.scalar(
select([message.c.message]).\
where(message.c.username == "some user")
))
The pgp_sym_encrypt
and pgp_sym_decrypt
functions are applied
to the INSERT and SELECT statements:
INSERT INTO message (username, message)
VALUES (%(username)s, pgp_sym_encrypt(%(message)s, %(pgp_sym_encrypt_1)s))
{'username': 'some user', 'message': 'this is my message',
'pgp_sym_encrypt_1': 'this is my passphrase'}
SELECT pgp_sym_decrypt(message.message, %(pgp_sym_decrypt_1)s) AS message_1
FROM message
WHERE message.username = %(username_1)s
{'pgp_sym_decrypt_1': 'this is my passphrase', 'username_1': 'some user'}
See also
SQLAlchemy Core defines a fixed set of expression operators available to all column expressions.
Some of these operations have the effect of overloading Python’s built in operators;
examples of such operators include
ColumnOperators.__eq__()
(table.c.somecolumn == 'foo'
),
ColumnOperators.__invert__()
(~table.c.flag
),
and ColumnOperators.__add__()
(table.c.x + table.c.y
). Other operators are exposed as
explicit methods on column expressions, such as
ColumnOperators.in_()
(table.c.value.in_(['x', 'y'])
) and ColumnOperators.like()
(table.c.value.like('%ed%')
).
The Core expression constructs in all cases consult the type of the expression in order to determine
the behavior of existing operators, as well as to locate additional operators that aren’t part of
the built in set. The TypeEngine
base class defines a root “comparison” implementation
TypeEngine.Comparator
, and many specific types provide their own sub-implementations of this
class. User-defined TypeEngine.Comparator
implementations can be built directly into a
simple subclass of a particular type in order to override or define new operations. Below,
we create a Integer
subclass which overrides the ColumnOperators.__add__()
operator:
from sqlalchemy import Integer
class MyInt(Integer):
class comparator_factory(Integer.Comparator):
def __add__(self, other):
return self.op("goofy")(other)
The above configuration creates a new class MyInt
, which
establishes the TypeEngine.comparator_factory
attribute as
referring to a new class, subclassing the TypeEngine.Comparator
class
associated with the Integer
type.
Usage:
>>> sometable = Table("sometable", metadata, Column("data", MyInt))
>>> print(sometable.c.data + 5)
sometable.data goofy :data_1
The implementation for ColumnOperators.__add__()
is consulted
by an owning SQL expression, by instantiating the TypeEngine.Comparator
with
itself as the expr
attribute. The mechanics of the expression
system are such that operations continue recursively until an
expression object produces a new SQL expression construct. Above, we
could just as well have said self.expr.op("goofy")(other)
instead
of self.op("goofy")(other)
.
When using Operators.op()
for comparison operations that return a
boolean result, the Operators.op.is_comparison
flag should be
set to True
:
class MyInt(Integer):
class comparator_factory(Integer.Comparator):
def is_frobnozzled(self, other):
return self.op("--is_frobnozzled->", is_comparison=True)(other)
New methods added to a TypeEngine.Comparator
are exposed on an
owning SQL expression
using a __getattr__
scheme, which exposes methods added to
TypeEngine.Comparator
onto the owning ColumnElement
.
For example, to add a log()
function
to integers:
from sqlalchemy import Integer, func
class MyInt(Integer):
class comparator_factory(Integer.Comparator):
def log(self, other):
return func.log(self.expr, other)
Using the above type:
>>> print(sometable.c.data.log(5))
log(:log_1, :log_2)
Unary operations
are also possible. For example, to add an implementation of the
PostgreSQL factorial operator, we combine the UnaryExpression
construct
along with a custom_op
to produce the factorial expression:
from sqlalchemy import Integer
from sqlalchemy.sql.expression import UnaryExpression
from sqlalchemy.sql import operators
class MyInteger(Integer):
class comparator_factory(Integer.Comparator):
def factorial(self):
return UnaryExpression(self.expr,
modifier=operators.custom_op("!"),
type_=MyInteger)
Using the above type:
>>> from sqlalchemy.sql import column
>>> print(column('x', MyInteger).factorial())
x !
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.
sqlalchemy.types.
UserDefinedType
¶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, **kw):
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))
)
The get_col_spec()
method will in most cases receive a keyword
argument type_expression
which refers to the owning expression
of the type as being compiled, such as a Column
or
cast()
construct. This keyword is only sent if the method
accepts keyword arguments (e.g. **kw
) in its argument signature;
introspection is used to check for this in order to support legacy
forms of this function.
New in version 1.0.0: the owning expression is passed to
the get_col_spec()
method via the keyword argument
type_expression
, if it receives **kw
in its signature.
Comparator
(expr)¶Bases: sqlalchemy.types.Comparator
coerce_compared_value
(op, value)¶Suggest a type for a ‘coerced’ Python value in an expression.
Default behavior for UserDefinedType
is the
same as that of TypeDecorator
; by default it returns
self
, assuming the compared value should be coerced into
the same type as this one. See
TypeDecorator.coerce_compared_value()
for more detail.
comparator_factory
¶alias of UserDefinedType.Comparator