Forcing relationships to load before after_commit

[Benjamin Bertrand]

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?

[Mike Bayer]

On Wed, Oct 10, 2018 at 4:40 PM Benjamin Bertrand <bee...@gmail.com> wrote:
>
> 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.

so you need to disable expire_on_commit, since you want the data there
inside of after_commit there is no way around this:

session = Session(engine, expire_on_commit=False)

it's likely you might want to use a different event hook for what
you're trying to do as the above is not ideal. after_flush_postexec()
might be a better choice as that is prior to the expiration.


then you need to load all the attributes somewhere ahead of time,
here's doing it inside of before_flush:

@event.listens_for(Session, "before_flush")
def before_flush(session, flush_context, instances):

# ...
if isinstance(obj, (Device, Host)):
# ...

for attr in inspect(obj).attrs:
getattr(obj, attr.key)

where again, you can query the database inside of
after_flush_postexec() without issue and no pre-load is needed. If
you are trying to only print at the after_commit boundary, then place
the state you want into your session._changes, like the actual strings
you're looking to print, then just print those.

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Benjamin Bertrand]

Thanks for the quick answer.

On my small example, retrieving the objects from the session.new and session.dirty lists works fine in the after_flush_postexec event.

But in my web application, the lists are already empty in that event.

I ended:

1. Still retrieving the objects from the session.new and session.dirty lists in before_flush event

2. Calling obj.to_dict() in the after_flush_postexec event and saving that representation

3. Using that representation in the after_commit event

Thanks for the help!