在Flask-SQLAlchemy中自动格式化db.Datetime值 [英] Format db.Datetime values automatically in Flask-SQLAlchemy

问题描述

我正在迁移主机，并且新主机使用更新版本的MySQL，该版本对其接受的日期时间值的格式更加严格.我知道我可以将其关闭，但是我想找到一种解决方案，以将这些值正确转换为正确的格式.最好是一个可以轻松替换为我需要更新的许多模型(大约100个)的模型.

I'm in the process of migrating hosts, and the new hosts uses a newer version of MySQL which is stricter about the format of datetime values it accepts. I know that I can turn this off, but I'd like to find a solution to properly convert these values to the correct format. Preferably one that can be easily swapped out across the many models I need to update (100 or so).

如果我找不到在模型级别进行更新的方法，则可能不得不寻找设置这些值的成千上万个位置.

If I can't find a way to update this on the model level, I may have to hunt down the thousands of locations where these values are set.

我在这里修剪了一个示例模型:

I've trimmed down an example model here:

class Timesheet(BaseModel, db.Model):
    __tablename__ = "timesheet"
    timesheet_id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, default=datetime.utcnow)
    updated = db.Column(db.DateTime)
    employee_id = db.Column(db.Integer, db.ForeignKey('user.employee_id'))
    description = db.Column(db.String(255))
    hours = db.Column(db.Numeric(9, 2))
    billable = db.Column(db.Boolean, default=False)
    retainer = db.Column(db.Boolean, default=False)
    client_id = db.Column(db.Integer, db.ForeignKey('client.client_id'), default=None)
    project_id = db.Column(db.Integer, db.ForeignKey('project.project_id'), default=None)
    task_id = db.Column(db.Integer, default=None)
    timesheet_date = db.Column(db.DateTime, default=datetime.utcnow)

我希望有一种方法可以修改db.Datetime，以便在提供ISO8601日期时间字符串('YYYY-MM-DDT00:00:00.000Z')时返回真正的Python日期时间对象

I'm hoping there is a way to modify db.Datetime so that it returns a true Python datetime object when provided with an ISO8601 datetime string ('YYYY-MM-DDT00:00:00.000Z')

正在寻找类似于以下内容的东西:

Looking for something along the lines of:

class FormattedDateTime(db.DateTime):
    def self.__set__(self, value):
        return dateutil.parser.parse(value)

然后，列定义将更改为:

The column definition would then be changed to:

updated = db.Column(FormattedDateTime)

...以便当SQLAlchemy发送到MySQL时，它将以正确的格式自动保存.

...so that it will automatically save with the correct format when SQLAlchemy sends to MySQL.

我查看了Mixins，并搜索了可以实现此目的的方法，但是似乎找不到任何好的解决方案.非常感谢您的帮助.

I've looked at Mixins and searched around for approaches that will accomplish this, but can't seem to find any good solutions. Help is much appreciated.

更新: 这是到目前为止我已经完成的工作的粗略草稿……即使在查询中对字段进行过滤/比较时，它似乎也能很好地完成工作，但是还没有经过很好的测试.

UPDATED: This is a rough draft of what I've worked out so far... it seems to do well even when filtering/comparing on the field in a query but isn't well tested just yet.

class TFDateTime(TypeDecorator):
    impl = DATETIME

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        print("process_bind_param", value, type(value))
        if type(value) == datetime or type(value) == date:
            return value
        elif type(value) == str:
            return parse(value, ignoretz=True)
        else:
            return None

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        print("process_result_value", value, type(value))
        return value

推荐答案

我很惊讶内置的DateTime类型没有自动为数据库提供有效值-这可能是SQLAlchemy中的错误吗?

I'm surprised the built-in DateTime type doesn't automatically give a valid value to the DB - it might be a bug in SQLAlchemy?

也就是说，创建自定义类型可能会满足您的要求-有一个 SQLAlchemy文档中的几个示例，但是类似的内容应该可以帮助您入门:

That said, creating a custom type will probably do what you want - there's a few examples in the SQLAlchemy docs, but something like this should get you started:

import datetime
from sqlalchemy.types import TypeDecorator, TIMESTAMP
import iso8601

class ISO8601DateTime(TypeDecorator):
    impl = TIMESTAMP

    def process_bind_param(self, value, dialect):
        if value is None:
            return None

        if isinstance(value, datetime.datetime):
            return value.strftime(<iso8601-format-string>)

        return value

    def process_result_value(self, value, dialect):
        if value is None:
            return None

        return iso8601.parse_date(value)

为简洁起见，使用 iso8601 lib为简洁起见，

Uses the iso8601 lib for brevity, date-util might be necessary. You may also need to use dialect-specific types for timestamp/datetime.

这篇关于在Flask-SQLAlchemy中自动格式化db.Datetime值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！