SQLAlchemy: convert column value back and forth between internal and base format

Question

In my database, I have several columns where the data is stored in some strange format. Since the database is also used by other code, I cannot change the data format.

For example, one of the weird formats is that the time value is represented as a string, for example 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 look something like this:

 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 of reading and writing data. Such things would be impossible:

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

Another way would be to use a hybrid extension . Thus, the request will be possible, but if I see it correctly, the letter will not be. In addition, this requires writing code only twice, once in python code and once in SQL code. (Additional problem: I am not sure that a transformation can only be written using SQL code.)

Is there really no way to combine both? For example, I define two functions, say python2sql and sql2python , that SQLAlchemy uses to transparently convert values ​​from a python object to a string and vice versa? I know that this will make some queries impossible, for example between or like or amounts, etc., but that’s good.

Please keep in mind that time is just an example of one of the cases when I need to convert data; so please try to keep general answers.

Answer 1

Use a type decorator that handles the conversion to and from a custom format. Use this type, not String when defining a 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))