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

Re: [sqlalchemy] Setting Foreign Key based on natural key

[Steven James]

Thank you for taking the time to look at this. I have shied away from using 
events in the past, but now that you mention it I can see how that could 
work for this case.

I had to add slightly to my previous solution. In some cases, the 
relationship is nullable and this solution was silently giving a NULL when 
I expected a Foreign Key constraint failure. 

@parent_key.setter
def parent_key(self, val):
non_existent_id = -999
self.parent_id = coalesce(

select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == 
val).as_scalar(),
non_existent_id
)

(the .as_scalar() is needed to make coalesce() happy)

On Thursday, 19 November 2020 at 14:36:12 UTC-5 Mike Bayer wrote:

>
>
> On Thu, Nov 19, 2020, at 2:03 PM, Steven James wrote:
>
> In general I have set up foreign keys using the following pattern:
>
> p = session.query(Parent).filter(Parent.natural_key == key).one()
> new_child = Child(parent=p)
>
> This makes a SELECT and an INSERT and is fine, but it gets a little 
> cumbersome when I want to create a new child object with many relationships 
> where I am not given the primary key. It basically boils down to requiring 
> custom constructor functions for each kind of object.
>
> What I would like is the following pattern:
>
> new_child = Child(parent_key=key)
>
> where parent_key can be a kind of hybrid_property() on the Child and it 
> would generate an `INSERT INTO child_table (parent_id) VALUES ((SELECT id 
> FROM parent_table WHERE key=?))`  I've gotten it working that way actually, 
> but I wanted to check to make sure that this pattern does not already exist 
> in some easier construct.
>
>
> I think that's a fine pattern to use, it all comes down to when you want 
> to be able to access the object.since you are setting up Child() with a 
> key before the Child has any connection to any session or transaction, that 
> rules out being able to know the "parent_id" right at that moment.You 
> could, if you wanted, use events to do that lookup at different times, such 
> as using the after_attach() event to look up the "parent" where you could 
> load the full Parent object and associate it, that way child.parent would 
> be ready before you ever invoked the INSERT.  Or you could use events like 
> before_insert(), before_update() to set up that parent_id too.  but setting 
> it to the SELECT expression is more succinct and pretty clever too.
>
> there's basically  a lot of event hooks (too many at this point) that can 
> be used for this, but you've found probably the one way to do it without 
> using a hook, so should be good.
>
>
>
> Working example:
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, 
> select
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.hybrid import hybrid_property
> from sqlalchemy.orm import Session, relationship
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parent'
> id_ = Column(Integer, primary_key=True)
> key = Column(String(4))
> data = Column(String(64))
>
> def __repr__(self) -> str:
> return 'Parent(id={id_!r}, key={key!r})'.format(**self.__dict__)
>
>
> class Child(Base):
> __tablename__ = 'child'
> id_ = Column(Integer, primary_key=True)
> parent_id = Column(ForeignKey('parent.id_'))
> data = Column(String(64))
>
> parent = relationship(Parent, backref='children', lazy='joined')
>
> @hybrid_property
> def parent_key(self):
> return self.parent.key
>
> @parent_key.expression
> def parent_key(self):
> return Parent.key
>
> @parent_key.setter
> def parent_key(self, val):
> self.parent_id = 
> select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == val)
>
>
> if __name__ == '__main__':
> e = create_engine('sqlite:///', echo=True)
> Base.metadata.create_all(e)
> s = Session(bind=e)
>
> p1 = Parent(key='p1', data='parent1')
> p2 = Parent(key='p2', data='parent2')
>
> s.add_all([p1, p2])
> s.commit()
>
> # initialize child/parent using the "natural key"
> ##  INSERT INTO child (parent_id, data) VALUES ((SELECT parent.id_ 
> FROM parent WHERE parent."key" = ?), ?)
> c = Child(data='i am the child', parent_key='p1')
> s.add(c)
> s.commit()
> print(c.parent)
>
> # update relationship using the "natural key"
> ## UPDATE child SET parent_id=(SELECT parent.id_ FROM parent WHERE 
> parent."key" = ?) WHERE child.id_ = ?
> c.parent_key = 'p2'
> s.commit()
> print(c.parent)
>
>
> -- 
> 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 
>