I am trying to write a script to populate a database with mock data, but all my attempts to do so result in a failure that I would expect the ORM session to handle, such as committing parent records before attempting to commit the child.

I have the following models:

class Base(DeclarativeBase):
    pass

class One(Base):
    __tablename__ = "one"
    id: Mapped[int] = mapped_column(primary_key=True)
    twos: Mapped[List["Two"]] = relationship(back_populates="one")

class Two(Base):
    __tablename__ = "two"
    id: Mapped[int] = mapped_column(primary_key=True)
    one_id: Mapped[int] = mapped_column(ForeignKey("one.id"))
    one: Mapped["A"] = relationship(back_populates="twos")

and the following factories:

class OneFactory(SQLAlchemyModelFactory):
    class Meta:
        model = models.One
        sqlalchemy_session = db.session
    id = Sequence(lambda n: n)

class TwoFactory(SQLAlchemyModelFactory):
    class Meta:
        model = models.Two
        sqlalchemy_session = db.session

    id = Sequence(lambda n: n)
    one = SubFactory(OneFactory)

which I initialize and attempt to commit like so:

one = OneFactory()
two = TwoFactory(one=one)
db.session.commit()

Which always fails on the child constraints with an error like

Cannot add or update a child row: a foreign key constraint fails

Solutions I have tried and not worked:

• RelatedFactoryList, same problem
• SubFactory, same problem
• using sqlalchemy_session_factory and sqlalchemy_session_persistence; fails due to instances being held in different sessions or because Instance has been deleted, or its row is otherwise not present

What am I doing wrong?