object “is already present in this session” when using a new session with older objects

[Vinit Shah]

I posted this on StackOverflow a few days ago, but I haven't been able to figure this one out yet. The original post can be found here: StackOverflow: object "is already present in this session".

I'm seeing the below error:

sqlalchemy.exc.InvalidRequestError: Can't attach instance <ObjectType at 0x10592fe50>; another instance with key ( <class '__main__.ObjectType'>, (1,), None) is already present in this session.

I'm seeing this issue when I try to instantiate a new object that has a foreign key relationship with an existing object that was created in another session.

This happens in a few different cases in my actual code, but in the provided sample it occurs with the following steps:

1. Add a new object into a new session

2. Close session and remove() from scoped_session

3. Reference the object in two newly constructed ones via their relationship

4. Error appears on the second object

    # typeDict just contains a pre-fetched ObjectTypes 

    tokyo = Location(name="tokyo", objectType=typeDict['location'])

    tokyo = write(tokyo)

    # If I clear out the current session here, the error will occur

    scopedSessionFactory().close()

    scopedSessionFactory.remove()

    westGate = Gate(name="westGate", destination=tokyo, objectType=typeDict['gate'])

    westGate = write(westGate)

    luggage = LocationInput(name="luggage", objectType=typeDict['locationinput'])

    luggage = write(luggage)

    # This is the line where the error occurs

    eastGate = Gate(name="eastGate", origin=tokyo, destinationInput=luggage, objectType=typeDict['gate'])

    eastGate = write(eastGate) 

I'm not sure what exactly causes this or way. For this example, I could just reuse the same session, but I'd like to be able to take an object from one closed session and add as a relationship field to another.

Full code sample available here: 

https://gist.github.com/funseiki/a73424bebfb0d809e6d934f699a725bf

[Mike Bayer]

On Thu, Nov 19, 2020, at 6:15 PM, Vinit Shah wrote:

I posted this on StackOverflow a few days ago, but I haven't been able to figure this one out yet. The original post can be found here: StackOverflow: object "is already present in this session".

I'm seeing the below error:

sqlalchemy.exc.InvalidRequestError: Can't attach instance <ObjectType at 0x10592fe50>; another instance with key ( <class '__main__.ObjectType'>, (1,), None) is already present in this session.

I'm seeing this issue when I try to instantiate a new object that has a foreign key relationship with an existing object that was created in another session.

This happens in a few different cases in my actual code, but in the provided sample it occurs with the following steps:

1. Add a new object into a new session

2. Close session and remove() from scoped_session

3. Reference the object in two newly constructed ones via their relationship

4. Error appears on the second object

