In SQLAlchemy, is there a way of symbolically creating a column that really creates two columns?

Question

Consider the following code:

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)

Don't worry too much about the property methods, but understand they take one Python value and must then split that Python value into two database columns.

Naturally, I'd prefer to create my own column type:

class Appointment(Base):
    scheduled_date = Column(MyDatetimeAware())

The problem is that scheduled_date isn't just one column, it would need to be multiple columns. Is there a way I can generalize a multi-column "data type" in SQLAlchemy?

Ruben Helsloot · Answer 1 · 2020-08-13T14:07:39.523

You're thinking of hybrid properties(see the docs). These can be used to exhibit different behaviour in SQL vs python settings, but can also be used to predefine certain transformations. I've often used them to transform UTC timestamps to the local timezone. Note that you define the property 1-3 times. Once as a python property, once for how you would like the SQL to function, and once for a 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)

To make the solution reusable, you could write a mixin with a wrapper around __setattr__:

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

Or to make it use only one shared timezone object:

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

I've seen those, but the goal is to be able to *reuse* those getters and setters elsewhere in the codebase. One option would be a mixin, but suppose there's more than one combined date/zimezone column on a class. Is there a way to encapsulate them so the hybrid property is defined in a reusable way? — Ken Kinder, Aug 13 '20 at 12:48
I've updated my answer with a potential mixin. You'll lose the `hybrid` aspect of the property, but since you didn't need seem to need those in the first place, it should hopefully suit your needs — Ruben Helsloot, Aug 13 '20 at 13:29
Thanks for your help Rubin, but the only problem with a mixin is that you couldn't use the same column twice. Eg, `col1 = DateTimeZone() ... col2 = DateTimeZone` — Ken Kinder, Aug 13 '20 at 14:01
Do you mean use one single `timezone`? You can easily change that. I've reflected the answer to do just that — Ruben Helsloot, Aug 13 '20 at 14:06
No, I mean to replicate the two column layout repeatedly. The comment section is a little small for this but: 'col1 = MyColType() ; col2 = MyColType()` -> 'col1_utc, col1_timezone, col2_utc, col2_timezone`, etc. You could have several date/times, and several corresponding timezones, on one model. — Ken Kinder, Aug 13 '20 at 14:09
Okay, clear, so you're not looking for a mixin, but for a custom SQLAlchemy column type — Ruben Helsloot, Aug 13 '20 at 14:13
A column type that on the db side, represents several actual columns, yes. — Ken Kinder, Aug 13 '20 at 15:34

score 0 · Answer 2 · answered Aug 13 '20 at 14:53

In the specific case of timezone aware datetime columns, you can use the TIMESTAMP column type with supported backends. In general, if you want to use fields that actually represent multiple columns, you can use a composite column type. That does, however, require you to specify the columns separately.

In SQLAlchemy, is there a way of symbolically creating a column that really creates two columns?

2 Answers2