I have a large number of data, that from different acquisition systems. This is the ORM defined with SQLalchemy:

class DataTable(Base):
    __tablename__ = "data_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    system_id: Mapped[int] = mapped_column(ForeignKey("system.id"))
    system: Mapped["System"] = relationship()
    data_feature: Mapped[int] = mapped_column(String(32))


class System(Base):
    __tablename__ = "system"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(16))
    system_feature: Mapped[str] = mapped_column(String(16))

Systems are already there on the DB. Now I want to put a large number of data (~200k) on the DB. Each data row comes with the system name. I want to resolve those names during DB-upload into the corresponding IDs. At the moment I resolve the system names using a pandas.merge on the client side. I remove the system name columns from the myData pandas.DataFrame and renaming the id column (stemming from the systems table)

system_table = session.execute(select(System.id, System.name)).all()
system_table_df = pandas.DataFrame(system_table)
new_data = myData.merge(system_table_df, left_on='system_name', 
right_on='name').drop(['system_name', 'name'], axis=1)
new_data.rename(columns={'id': 'system_id'}, inplace=True)

Then I can insert the data into the DB.

session.execute(insert(DataTable).values(myData.to_dict(orient='records'))
session.commit()

Is there a way to perform the resolving of the system id on the DB?

I tried with insert().returning(), which brought me quite close, but did not exactly do the job. I do not know how to catch the system_name attribute from myData and use it in the where-clause.

session.execute(
insert(DataTable).values(
    system_id=select(System.id).where(System.name == "???")
).returning(DataTable), myData.to_dict(orient="records"))

Another approach was to use session.add() like in How to create a foreignkey reference with sqlalchemy. Didn’t work out either.