Mike, thank you for the answer. But I have another problem with deleting the instance. Even though all instances belong to the same session (it is possible to push foo instance):
with Session() as session: b1 = session.query(BarTable).get(1) b2 = session.query(BarTable).get(1) foo = FooTable('some_type', [b1, b2]) session.add(foo) session.commit() I get this error: sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'foo_bar' expected to delete 1 row(s); Only 2 were matched. After simple delete ex.: with Session() as session: foo = session.query(FooTable).get() session.delete(foo) session.commit() Pls tell how to delete instance with repeated elements in relation. P.S. It seems the error to be raised implicit, because "ONLY 2 were matched" понедельник, 19 июля 2021 г. в 16:22:01 UTC+3, Mike Bayer: > This is all expected behavior, the main reason you're having problems is > that you are using multiple sessions and mixing their results together. > If you need to do this, there are few approaches, the most basic being to > use the merge() method: > https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=session%20merge#sqlalchemy.orm.Session.merge > > however the main issue is that you are mixing results from multiple > sessions, which in the vast majority of cases is unnecessary. The session > corresponds to working in a single transaction at a time, and you should > normally be able to complete all the work you have for a particular > operation within that single scope. > > On Mon, Jul 19, 2021, at 8:31 AM, Evgenii wrote: > > > Hello! > I’m using many-to-many relation, and this relationship bar_list must have > list of > instances. Some of them can be repeated (ex. [inst1, inst2, inst1]). > I attach very simplified code there (all of database interaction is hidden > under the hood, user accesses database at top level, but this example > reflects > my problem). > > foo_bar_association = Table( > 'foo_bar', Base.metadata, > Column('foo_id', Integer, ForeignKey('foo.id')), > Column('bar_id', Integer, ForeignKey('bar.id')) > ) > *class* *FooTable*(Base): > __tablename__ = 'foo' > > id = Column(Integer, primary_key=*True*) > type = Column(String, nullable=*False*) > > bar_list = relationship('BarTable', > secondary=foo_bar_association, > lazy='subquery') > > *def* *__init__*(self, type_, bar_list): > self.type = type_ > self.bar_list = bar_list > *class* *BarTable*(Base): > __tablename__ = 'bar' > > id = Column(Integer, primary_key=*True*) > name = Column(String, nullable=*False*) > > *def* *__init__*(self, name): > self.name = name > > When I pass two exact instances [bar_one, bar_same_one](as a > relationship) have to be related (before session.close()) to different > sessions I have this error: > sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance > with key is already present in this session. > > *with* Session() *as* session: > bar_one = session.query(BarTable).get(1) > *with* Session() *as* session: > bar_same_one = session.query(BarTable).get(1) > *with* Session() *as* session: > foo = FooTable('some_type', [bar_one, bar_same_one]) > session.add(foo) > session.commit() > > But I don’t have any error after I create instances in same session: > > *with* Session() *as* session: > bar_one = session.query(BarTable).get(1) > bar_same_one = session.query(BarTable).get(1) > *with* Session() *as* session: > foo = FooTable('some_type', [bar_one, bar_same_one]) > session.add(foo) > session.commit() > > And after: > > *with* Session() *as* session: > foo = FooTable('some_type', [bar_one, bar_one]) > session.add(foo) > session.commit() > > I can make a work around: > > 1. Find unique instances in bar_list and replace not unique with unique > or > 2. Get all bar_list ids and get all instances in same session before > adding > foo instance. > But both of them are not pure python way and seems to be complicated > and ugly. > I hope there is simple alchemy solution (as adding simple attribute in > relationship) > > Python 3.7.10 > SQLAlchemy==1.4.15 > > > > > > -- > 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/8205e75c-6d78-4123-842b-4e342ad244een%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/8205e75c-6d78-4123-842b-4e342ad244een%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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/64a84806-e010-4dd4-b27c-9ff62f80faa0n%40googlegroups.com.