Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
@Mike @Simon, Thanks guys, I fully understand what you need. However I'm currently very, very busy and just don't have the time. The reason I posted without providing what you need was a little hope that maybe somebody recognized a similar problem. We have around 120 objects and trimming down to the root of the evil will take some time. I will try to do so and come back here if I don't get it. Cya, Nacho -- 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/5d11b8db-e588-47de-ba4b-5dde94f81bc9%40googlegroups.com.
Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
like Simon wrote, we would need more detail on the exact query and how you came
up with it, in the original example it refers to an ItemGroup object "ig", is
that the object that may have been deleted, is there a stack trace, etc.
If you can just put together a script that has the minimal mappings in use, and
a little bit of sample data, and the steps you are doing to get to the query
that fails, even if the script doesn't reproduce the problem I may be able to
come up with how it would come to the stack trace you are describing (need that
too as it shows where the problem originates).
On Mon, Oct 14, 2019, at 3:35 AM, natsjoo sodillepa wrote:
> Ok,
>
> I have managed to get rid of the problem, but I'm don't like what is
> happening.
> Solution: remove a secondary relation from the model:
>
> model = relationship("Model", secondary='item_group', uselist=False)
>
> After this, the following code does do a proper delete of the items:
>
> for it in self.session.query(ItemMeta).filter_by(item_group=item_group).all():
> if type(it) == ItemMeta:
> item_group.items.remove(it)
> self.session.delete(it)
> self.session.commit()
>
> However, things are still not ideal. The following should work but doesn't:
>
> for item in item_group.items:
> self.session.delete(item)
>
> Then only half of the item get deleted, which is a bit weird.
>
> Anybody any thoughts on this? Why does a secondary relation of this kind of
> effect on a delete?
>
> Kinde regards,
> Nacho
>
> --
> 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/073b7947-ed51-4b63-bfdb-a5499128c06c%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/63610416-e74e-46b2-967b-4e0d24481340%40www.fastmail.com.
Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
I can't make a guess without a script to reproduce it I'm afraid.
Sorry,
Simon
On Mon, Oct 14, 2019 at 8:35 AM natsjoo sodillepa wrote:
>
> Ok,
>
> I have managed to get rid of the problem, but I'm don't like what is
> happening.
> Solution: remove a secondary relation from the model:
>
> model = relationship("Model", secondary='item_group', uselist=False)
>
>
> After this, the following code does do a proper delete of the items:
>
> for it in self.session.query(ItemMeta).filter_by(item_group=item_group).all():
> if type(it) == ItemMeta:
> item_group.items.remove(it)
> self.session.delete(it)
> self.session.commit()
>
>
> However, things are still not ideal. The following should work but doesn't:
>
> for item in item_group.items:
>self.session.delete(item)
>
> Then only half of the item get deleted, which is a bit weird.
>
> Anybody any thoughts on this? Why does a secondary relation of this kind of
> effect on a delete?
>
> Kinde regards,
> Nacho
>
> --
> 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/073b7947-ed51-4b63-bfdb-a5499128c06c%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/CAFHwexf05rvzhR068JJ8SYP%2BxgZmgBb-bN2V-LDSZQ8AriGU9g%40mail.gmail.com.
Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
Ok,
I have managed to get rid of the problem, but I'm don't like what is
happening.
Solution: remove a secondary relation from the model:
model = relationship("Model", secondary='item_group', uselist=False)
After this, the following code does do a proper delete of the items:
for it in self.session.query(ItemMeta).filter_by(item_group=item_group).all():
if type(it) == ItemMeta:
item_group.items.remove(it)
self.session.delete(it)
self.session.commit()
However, things are still not ideal. The following should work but doesn't:
for item in item_group.items:
self.session.delete(item)
Then only half of the item get deleted, which is a bit weird.
Anybody any thoughts on this? Why does a secondary relation of this kind of
effect on a delete?
Kinde regards,
Nacho
--
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/073b7947-ed51-4b63-bfdb-a5499128c06c%40googlegroups.com.
Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
Hi Simon, Thank you for your reply. I've tried all the variant I could think of, also eg for item in items: session.delete(it), with and without remove on the list of items, all known mapper and relation params but nothing. However, I've tried a similar structure from examples from the web and those seems to work, so the problem must lie somewhere in our code. Or could it have anything to do with scoped sessions? Anyway: I'm working on the isolation of the problem, which is not that easy. I'll come back here if I've got something more MCVE or with a solution. Kind regards, Nacho -- 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/32d41bf0-11a2-4552-9ae3-450808074080%40googlegroups.com.
Re: [sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
You haven't given a complete script to reproduce the problem, so I'm
not certain what happened. The following is just a guess
You are using query(...).delete() with joined-table inheritance, which
has a lot of caveats in the docs:
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.delete
As far as I can tell, SA will issue a query to delete rows from the
item table. You've got ON DELETE CASCADE on the item_meta.id foreign
key, so I guess the db will delete the corresponding rows from
item_meta.
The default value for synchronize_session in Query.delete is
'evaluate', which means SA will try to find objects in the in-memory
session which match your deletion criteria and remove them from the
session. I'm guessing that this step is missing at least one ItemMeta
object for some reason.
When you query for Item objects, SA will first autoflush any pending
changes in the session. It looks like you have some pending changes on
an ItemMeta instance, so it tries to flush those, but the
corresponding row has already been deleted, hence the error.
If you provide a runnable test script, we may be able to give more answers.
Hope that helps,
Simon
On Fri, Oct 11, 2019 at 10:08 AM natsjoo sodillepa wrote:
>
> Hi all,
>
> We have a list of polymorphic objects from which delete object does not work,
> not matter what we try.
> The situation:
>
> class ItemGroup(Base):
> __tablename__ = 'item_group'
> __table_args__ = (
> UniqueConstraint('model_id', 'item_group_color_id',
> name='unique_model_id_item_group_color_id_uc'),
> )
>
> id = Column(Integer, primary_key=True)
> items = relationship("Item", back_populates="item_group")
>
> class Item(Base):
> __tablename__ = 'item'
>
> id = Column(Integer, primary_key=True)
>
> item_group_id = Column(ForeignKey('item_group.id'), nullable=False,
> index=True)
> item_group = relationship('ItemGroup', back_populates="items",
> uselist=False)
>
> __mapper_args__ = {
> 'polymorphic_identity': __tablename__,
> 'polymorphic_on': item_type
> }
>
> class ItemMeta(Item):
> __tablename__ = 'item_meta'
>
> id = Column(Integer, ForeignKey('item.id', ondelete="CASCADE"),
> primary_key=True)
>
> meta_name = Column(String(255, collation), nullable=False)
>
> __mapper_args__ = {
> 'polymorphic_identity': __tablename__,
> }
>
>
> The problem occurs after a delete:
>
> session.query(ItemMeta).filter_by(item_group=ig).delete()
>
> Now, querying the child works fine:
> session.query(ItemMeta).filter_by(item_group=ig).all()
> []
>
> But querying the parent:
> test_fixtures.session.query(Item).filter_by(item_group=ig).all()
>
>
> Give: Instance '' has been deleted, or its row
> is otherwise not present.
>
> In the database I can see the lingering parent objects. I guess that I have
> to use something like
> delete-orphan, but as I dont have a relation from child to father, so now I'm
> stuck.
>
> Any ideas are welcome.
>
> Kind regards,
> Nacho
>
> --
> 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/7dea2eaa-6390-4a54-abd5-fae925727c17%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/CAFHwexdiKj4vPGW%2BSB-HC6ZU%3DwABe6Vz98pSjZBNp6wLZ3DH1w%40mail.gmail.com.
[sqlalchemy] delete of polymorphic objects leaves orphaned parent objects
Hi all,
We have a list of polymorphic objects from which delete object does not
work, not matter what we try.
The situation:
class ItemGroup(Base):
__tablename__ = 'item_group'
__table_args__ = (
UniqueConstraint('model_id', 'item_group_color_id',
name='unique_model_id_item_group_color_id_uc'),
)
id = Column(Integer, primary_key=True)
items = relationship("Item", back_populates="item_group")
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
item_group_id = Column(ForeignKey('item_group.id'), nullable=False,
index=True)
item_group = relationship('ItemGroup', back_populates="items",
uselist=False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
'polymorphic_on': item_type
}
class ItemMeta(Item):
__tablename__ = 'item_meta'
id = Column(Integer, ForeignKey('item.id', ondelete="CASCADE"),
primary_key=True)
meta_name = Column(String(255, collation), nullable=False)
__mapper_args__ = {
'polymorphic_identity': __tablename__,
}
The problem occurs after a delete:
session.query(ItemMeta).filter_by(item_group=ig).delete()
Now, querying the child works fine:
session.query(ItemMeta).filter_by(item_group=ig).all()
[]
But querying the parent:
test_fixtures.session.query(Item).filter_by(item_group=ig).all()
Give: Instance '' has been deleted, or its row is
otherwise not present.
In the database I can see the lingering parent objects. I guess that I have to
use something like
delete-orphan, but as I dont have a relation from child to father, so now I'm
stuck.
Any ideas are welcome.
Kind regards,
Nacho
--
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/7dea2eaa-6390-4a54-abd5-fae925727c17%40googlegroups.com.
