if you are mapping ORM classes to the same table that is also used as the "secondary" table in a relationship() that can lead to the ORM inserting more than one row for that table. based on the name "foo_bar" I would imagine something like this might be going on.
> > Pls tell how to delete instance with repeated elements in relation. So assuming this is a development database where you can start all over again, the approach here is to *never have* repeated elements in a relationship. When you make a "secondary" table, make sure you set the columns that refer to the related tables inside of a constraint, such as UNIQUE constraint, or more commonly make them the primary key. I should add this to the docs at https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#many-to-many as this seems to not be mentioned: association_table = Table('association', Base.metadata, Column('left_id', Integer, ForeignKey('left.id'), primary_key=True), Column('right_id', Integer, ForeignKey('right.id'), primary_key=True) ) class Parent(Base): __tablename__ = 'left' id = Column(Integer, primary_key=True) children = relationship("Child", secondary=association_table) class Child(Base): __tablename__ = 'right' id = Column(Integer, primary_key=True) > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/64a84806-e010-4dd4-b27c-9ff62f80faa0n%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/6120a1b1-2d48-45df-8fa5-5a7b1f6ad449%40www.fastmail.com.
