考虑以下代码:
class Appointment(Base):
scheduled_date_utc = Column(DateTime) # Naive UTC
scheduled_date_timezone = Column(TimezoneType()) # TimezoneType is from sqlalchemy-utils
@property
def scheduled_date(self) -> datetime:
... (assembles scheduled_date_utc and
scheduled_date_timezone into a unified object)
@scheduled_date.setter
def scheduled_date(self, value: datetime):
... (splits up tz-aware datetime into naive UTC time,
and timezone column, and sets them separately)
不要太担心属性方法,但要理解它们采用一个Python值,然后必须将该Python值拆分为两个数据库列。
当然,我更喜欢创建自己的列类型:
class Appointment(Base):
scheduled_date = Column(MyDatetimeAware())
问题是scheduled_date
不仅仅是一列,它还需要是多列。有没有一种方法可以推广多列";数据类型";在SQLAlchemy?
您想到的是hybrid properties
(请参阅文档(。这些可以用于在SQL和python设置中展示不同的行为,但也可以用于预定义某些转换。我经常使用它们将UTC时间戳转换为本地时区。请注意,您可以定义属性1-3次。一次作为python属性,一次用于您希望SQL如何运行,还有一次用于setter。
import pytz
from sqlalchemy.ext.hybrid import hybrid_property
class Appointment(Base):
scheduled_date_utc = Column(DateTime) # Naive UTC
scheduled_date_timezone = Column(TimezoneType()) # TimezoneType is from sqlalchemy-utils
@property
def scheduled_date(self) -> datetime:
# see https://stackoverflow.com/a/18646797/5015356
return self.scheduled_date_utc
.replace(tzinfo=pytz.utc)
.astimezone(pytz.timezone(self.scheduled_date_timezone))
@scheduled_date.expr
def scheduled_date(cls):
return func.timezone(cls.scheduled_date_timezone, cls.scheduled_date_utc)
为了使解决方案可重用,您可以在__setattr__
:周围编写一个带有包装器的mixin
import pytz
class TimeZoneMixin:
def is_timezone_aware_attr(self, attr):
return hasattr(self, attr + '_utc') and hasattr(self, attr + '_timezone')
def __getattr__(self, attr):
"""
__getattr__ is only called as a last resort, if no other
matching columns exist
"""
if self.is_timezone_aware_attr(attr):
return func.timezone(getattr(self, attr + '_utc'),
getattr(self, attr + '_timezone'))
raise AttributeError()
def __setattr__(self, attr, value):
if self.is_timezone_aware_attr(attr):
setattr(self, attr + '_utc', value.astimezone(tzinfo=pytz.utc))
setattr(self, attr + '_utc', value.tzinfo)
raise AttributeError()
或者只使用一个共享的timezone
对象:
import pytz
class TimeZoneMixin:
timezone = Column(TimezoneType())
def is_timezone_aware_attr(self, attr):
return hasattr(self, attr + '_utc')
def __getattr__(self, attr):
"""
__getattr__ is only called as a last resort, if no other
matching columns exist
"""
if self.is_timezone_aware_attr(attr):
return func.timezone(getattr(self, attr + '_utc'), self.timezone)
raise AttributeError()
def __setattr__(self, attr, value):
if self.is_timezone_aware_attr(attr):
setattr(self, attr + '_utc', value.astimezone(tzinfo=pytz.utc))
self.timezone = value.tzinfo
raise AttributeError()
在时区感知日期时间列的特定情况下,可以使用支持后端的TIMESTAMP列类型。通常,如果要使用实际表示多列的字段,可以使用复合列类型。但是,这确实需要您单独指定列。