Getter/setter in SQLAlchemy ORM mapped Declarative Table

Question

I want to change a value into a different format during assignment to a mapped table object in SQLAlchemy and then perform the reverse formatting for that column when selecting the row.

Desired

# Adds a row with a formatted start_time="2023-03-01 12:00:00"
my_table_row = MyTable(start_time="20230301-120000")
session.add(my_table_row)
session.flush()


# Returns a MyTable object with an inverse formatted start_time="20230301-120000"
result = session.execute(
    select(MyTable).filter_by(start_time="2023-03-01 12:00:00")
).scalar_one()

Attempted

I am trying to use the @property and @attr.setter decorator as per this. However, the values are added and retrieved from the DB without any formatting changed.

from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped
from sqlalchemy import Table, Column, Integer, String

class Base(DeclarativeBase):
    pass


class MyTable(Base):

    __table__ = "test_table"

    _start_time: Mapped[str] = mapped_column("start_time", String(23), nullable=True, index=True)

    @property
    def start_time(self):
        return convert_from_db_format(self._start_time)

    @start_time.setter
    def start_time(self, start_time):
        self._start_time = convert_to_db_format(start_time)


def convert_to_db_format(input_time):
    return datetime.strptime(
        input_time,
        "%Y%m%d-%H%M%S"
    ).strftime("%Y-%m-%d %H:%M:%S")

def convert_from_db_format(input_time):
    return datetime.strptime(
        input_time,
        "%Y-%m-%d %H:%M:%S"
    ).strftime("%Y%m%d-%H%M%S")

What am I doing wrong here?

The setter convert_to_db_format() works fine, but when I add the getter convert_from_db_format() it seems to reverse the effect of the setter before inserting to the DB.

Answer 1

As per the comment by @snakecharmerb, a simple solution is to utilise the in-built datetime type (instead of writing custom getters/setters).

from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped
from datetime import datetime

class Base(DeclarativeBase):
    pass


class MyTable(Base):
    __table__ = "test_table"

    start_time: Mapped[datetime] = mapped_column(DateTime())