如何在SqlAlchemy/Mysql中存储时区感知的日期时间值？

Question

我想在mysql数据库中存储一个Python时区感知的datetime属性。我使用炼金术。在SqlAlchemy类定义中，我按以下方式指定列：

    a_utcDatetime = Column(DATETIME(fsp=6))

我意识到在数据库中，该列不再有时区信息。 此外，从数据库读取后，变量也丢失了其时区信息

有什么方法可以在定义时将a_utcDatetime配置为时区感知

Answer 1

如果您确实想要存储时区偏移量（而不仅仅是遵循将所有日期/时间值存储为UTC的惯例），则需要为该偏移量值单独设置一列。我刚刚用MySQL尝试了一下，它似乎很管用：

from datetime import datetime, timezone, timedelta

from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    text,
    __version__ as sa_version,
    DATETIME,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

connection_uri = "mysql+pymysql://root:toot@localhost:3307/mydb"
engine = create_engine(connection_uri, echo=False)
Base = declarative_base()

with engine.begin() as conn:
    conn.execute(text("DROP TABLE IF EXISTS event_log"))


class EventLog(Base):
    __tablename__ = "event_log"
    id = Column(Integer, primary_key=True)
    event = Column(String(50))
    local_datetime = Column(DATETIME)
    tz_offset_minutes = Column(Integer)

    @property
    def dt_aware(self):
        return self.local_datetime.replace(
            tzinfo=timezone(timedelta(minutes=self.tz_offset_minutes))
        )

    @dt_aware.setter
    def dt_aware(self, value):
        self.local_datetime = value.replace(tzinfo=None)
        self.tz_offset_minutes = (
            value.tzinfo.utcoffset(value).total_seconds() // 60
        )

    def __repr__(self):
        return (
            f"<EventLog(id={self.id}, "
            f"event={repr(self.event)}, "
            f"str(dt_aware)='{self.dt_aware}'>"
        )


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

my_event_start = datetime(
    2020, 4, 20, 16, 20, 0, tzinfo=timezone(timedelta(hours=-7))
)
my_event = EventLog(id=1, event="an event", dt_aware=my_event_start)

session.add(my_event)
session.commit()

# check results by querying database directly
sql = text("SELECT * FROM event_log ORDER BY id")
with engine.begin() as conn:
    result = conn.execute(sql).fetchall()
print(result)
""" console output:
[(1, 'an event', datetime.datetime(2020, 4, 20, 16, 20), -420)]
"""

print(my_event)
""" console output:
<EventLog(id=1, event='an event', str(dt_aware)='2020-04-20 16:20:00-07:00'>
"""

print(type(my_event.dt_aware))  # <class 'datetime.datetime'>