How to have MySQL store TIMESTAMP but return timezone-aware datetime objects

[Charles-Axel Dein]

Hi,

I'm trying to have a deleted_at column on my records. I use MySQL and latest sqlalchemy as of writing. For historical reasons, I want to keep using the MySQL's TIMESTAMP columns.

I would like to use timezone-aware datetime throughout my codebase. Everything in my DB is stored as UTC, so I don't really need to store the timezone. I just want to make sure the datetime is returned as a UTC-datetime.

import pytz
from sqlalchemy import Column, text, types
from sqlalchemy.dialects.mysql import TIMESTAMP as M_TIMESTAMP


# Fractional second precision
FSP = 6
TIMESTAMP = M_TIMESTAMP(fsp=FSP)
CURRENT_TIMESTAMP = text("CURRENT_TIMESTAMP(%d)" % FSP)


class TimezoneAwareTimestamp(types.TypeDecorator):
    """Ensure tz-aware timestamp are returned."""


    impl = TIMESTAMP


    def process_result_value(self, value, dialect):
        if not value:
            return None
        return value.replace(tzinfo=pytz.UTC)


def DeletedAt():
    return Column("deleted_at", TimezoneAwareTimestamp, server_onupdate=text("0"),
                  nullable=True)

This is a pretty natural solution I came up with. Problem: TimezoneAwareTimestamp does not respect the `server_onupdate` attribute on creation. A table created with this DeletedAt column will show up as:

deleted_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

instead of (replacing TimezoneAwareTimestamp with TIMESTAMP):

deleted_at` timestamp(6) NULL DEFAULT NULL

How can I have the custom type respect server_onupdate and nullable?

Thanks,

Charles

[Mike Bayer]

On Thu, Jun 27, 2019, at 12:02 PM, Charles-Axel Dein wrote:

Hi,

I'm trying to have a deleted_at column on my records. I use MySQL and latest sqlalchemy as of writing. For historical reasons, I want to keep using the MySQL's TIMESTAMP columns.

I would like to use timezone-aware datetime throughout my codebase. Everything in my DB is stored as UTC, so I don't really need to store the timezone. I just want to make sure the datetime is returned as a UTC-datetime.

import pytz
from sqlalchemy import Column, text, types
from sqlalchemy.dialects.mysql import TIMESTAMP as M_TIMESTAMP


# Fractional second precision
FSP = 6
TIMESTAMP = M_TIMESTAMP(fsp=FSP)
CURRENT_TIMESTAMP = text("CURRENT_TIMESTAMP(%d)" % FSP)


class TimezoneAwareTimestamp(types.TypeDecorator):
    """Ensure tz-aware timestamp are returned."""


    impl = TIMESTAMP


    def process_result_value(self, value, dialect):
        if not value:
            return None
        return value.replace(tzinfo=pytz.UTC)


def DeletedAt():
    return Column("deleted_at", TimezoneAwareTimestamp, server_onupdate=text("0"),
                  nullable=True)

This is a pretty natural solution I came up with. Problem: TimezoneAwareTimestamp does not respect the `server_onupdate` attribute on creation. A table created with this DeletedAt column will show up as:

deleted_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

So you need to instead be looking at the DDL that's being emitted and not the "SHOW CREATE TABLE" which I assume is what's above.

"server_onupdate" does *not* generate any DDL, as there is no such thing as "ON UPDATE" in SQL; this is a MySQL-specific extension that I believe only applies to their TIMESTAMP datatype in the first place.

Support for MySQL's "ON UPDATE" phrase is not included as a first class feature in SQLAlchemy right now and https://github.com/sqlalchemy/sqlalchemy/issues/4652 seeks to add this functionality.   However a widely used workaround is to apply the "ON UPDATE" inside the "server_default" field, which *is* part of standard SQL, e.g. server_default=text("DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP") or whatever you want it to be.

as for the nullable part, you've unfortunately found a bug, in that the TypeDecorator is preventing it from detecting the special case nullability for TIMESTAMP.  It will be fixed in about 90 minutes https://github.com/sqlalchemy/sqlalchemy/issues/4743 but a new SQLAlchemy release isn't for a couple of weeks most likely.    For now what I would do is add an "ALTER TABLE" command in a textual way to your metadata, and you can make whatever result you'd like occur here:

class A(Base):

    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

    data = DeletedAt()

event.listen(

    A.__table__,

    'after_create',

    DDL(

        "ALTER TABLE a MODIFY deleted_at TIMESTAMP NULL "

        "DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP").execute_if(dialect="mysql")

)

the ALTER will fire off after the CREATE TABLE.

instead of (replacing TimezoneAwareTimestamp with TIMESTAMP):

deleted_at` timestamp(6) NULL DEFAULT NULL

How can I have the custom type respect server_onupdate and nullable?

Thanks,

Charles

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/983a49db-ae3f-422a-b996-b176f76c5aa7%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Charles-Axel Dein]

Thanks so much for the detailed answer Mike, and for the quick fix! You're the best.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2aaaf338-42a3-4afe-95e6-d3de492b7c8e%40www.fastmail.com.