Handling One-to-Many Relationships in SQLAlchemy: Issue with persisting associations of objects in database session

I'm using sqlalchemy 2.0.23, postgres 16.1 and python 3.11.6

I created a Many-to-One relationship between Publication and EducationalSystem models respectively. But, if I assign a Publication instance an unique instance of EducationalSystem that was assigned previously in the loop to another publication then the later just gets reassigned to the latest one and reference to it is removed, thus the foreign key in all previous Many side instances is null but for the latest one.

Here is the code regarding the models:

class Publication(Base):
    # __tablename__ = "publication"
    __table_args__ = (
        UniqueConstraint("section_id", "title", name="section_title_uc"),
        { "schema" : "publications", }
    )
    
    __mapper_args__ = {
        "polymorphic_identity": "publication",
        "polymorphic_on": "publication_type"
    }

    publication_type: Mapped[str] = mapped_column(String(), nullable=False)
    id = mapped_column(Integer, primary_key=True)
    title = mapped_column(String(), nullable=False)
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    updated_at: Mapped[datetime] = mapped_column(onupdate=func.now(), nullable=True)
    section_id: Mapped[int] = mapped_column(ForeignKey(Section.__table__.c.name), nullable=True)
    section = relationship("Section", back_populates="publications")
#### EducationalSystem ForeignKey ########
    educational_system_fk: Mapped[str] = mapped_column(ForeignKey("publications.educational_system.name"), nullable=True)

##### ED SYSTEM relationship object ####
    ed_system: Mapped[EducationalSystem] = relationship(back_populates="publications")
######
    unique_title_section = UniqueConstraint("section_id", "title", "draft", name="utsc") #There can only be a published article in the same section
    schedule: Mapped[datetime] = mapped_column(default=None, nullable=True)
    content: Mapped[str] = mapped_column(nullable=True)
    draft: Mapped[bool] = mapped_column(default=True)
    summary: Mapped[str] = mapped_column(nullable=True)
    quotes: Mapped[list[Quote]] = relationship(back_populates="publication")
    important: Mapped[bool] = mapped_column(nullable=True, default=False)
    views: Mapped[str] = mapped_column(default=0)
    statement: Mapped[str] = mapped_column(nullable=True)
    authors: Mapped[list[Author]] = relationship(secondary=publication_authors_table, back_populates="publications")
    images: Mapped[Image] = relationship(secondary=publication_image_table)
    editor_fk: Mapped[str] = mapped_column(ForeignKey("users.editor.username"), nullable=True)
    editor: Mapped[Editor] = relationship(back_populates="publications")
    

    #synonyms
    educational_system = synonym("ed_system")

    def __repr__(self):
        return f"{self.__class__.__name__}({self.title!r})"


class EducationalSystem(Base):
    __table_args__ = (
        {"schema": "publications"},
    )
    name: Mapped[str] = mapped_column(primary_key=True)
    publications: Mapped[Publication] = relationship(back_populates="ed_system")

Initialization function:

@asynccontextmanager
async def initialize(app: FastAPI):
    with SessionLocal(autoflush=True) as session:
        Base.metadata.create_all(bind=engine)
        stmts = [
            "INSERT INTO users.role VALUES('admin') ON CONFLICT DO NOTHING;"
            "INSERT INTO users.role VALUES('editor') ON CONFLICT DO NOTHING;"
            "INSERT INTO users.role VALUES('normal') ON CONFLICT DO NOTHING;"
            "INSERT INTO publications.section VALUES('news') ON CONFLICT DO NOTHING;"
            "INSERT INTO publications.section VALUES('inquiries') ON CONFLICT DO NOTHING;"
            "INSERT INTO publications.section VALUES('resources') ON CONFLICT DO NOTHING;"
            "INSERT INTO publications.section VALUES('experiences') ON CONFLICT DO NOTHING;"
        ]
        
        for stmt in stmts:
            session.execute(text(stmt))

        users = None
        pubs = None
        authors = None
        ed_systems = None
        quotes = None
        statements = None
        
        with open("./users.json") as f: users = json.load(f)
        with open("./publication.json") as f: pubs = json.load(f)
        with open("./authors.json") as f: authors = json.load(f)
        with open("./educational_systems.json") as f: ed_systems = json.load(f)
        with open("./quotes.json") as f: quotes = json.load(f)
        with open("./statements.json") as f: statements = json.load(f)
        
        editor_objs = []

        for user in users:
            class_ = eval(user["role_fk"].capitalize())
            user_obj = class_(**user)
            if isinstance(user_obj, Editor): editor_objs.append(user_obj)
            session.add(user_obj)
        try: session.commit() #commit users
        except sqlalchemy.exc.IntegrityError as err: print(err)

        author_list = []
        for author in authors:
            author["social_media_handles"] = [SocialMediaHandle(handle=handle) for handle in author["social_media_handles"]] 
            author_obj = Author(**author)
            session.add(author_obj)
            author_list.append(author_obj)
        
        ed_system_objs = []
        
        ###### WHERE EducationalSystems created ######
        for ed_system in ed_systems:
            ed_system_obj = EducationalSystem(name=ed_system)
            ed_system_objs.append(ed_system_obj)
            session.add(ed_system_obj)
            session.commit()

        
        for pub in pubs:
            class_ = eval(
                coerce_genre(pub["publication_type"], to="camel_case").pop()
            )
            pub["authors"] = choices(author_list, k=randint(1,3))
            pub_obj = class_(**pub)
            
            pub_obj.quotes.extend([Quote(**quote) for quote in choices(quotes, k=randint(1,3))])
            pub_obj.statement = statements[randint(0, len(statements) -1)]
            pub_obj.summary = pub_obj.statement
            ## ADDING a same educationalsystem to every publication ###
            pub_obj.ed_system = ed_system_objs[0]
            pub_obj.editor = editor_objs[randint(0, len(editor_objs) - 1)]
            session.add(pub_obj)
            session.commit() # commit
            
        
        
        
        try: session.commit()
        except sqlalchemy.exc.IntegrityError as err: print("Initial registries already exist, SKIPPING!")
        except Exception as err: print(f"{err}")
    yield
    with SessionLocal.begin() as session:
        print("DROPING DATABASE!")
        Base.metadata.drop_all(bind=engine)

Here is a photo of how it looks in the database (postgres): Database view

• I tried creating and assigning to multiple publications an unique educationalSystem object, and expected sqlalchemy to fill in the foreign key field of each one accordingly. But it seems is not that intuitive with ORM mappings.

I searched in the docs for the common relationship patterns section and see if I could spot some misdoing on my part, but in a nutshell is about the relationship pattern establishment, setting on_delete triggers and to-related-entity references on each side of the relationship.

Came across the Unit of Work system to try understand (newbee) how changes are captured and emitted to the database, and I guess it's reupdating the to-be-emitted sql each time I assign the educational_system to the latest pub, so after commiting changes it yields that result. And if allow myself to be a little paranoic, the cache could be affecting somehow implicitly.