437
446
support for mutability is best applied using the ``sqlalchemy.ext.mutable``
438
447
extension - see the example in :ref:`mutable_toplevel`.
449
.. _replacing_processors:
451
Replacing the Bind/Result Processing of Existing Types
452
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
454
Most augmentation of type behavior at the bind/result level
455
is achieved using :class:`.TypeDecorator`. For the rare scenario
456
where the specific processing applied by SQLAlchemy at the DBAPI
457
level needs to be replaced, the SQLAlchemy type can be subclassed
458
directly, and the ``bind_processor()`` or ``result_processor()``
459
methods can be overridden. Doing so requires that the
460
``adapt()`` method also be overridden. This method is the mechanism
461
by which SQLAlchemy produces DBAPI-specific type behavior during
462
statement execution. Overriding it allows a copy of the custom
463
type to be used in lieu of a DBAPI-specific type. Below we subclass
464
the :class:`.types.TIME` type to have custom result processing behavior.
465
The ``process()`` function will receive ``value`` from the DBAPI
468
class MySpecialTime(TIME):
469
def __init__(self, special_argument):
470
super(MySpecialTime, self).__init__()
471
self.special_argument = special_argument
473
def result_processor(self, dialect, coltype):
477
if value is not None:
478
microseconds = value.microseconds
479
seconds = value.seconds
480
minutes = seconds / 60
484
seconds - minutes * 60,
490
def adapt(self, impltype):
491
return MySpecialTime(self.special_argument)
493
.. _types_sql_value_processing:
495
Applying SQL-level Bind/Result Processing
496
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
498
As seen in the sections :ref:`types_typedecorator` and :ref:`replacing_processors`,
499
SQLAlchemy allows Python functions to be invoked both when parameters are sent
500
to a statement, as well as when result rows are loaded from the database, to apply
501
transformations to the values as they are sent to or from the database. It is also
502
possible to define SQL-level transformations as well. The rationale here is when
503
only the relational database contains a particular series of functions that are necessary
504
to coerce incoming and outgoing data between an application and persistence format.
505
Examples include using database-defined encryption/decryption functions, as well
506
as stored procedures that handle geographic data. The Postgis extension to Postgresql
507
includes an extensive array of SQL functions that are necessary for coercing
508
data into particular formats.
510
Any :class:`.TypeEngine`, :class:`.UserDefinedType` or :class:`.TypeDecorator` subclass
511
can include implementations of
512
:meth:`.TypeEngine.bind_expression` and/or :meth:`.TypeEngine.column_expression`, which
513
when defined to return a non-``None`` value should return a :class:`.ColumnElement`
514
expression to be injected into the SQL statement, either surrounding
515
bound parameters or a column expression. For example, to build a ``Geometry``
516
type which will apply the Postgis function ``ST_GeomFromText`` to all outgoing
517
values and the function ``ST_AsText`` to all incoming data, we can create
518
our own subclass of :class:`.UserDefinedType` which provides these methods
519
in conjunction with :data:`~.sqlalchemy.sql.expression.func`::
521
from sqlalchemy import func
522
from sqlalchemy.types import UserDefinedType
524
class Geometry(UserDefinedType):
525
def get_col_spec(self):
528
def bind_expression(self, bindvalue):
529
return func.ST_GeomFromText(bindvalue, type_=self)
531
def column_expression(self, col):
532
return func.ST_AsText(col, type_=self)
534
We can apply the ``Geometry`` type into :class:`.Table` metadata
535
and use it in a :func:`.select` construct::
537
geometry = Table('geometry', metadata,
538
Column('geom_id', Integer, primary_key=True),
539
Column('geom_data', Geometry)
542
print select([geometry]).where(
543
geometry.c.geom_data == 'LINESTRING(189412 252431,189631 259122)')
545
The resulting SQL embeds both functions as appropriate. ``ST_AsText``
546
is applied to the columns clause so that the return value is run through
547
the function before passing into a result set, and ``ST_GeomFromText``
548
is run on the bound parameter so that the passed-in value is converted::
550
SELECT geometry.geom_id, ST_AsText(geometry.geom_data) AS geom_data_1
552
WHERE geometry.geom_data = ST_GeomFromText(:geom_data_2)
554
The :meth:`.TypeEngine.column_expression` method interacts with the
555
mechanics of the compiler such that the SQL expression does not interfere
556
with the labeling of the wrapped expression. Such as, if we rendered
557
a :func:`.select` against a :func:`.label` of our expression, the string
558
label is moved to the outside of the wrapped expression::
560
print select([geometry.c.geom_data.label('my_data')])
564
SELECT ST_AsText(geometry.geom_data) AS my_data
567
For an example of subclassing a built in type directly, we subclass
568
:class:`.postgresql.BYTEA` to provide a ``PGPString``, which will make use of the
569
Postgresql ``pgcrypto`` extension to encrpyt/decrypt values
572
from sqlalchemy import create_engine, String, select, func, \
573
MetaData, Table, Column, type_coerce
575
from sqlalchemy.dialects.postgresql import BYTEA
577
class PGPString(BYTEA):
578
def __init__(self, passphrase, length=None):
579
super(PGPString, self).__init__(length)
580
self.passphrase = passphrase
582
def bind_expression(self, bindvalue):
583
# convert the bind's type from PGPString to
584
# String, so that it's passed to psycopg2 as is without
585
# a dbapi.Binary wrapper
586
bindvalue = type_coerce(bindvalue, String)
587
return func.pgp_sym_encrypt(bindvalue, self.passphrase)
589
def column_expression(self, col):
590
return func.pgp_sym_decrypt(col, self.passphrase)
592
metadata = MetaData()
593
message = Table('message', metadata,
594
Column('username', String(50)),
596
PGPString("this is my passphrase", length=1000)),
599
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
600
with engine.begin() as conn:
601
metadata.create_all(conn)
603
conn.execute(message.insert(), username="some user",
604
message="this is my message")
607
select([message.c.message]).\
608
where(message.c.username == "some user")
611
The ``pgp_sym_encrypt`` and ``pgp_sym_decrypt`` functions are applied
612
to the INSERT and SELECT statements::
614
INSERT INTO message (username, message)
615
VALUES (%(username)s, pgp_sym_encrypt(%(message)s, %(pgp_sym_encrypt_1)s))
616
{'username': 'some user', 'message': 'this is my message',
617
'pgp_sym_encrypt_1': 'this is my passphrase'}
619
SELECT pgp_sym_decrypt(message.message, %(pgp_sym_decrypt_1)s) AS message_1
621
WHERE message.username = %(username_1)s
622
{'pgp_sym_decrypt_1': 'this is my passphrase', 'username_1': 'some user'}
625
.. versionadded:: 0.8 Added the :meth:`.TypeEngine.bind_expression` and
626
:meth:`.TypeEngine.column_expression` methods.
630
:ref:`examples_postgis`
634
Redefining and Creating New Operators
635
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
637
SQLAlchemy Core defines a fixed set of expression operators available to all column expressions.
638
Some of these operations have the effect of overloading Python's built in operators;
639
examples of such operators include
640
:meth:`.ColumnOperators.__eq__` (``table.c.somecolumn == 'foo'``),
641
:meth:`.ColumnOperators.__invert__` (``~table.c.flag``),
642
and :meth:`.ColumnOperators.__add__` (``table.c.x + table.c.y``). Other operators are exposed as
643
explicit methods on column expressions, such as
644
:meth:`.ColumnOperators.in_` (``table.c.value.in_(['x', 'y'])``) and :meth:`.ColumnOperators.like`
645
(``table.c.value.like('%ed%')``).
647
The Core expression constructs in all cases consult the type of the expression in order to determine
648
the behavior of existing operators, as well as to locate additional operators that aren't part of
649
the built in set. The :class:`.TypeEngine` base class defines a root "comparison" implementation
650
:class:`.TypeEngine.Comparator`, and many specific types provide their own sub-implementations of this
651
class. User-defined :class:`.TypeEngine.Comparator` implementations can be built directly into a
652
simple subclass of a particular type in order to override or define new operations. Below,
653
we create a :class:`.Integer` subclass which overrides the :meth:`.ColumnOperators.__add__` operator::
655
from sqlalchemy import Integer
657
class MyInt(Integer):
658
class comparator_factory(Integer.Comparator):
659
def __add__(self, other):
660
return self.op("goofy")(other)
662
The above configuration creates a new class ``MyInt``, which
663
establishes the :attr:`.TypeEngine.comparator_factory` attribute as
664
referring to a new class, subclassing the :class:`.TypeEngine.Comparator` class
665
associated with the :class:`.Integer` type.
669
>>> sometable = Table("sometable", metadata, Column("data", MyInt))
670
>>> print sometable.c.data + 5
671
sometable.data goofy :data_1
673
The implementation for :meth:`.ColumnOperators.__add__` is consulted
674
by an owning SQL expression, by instantiating the :class:`.TypeEngine.Comparator` with
675
itself as the ``expr`` attribute. The mechanics of the expression
676
system are such that operations continue recursively until an
677
expression object produces a new SQL expression construct. Above, we
678
could just as well have said ``self.expr.op("goofy")(other)`` instead
679
of ``self.op("goofy")(other)``.
681
New methods added to a :class:`.TypeEngine.Comparator` are exposed on an
682
owning SQL expression
683
using a ``__getattr__`` scheme, which exposes methods added to
684
:class:`.TypeEngine.Comparator` onto the owning :class:`.ColumnElement`.
685
For example, to add a ``log()`` function
688
from sqlalchemy import Integer, func
690
class MyInt(Integer):
691
class comparator_factory(Integer.Comparator):
692
def log(self, other):
693
return func.log(self, other)
695
Using the above type::
697
>>> print sometable.c.data.log(5)
702
are also possible. For example, to add an implementation of the
703
Postgresql factorial operator, we combine the :class:`.UnaryExpression` construct
704
along with a :class:`.custom_op` to produce the factorial expression::
706
from sqlalchemy import Integer
707
from sqlalchemy.sql.expression import UnaryExpression
708
from sqlalchemy.sql import operators
710
class MyInteger(Integer):
711
class comparator_factory(Integer.Comparator):
713
return UnaryExpression(self.expr,
714
modifier=operators.custom_op("!"),
717
Using the above type::
719
>>> from sqlalchemy.sql import column
720
>>> print column('x', MyInteger).factorial()
725
:attr:`.TypeEngine.comparator_factory`
727
.. versionadded:: 0.8 The expression system was enhanced to support
728
customization of operators on a per-type level.
440
731
Creating New Types
441
732
~~~~~~~~~~~~~~~~~~
added
removed
doc/_sources/core/types.txt
