As it was mentioned before, I create repeated elements in relationship deliberately. Moreover, alchemy allows me to do that, but it fails during deleting instances and modifying relationships. Unfortunately, this is not that case, where I can start all over again. All examples are maximally simplified and depersonalized and clearly describes my problem. As you STRONGLY recommend *never have* repeated elements in a relationship, is there another way to do this in alchemy? Just imagine in your simple example that Parent instance has list of two absolutely same children (that children have same ids). Is this possible to do via alchemy?
вторник, 20 июля 2021 г. в 16:10:12 UTC+3, Mike Bayer: > 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+...@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/a34b796b-3193-466a-94a8-a0d7f0fc98dfn%40googlegroups.com.
