Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
On Tue, Jul 20, 2021, at 10:00 AM, Evgenii wrote:
> As it was mentioned before, I create repeated elements in relationship
> deliberately.
Assuming this implies the table can have no candidate key, this is an
antipattern in SQL and there are lots of answers/articles/etc on the web why
all relational database tables need to have some kind of uniqueness to each row
(candidate key).Pure duplicated rows add essentially no information, and
it's not possible to remove some of the rows and not all of them.
The best you can do here is to use the association object pattern such that
each row in your "Secondary" table contains additional data, such as a "sort
by" key or similar, that is also part of the primary key and can be used to
differentiate multiple rows that refer to the same entities.
>
>
> Moreover, alchemy allows me to do that,
this is correct. the unit of work unfortunately does not unconditionally
dedupe when inserting rows into "secondary", as it does not require that the
table's existing contents are loaded into memory in order to persist, therefore
a unique/primary constraint on secondary should be implemented. If you use
collection_class=set, then the collection is deduped on the Python side if it's
loaded, but this is not a guarantee of uniqueness.
>> but it fails during deleting instances and modifying relationships.
correct because there is no way to delete only some of the "dupes" - if there
are dupes in the table, that means things have gone wrong. for a production
DB, this should be repaired manually and proper constraints applied.
SQLAlchemy detects this dupe condition as soon as it can as it can refer not
just to dupe rows but also mapping mis-configurations.
> 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?
you would need to add additional columns to your "secondary" table that
distinguish between the "dupe" rows and use the association object pattern for
persistence.
>
> вторник, 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
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
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*
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
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
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
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
>
>
Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session
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
>
> .
--
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.