This can be worked around using the cascade_backrefs option which in 1.4 will default to the better setting.  you can see what's happening here in the stack trace where you will see some of this:

  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1017, in set

    value = self.fire_replace_event(state, dict_, value, old, initiator)

  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1039, in fire_replace_event

    value = fn(

  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1472, in emit_backref_from_scalar_set_event

    child_impl.append(

  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1244, in append

    collection.append_with_event(value, initiator)

  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/collections.py", line 654, in append_with_event

see the "backref" ?  your program seeks to add objects to the session using session.merge() exclusively.   but above you can see the offending object is being "added", that's wrong, it's being "added" due to an "append" to your collection, which is from a backref, all of that is what you don't want and it's because of "origin=tokyo, destinationInput=luggage" are reverse-cascading your object directly into the session including the "origin" which you'd prefer is merged.

this is all described at https://docs.sqlalchemy.org/en/13/orm/cascades.html#controlling-cascade-on-backrefs , however the example there assumes the relationship is configured on the one-to-many side.  so to set this for your example, it's a little more verbose:

class Gate(Thing):

    __tablename__ = "gates"

    id = Column(Integer, ForeignKey("things.id"), primary_key=True)

    originId = Column(Integer, ForeignKey("locations.id"))

    origin = relationship(

        Location,

        foreign_keys=[originId],

        backref=backref("originGates", cascade_backrefs=False),

        cascade_backrefs=False,

    )

    originInputId = Column(Integer, ForeignKey("locationinputs.id"))

    originInput = relationship(

        LocationInput,

        foreign_keys=[originInputId],

        backref=backref("originInputGates", cascade_backrefs=False),

        cascade_backrefs=False,

    )

    destinationId = Column(Integer, ForeignKey("locations.id"))

    destination = relationship(

        Location,

        foreign_keys=[destinationId],

        backref=backref("destinationGates", cascade_backrefs=False),

        cascade_backrefs=False,

    )

    destinationInputId = Column(Integer, ForeignKey("locationinputs.id"))

    destinationInput = relationship(

        LocationInput,

        foreign_keys=[destinationInputId],

        backref=backref("destinationInputGates", cascade_backrefs=False),

        cascade_backrefs=False,

    )

in 1.4, cascade_backrefs defaults to False so you won't need to do this anymore.

    # typeDict just contains a pre-fetched ObjectTypes 

    tokyo = Location(name="tokyo", objectType=typeDict['location'])

    tokyo = write(tokyo)

    # If I clear out the current session here, the error will occur

    scopedSessionFactory().close()

    scopedSessionFactory.remove()

    westGate = Gate(name="westGate", destination=tokyo, objectType=typeDict['gate'])

    westGate = write(westGate)

    luggage = LocationInput(name="luggage", objectType=typeDict['locationinput'])

    luggage = write(luggage)

    # This is the line where the error occurs

    eastGate = Gate(name="eastGate", origin=tokyo, destinationInput=luggage, objectType=typeDict['gate'])

    eastGate = write(eastGate) 

I'm not sure what exactly causes this or way. For this example, I could just reuse the same session, but I'd like to be able to take an object from one closed session and add as a relationship field to another.

Full code sample available here: 

https://gist.github.com/funseiki/a73424bebfb0d809e6d934f699a725bf

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4d01df93-16ed-45a4-82f3-de04a8e57bcbn%40googlegroups.com.

[Simon King]

I think your situation is described here:

https://docs.sqlalchemy.org/en/14/orm/session_state_management.html#merge-tips

I'm not certain, but my guess is that when you create westGate, you
cause a *copy* of typeDict['gate'] to be merged into the current
session (because the merge cascades across the objectType
relationship). This instance is marked as pending. Then when you try
to create eastGate, the same thing happens again, but now there is
*already* a pending copy of typeDict['gate'] in the session, so you
get an error.

The easiest solution would be to create a session-specific copy of
typeDict, either by re-querying from the database, or by using
session.merge with load=False.

Hope that helps,

Simon

[Vinit Shah]

Thanks both Mike and Simon for the input! Apologies for the late reply, I've been trying to work this change into the non-example code, but ran into some issues. Disabling the Cascading Backrefs resolved the error in the sample, but I've stumbled onto a couple of different problems.

Something to note: I'm using the classical mapping approach and calling the mapper method direction.

The issue in my own code base is that I'm seeing the following:

tokyo.destinationGates = [Gate(id=null, name='westGate'), Gate(id=west gate's id, name='westGate')]

The null id row is inserted after westGate's instantiation and the other is inserted after the merge+commit. 

I've attempted to reproduce the issue in a gist, but have run into something slightly different.

In this new example, I'm getting an Instance <Location at 0x110dd0a50> has a NULL identity key. I think this should be 1:1 with the original example, but it looks like I may have missed something. I can post these questions separately, but I figured since it is related to the original issue I'd post here.

The gist can be found below:

https://gist.github.com/funseiki/3ddb35f849ed3c9bfb38eae404e7b8b4

[Vinit Shah]

Ah! I found it. In the classical mapping in the example, I forgot to set inherits=True. 

Upgrading to 1.4.0b1 solved the other issues for me.