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 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
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
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.Автор: Marian Источник Размещён: 12.11.2019 09:14
Use a type decorator that handles converting to and from the custom format. Use this type rather than
String when defining your column.
Автор: davidism Размещён: 26.01.2015 01:53
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))