Hi, I'm trying to index some data from my postgres database to elasticsearch. I do something similar to what is described in this blog post: https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-xvi-full-text-search I store the new or modified objects in the before_flush event and want to store a representation of the object in the after_commit event.
My issue is that I have some relationships in my objects and the following exception is raised by sqlalchemy: sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction. Here is a short example to illustrate the issue: import itertools from sqlalchemy import event, Column, Integer, String, ForeignKey, create_engine from sqlalchemy.orm import Session, relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() @event.listens_for(Session, "before_flush") def before_flush(session, flush_context, instances): session._changes = [] for obj in itertools.chain(session.new, session.dirty): print(f"before_flush: {obj}") if isinstance(obj, (Device, Host)): session._changes.append(obj) @event.listens_for(Session, "after_commit") def after_commit(session): print("after_commit") for obj in session._changes: print(obj.to_dict()) class User(Base): __tablename__ = "user_account" id = Column(Integer, primary_key=True) username = Column(String, nullable=False, unique=True) def __str__(self): return self.username class Device(Base): __tablename__ = "device" id = Column(Integer, primary_key=True) name = Column(String) user_id = Column(ForeignKey("user_account.id"), nullable=False, default= 1) host_id = Column(Integer, ForeignKey("host.id")) user = relationship("User") def __str__(self): return self.name def to_dict(self): return { "id": self.id, "name": self.name, "user": str(self.user), "host": str(self.host), } class Host(Base): __tablename__ = "host" id = Column(Integer, primary_key=True) name = Column(String) devices = relationship("Device", backref="host") def __str__(self): return self.name def to_dict(self): return { "id": self.id, "name": self.name, "devices": [device.to_dict() for device in self.devices], } # engine = create_engine("postgresql://postgres:secret@localhost/test", echo=True) engine = create_engine("postgresql://postgres:secret@localhost/test") Base.metadata.drop_all(engine) Base.metadata.create_all(engine) session = Session(engine) user = User(id=1, username="John") session.add(user) session.commit() # If I don't query the user to add it to the session, I get: # sqlalchemy.exc.InvalidRequestError: # This session is in 'committed' state; no further SQL can be emitted within this transaction. myuser = session.query(User).get(1) device1 = Device(name="mydevice") session.add(device1) session.commit() host1 = Host(name="test") session.add(host1) # This raises sqlalchemy.exc.InvalidRequestError when trying to access host1.devices session.commit() How can I make sure that all relationships are in the session before the after_commit event? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.