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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@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 > > <https://groups.google.com/d/msgid/sqlalchemy/983a49db-ae3f-422a-b996-b176f76c5aa7%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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/2aaaf338-42a3-4afe-95e6-d3de492b7c8e%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.