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

2020-11-19 Thread 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.


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

2020-11-19 Thread Mike Bayer


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 
> "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/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%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 

[sqlalchemy] Setting Foreign Key based on natural key

2020-11-19 Thread Steven James
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.

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 
"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/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%40googlegroups.com.