You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/02/02 20:13:04 UTC
[GitHub] [airflow] potiuk commented on pull request #21272: Limit SQLAlchemy until MSSQL datetime bug is fixed
potiuk commented 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 :)
--
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