I am trying to use SQLAlchemy to repeatedly log what is happening to one object. Some example code is below Which demonstrates my aim.

from sqlalchemy.orm import session maker, declarative_base, Mapped, mapped_column
from sqlalchemy import create_engine, String, Integer
from random import randint
Base = declaritive_base()
class Person(Base):
    __tablename__ = 'people'
    id: Mapped[int] = mapped_column(Integer, primary_key=True, auto increment=True)
    name: Mapped[str] = mapped_column(String)
    age: Mapped[int] = mapped_column(Integer)
    favorite_color: Mapped[str] = mapped_column(String)
    
    def __init__(self, name, favorite_color):
        self.name = name
        self.age = 0
        self.favorite_color = favorite_color

    def change_favorite_color(self):
        colors = ['blue', 'red', 'green']
        self.favorite_color = colors[randint(0, 2)]

    def increase_age(self):
        self.age += 1


engine = create_engine('sqlite:///mydb.db', echo=False)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

Session = session maker(bind=engine)
session = Session()

susan = Person('Susan', 'pink')
for i in range(50):
    session.add(susan)
    session.commit()
    susan.increase_age()
    if susan.age % 5 == 0:
        susan.change_favorite_color()

After it is finished I would like to have a log of what Susan’s favourite colour was at every age. Presently, I end up with only what her final favourite colour is when she reaches 49.

I have found a couple of solutions. For example, at every age, generate a new person object to represent Susan specifically at that age, add that to the session, then disregard it and generate a new object for the next year. Also solutions involving a class to store only the recorded data and it is one of these which is created every time step and committed.

Both of these seem unnecessarily inelegant, I want simply to alter the attributes of the Susan object, log the current state, and then continue modifying until I want to log again and so on. I do not have an excellent understanding of SQLAlchemy and from the difficulty I have had with this, it occurs to me that it may be fundamentally against how SQLAlchemy is designed and if so why? This seems a fairly natural thing you might want to do, and is it still possible, or are there any other libraries which cater to this better?