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.

Reply via email to