SQLAlchemy: Convert column value back and forth between internal and database format

Question

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 the weird formats is that a time value is represented as a string like 23:42:30. I would like to have some magic that allows me to always use datetime.time objects on the python side.

A very simple solution would be something like:

col_raw = Column('col', String(7))

@property
def col(self):
    return datetime.strptime(self.col_raw, '%H:%M:%S').time()
@col.setter
def colself, t):
    self.col_raw = t.strftime('%H:%M:%S')

However, this only solves the problem for reading and writing data. Stuff like this would not be possible:

Table.query.filter(Table.col == time(23,42,30))

Another way would be to use the hybrid extension. That way, querying would be possible, but if I see it correctly, writing wouldn't. Besides, it requires writing coding everything twice, once in python code and once in SQL code. (Additional problem: I'm not sure if the conversion can be written using SQL code only.)

Is there really no way that combines both? Such as I define two functions, let's say python2sql and sql2python, which SQLAlchemy uses to transparently convert the values from python object to string and the back? I know that this would make some queries impossible, such as between or like or sums and the like, but that's okay.

Please keep in mind that the time thing is only example one of the cases where I need to convert data; so please try to keep the replies generic.

Answer 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))