Thanks so much for the detailed answer Mike, and for the quick fix! You're
the best.
On Thu, Jun 27, 2019 at 7:13 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
>
>
> 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
> <https://groups.google.com/d/msgid/sqlalchemy/2aaaf338-42a3-4afe-95e6-d3de492b7c8e%40www.fastmail.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/CAAN9J6r5uhzjRyQuuqqHT%2B8HN-3Q7%2BPYUTgoLPKSWOCBypLfTw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
