Sqlalchemy: Convert Column Value Back And Forth Between Internal And Database Format

In my database, I have some columns where data is stored in some weird format. Since the database is used by other code, too, I cannot change the data format. For example, one of t

Solution 1:

Use a type decorator that handles converting to and from the custom format. Use this type rather than String when defining your column.

class MyTime(TypeDecorator):
    impl = String

    def __init__(self, length=None, format='%H:%M:%S', **kwargs)
        super().__init__(length, **kwargs)
        self.format = format

    def process_literal_param(self, value, dialect):
        # allow passing string or time to column
        if isinstance(value, basestring):  # use str instead on py3
            value = datetime.strptime(value, self.format).time()

        # convert python time to sql string
        return value.strftime(self.format) if value is not None else None

    process_bind_param = process_literal_param

    def process_result_value(self, value, dialect):
        # convert sql string to python time
        return datetime.strptime(value, self.format).time() if value is not None else None

# in your model
class MyModel(Base):
    time = Column(MyTime(length=7))