Re: [sqlalchemy] object “is already present in this session” when using a new session with older objects

[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.

On Friday, December 4, 2020 at 11:27:57 AM UTC-5 Vinit Shah wrote:

>
> 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  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
>
> On Monday, November 23, 2020 at 10:01:53 AM UTC-5 Simon King wrote:
>
>> 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 
>>
>> On Thu, Nov 19, 2020 at 11:16 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 > at 0x10592fe50>; another instance with key ( , 
>> (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 
>> > 
>> > -- 
>> > 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 

Re: [sqlalchemy] object “is already present in this session” when using a new session with older objects

[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  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

On Monday, November 23, 2020 at 10:01:53 AM UTC-5 Simon King wrote:

> 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
>
> On Thu, Nov 19, 2020 at 11:16 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  0x10592fe50>; another instance with key ( , 
> (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
> >
> > --
> > 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
> .
>

-- 
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 

Re: [sqlalchemy] object “is already present in this session” when using a new session with older objects

[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

On Thu, Nov 19, 2020 at 11:16 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  0x10592fe50>; another instance with key ( , 
> (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
>
> --
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/4d01df93-16ed-45a4-82f3-de04a8e57bcbn%40googlegroups.com.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfk-xGOf5Je8Jubq0gtA6U6fFnwnBPj5SOFyArwJ51KbA%40mail.gmail.com.

Re: [sqlalchemy] object “is already present in this session” when using a new session with older objects

[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  0x10592fe50>; another instance with key ( , 
> (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: 
> 

[sqlalchemy] 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 ; another instance with key ( , 
(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

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