Is there a way to enforce timezone awareness when dealing with datetime fields using marshmallow-sqlalchemy with sqlite backend?

Question

I am trying to create an API for my application using sqlalchemy and sqlalchemy-marshmallow for automatic (de)serialization of objects. There are some objects that will contain datetime fields, that I would like to be considered always UTC. The reality is that, also if I specify an utc timestamp for those fields using e.g. datetime.datetime.utcnow(), they are always treated as naive timestamps when serializing and deserializing. After digging a bit in the web, I figured out that sqlite only deals with naive timestamp columns unless you do not implement some custom solution. I do not really need to store the timezone indicator in the database, since I only want to deal with UTC but I would like sqlalchemy-marshmallow to serialize/deserialize my timestamp columns using isoformat with timezone, e.g. 2022-09-05T07:17:30Z. I already tried to specify Column(DateTime(timezone=True)) or datetimeformat = '%Y-%m-%dT%H:%M:%SZ' in the Meta class of the marshmallow-sqlite schema. What I came up so far with is:

# Ruleset sqlalchemy class
class Ruleset(AbstractBase):
    __tablename__ = 'ruleset'
    name = Column(String, primary_key=True)
    definition = Column(JSON, nullable=False)
    scenario = Column(String, nullable=False)
    status = Column(Boolean, nullable=False)
    created = Column(DateTime(timezone=True), nullable=False)
    last_modified = Column(DateTime(timezone=True), nullable=False)
    variables = relationship("Variable", secondary=ruleset_variable, back_populates="rulesets")

# Ruleset marshmallow schema

class UTCDateTime(fields.DateTime):
    """ Extends marshmallow standard DateTime to enforce utc format. """

    fields.DateTime.SERIALIZATION_FUNCS['iso_with_utc'] = \
        lambda x: x.strftime('%Y-%m-%dT%H:%M:%SZ')
    fields.DateTime.DESERIALIZATION_FUNCS['iso_with_utc'] = \
        lambda x: datetime.datetime.strptime(x, '%Y-%m-%dT%H:%M:%SZ').replace(tzinfo=datetime.timezone.utc)


class RulesetSchema(SQLAlchemyAutoSchema):
    created = UTCDateTime(format='iso_with_utc')
    last_modified = UTCDateTime(format='iso_with_utc')

    class Meta:
        model = Ruleset
        include_relationships = True
        load_instance = True

This custom solution is working fine and was inspired by this topic: flask-marshmallow serialize datetime to unix timestamp? I wonder if this was really something necessary or there is a better (and safer) way already offered by these libraries to enforce an isoformat with utc. Thanks.

Is there a way to enforce timezone awareness when dealing with datetime fields using marshmallow-sqlalchemy with sqlite backend?

0 Answers0