[GitHub] [airflow] potiuk edited a comment on pull request #21272: Limit SQLAlchemy until MSSQL datetime bug is fixed

[GitBox <gi...@apache.org>]

potiuk edited a comment on pull request #21272:
URL: https://github.com/apache/airflow/pull/21272#issuecomment-1028317786


   The story unfolds.
   
   I tried to reproduce starting from a "minimum" script that `sqlalchemy` maintainers proposed. I modified it to embed our cusomizations (UTCDateTime field and utcnow function) but so far I could not reproduce it.
   
   ```
   import pytz
   import datetime as dt
   import pendulum
   import time
   
   from sqlalchemy import Column
   from sqlalchemy import create_engine
   from sqlalchemy import DateTime
   from sqlalchemy import Integer
   from sqlalchemy import String
   from sqlalchemy.ext.declarative import declarative_base
   from sqlalchemy.orm import Session
   from sqlalchemy.types import TypeDecorator
   
   Base = declarative_base()
   
   # UTC time zone as a tzinfo instance.
   utc = pendulum.tz.timezone('UTC')
   
   class UtcDateTime(TypeDecorator):
       """
       Almost equivalent to :class:`~sqlalchemy.types.DateTime` with
       ``timezone=True`` option, but it differs from that by:
   
       - Never silently take naive :class:`~datetime.datetime`, instead it
         always raise :exc:`ValueError` unless time zone aware value.
       - :class:`~datetime.datetime` value's :attr:`~datetime.datetime.tzinfo`
         is always converted to UTC.
       - Unlike SQLAlchemy's built-in :class:`~sqlalchemy.types.DateTime`,
         it never return naive :class:`~datetime.datetime`, but time zone
         aware value, even with SQLite or MySQL.
       - Always returns DateTime in UTC
   
       """
   
       impl = DateTime(timezone=True)
   
       def process_bind_param(self, value, dialect):
           if value is not None:
               if not isinstance(value, dt.datetime):
                   raise TypeError('expected datetime.datetime, not ' + repr(value))
               elif value.tzinfo is None:
                   raise ValueError('naive datetime is disallowed')
               return value.astimezone(utc)
           return None
   
       def process_result_value(self, value, dialect):
           """
           Processes DateTimes from the DB making sure it is always
           returning UTC. Not using timezone.convert_to_utc as that
           converts to configured TIMEZONE while the DB might be
           running with some other setting. We assume UTC datetimes
           in the database.
           """
           if value is not None:
               if value.tzinfo is None:
                   value = value.replace(tzinfo=utc)
               else:
                   value = value.astimezone(utc)
   
           return value
   
   
   class Project(Base):
       """
       Simple table describing projects.
       """
   
       __tablename__ = "project"
   
       id = Column("project_id", Integer, primary_key=True)
       name = Column(String, nullable=False)
       description = Column(String, nullable=False)
       created = Column(UtcDateTime)
       modified = Column(UtcDateTime)
   
   def utcnow() -> dt.datetime:
       """
       Get the current date and time in UTC
   
       :return:
       """
       result = dt.datetime.utcnow()
       result = result.replace(tzinfo=utc)
   
       return result
   
   e = create_engine(
       "mssql+pyodbc://sa:Airflow123@mssql:1433/airflow?driver=ODBC+Driver+17+for+SQL+Server",
       echo="debug",
   )
   Base.metadata.drop_all(e)
   Base.metadata.create_all(e)
   session = Session(e)
   p1 = Project(
           name="name",
           description="desc",
           created=utcnow() - dt.timedelta(seconds=60),
           modified=utcnow()
       )
   session.add(p1)
   session.commit()
   
   time.sleep(1)
   p1.created= utcnow() - dt.timedelta(seconds=60)
   session.flush()
   ```
   
   Any comments are welcome :)
   
   The script above can be executed in Breeze with `--backend mssql`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org