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+unsubscr...@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/84779c15-6d34-49fe-b819-91fbf0164e4d%40www.fastmail.com.
