SQLAlchemy:在内部和数据库格式之间来回转换列值 [英] 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.

例如，一种奇怪的格式是时间值表示为类似于23:42:30的字符串.我想拥有一些使我能够始终在python端使用datetime.time对象的魔术.

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

另一种方法是使用混合扩展名.这样一来，查询就可以了，但是如果我没看错的话，写就不可能了.此外，它要求对所有内容编写两次编码，一次使用python代码，一次使用SQL代码. (另一个问题:我不确定转换是否只能使用SQL代码编写.)

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

真的没有办法将两者结合起来吗?例如，我定义了两个函数，例如python2sql和sql2python，哪个SQLAlchemy用于将值从python对象透明地转换为string和back?我知道这将使某些查询成为不可能，例如between或like或sums之类，但这没关系.

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.

推荐答案

使用类型修饰符，用于处理与自定义格式之间的转换.定义列时，请使用此类型，而不要使用String.

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

这篇关于SQLAlchemy:在内部和数据库格式之间来回转换列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！