[Python] Optimizing Flush: Single Multi-Row INSERT per Table Despite cascade='all'

I’d like to optimize this so that each table (e.g. trans, scripts) uses a single INSERT INTO ... VALUES (...), (...), (...) statement, instead of multiple single-row inserts.

However, since I’m using cascade="all", I only add the parent (cfg) to the session, and all related child objects (Trans, Script) are added automatically during session.flush().

Because of that, I can't use bulk_save_objects, as it doesn't support relationships or handle primary key assignment properly.

Is there a way to intercept or group the generated INSERT statements per table during the flush ( without any db intervention), so I can manually construct a batch insert (multi-row) per table?

Base = declarative_base()

class Config(Base):
__tablename__ = 'configs'
id = Column(Integer, primary_key=True)
name = Column(String)
trans = relationship("Trans", back_populates="config", cascade="all")
scripts = relationship("Script", back_populates="config", cascade="all")

class Trans(Base):
__tablename__ = 'trans'
id = Column(Integer, primary_key=True)
config_id = Column(Integer, ForeignKey("configs.id"))
name = Column(String)
config = relationship("Config", back_populates="trans")

class Script(Base):
__tablename__ = 'scripts'
id = Column(Integer, primary_key=True)
config_id = Column(Integer, ForeignKey("configs.id"))
name = Column(String)
config = relationship("Config", back_populates="scripts")


engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=True)
session = Session()

cfg = Config(name="MyConfig")
cfg.trans.append(Trans(name="T1"))
cfg.trans.append(Trans(name="T2"))
cfg.scripts.append(Script(name="S1"))
cfg.scripts.append(Script(name="S2"))

# others children will be picked up via cascade
session.add(cfg)
session.flush()

Continue reading...