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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
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.
