How to add methods to a column using SQLAlchemy TypeDecorator

Question

I am trying to add custom methods to an ORM class member to get it to process things relevant to this member. Trying to follow the documentation.

class EnhDateTime(types.TypeDecorator):
    impl = types.DateTime
    def foo(self):
        return "foo"

class MyDoc(Base):
     id = Column(Integer, primary_key=True,  autoincrement=False) 
     created = Column(EnhDateTime)

doc = session.query(MyDoc).filter_by(id=123).one()

Unfortunately nothing happened and the member is still of DateTime type:

type(doc.created)

datetime.datetime

doc.created.foo()

AttributeError

Answer 1

The TypeDecorator is not the type it just decorates the type, i.e. it defines some methods that are called in certain circumstances, like when a value is passed to/from the database. You need to implement the two methods process_bind_param() and process_result_value(). There you may convert the value from the database to/from whatever you want.

See for example the JSON example from the TypeDecorator recipes section:

from sqlalchemy.types import TypeDecorator, VARCHAR
import json

class JSONEncodedDict(TypeDecorator):

    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

Example:

from datetime import datetime
from sqlalchemy.types import TypeDecorator, DATETIME

class _EnhDateTime(datetime):

    def foo(self):
        return 'foo'


class EnhDateTime(TypeDecorator):

    impl = DATETIME

    def process_result_value(self, value, dialect):
        if value is not None:
            value = _EnhDateTime(
                value.year, value.month, value.day, value.hour, value.minute,
                value.second, value.microsecond, value.tzinfo
            )
        return value

We don't need to implement process_bind_param() because _EnhDateTime instances are datetime instances, so the default bind processor of DATETIME should do.