Incorrect multi-table DELETE..USING SQL query when deleting Joined Inheritance Relationship object declared using __mapper_args__ = {'with_polymorphic': '*'} with SQAlchemy 1.4
#6178
Replies: 1 comment 5 replies
-
|
hey there - MySQL's DELETE..USING syntax is fairly specific to MySQL, while Postrgresql I think might also supports some variant of this, these structures are not generally available in SQL on most other databases, and the ORM generally can't support such consttructs. For this reason, ORM-enabled UPDATE and DELETE of multiple rows are explicitly not supported for joined-table inheritance setups, and this is documented in the big pink dragon box at https://docs.sqlalchemy.org/en/14/orm/session_basics.html#orm-expression-update-delete :
in short, none of this is tested or supported and it's out of scope; if you need mass UPDATE/DELETE of these structures, invoke your SQL using session.connection().execute() so that no ORM functionalities are used. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you for quick reply! Actually I don't need exactly mass-delete in my case, but just don't crash with error when deleting the parent records as it was in SQLAlchemy 1.3 (descendant object deletion can be performed by previous statement at application level). Is there any option available to have |
Beta Was this translation helpful? Give feedback.
-
|
Use the Table objects directly: stmt = delete(Engineer.__table__).where(Engineer.id == Employee.id).where(Employee.name == 'test')
session.connection().execute(stmt)
stmt = delete(Manager.__table__).where(Manager.id == Employee.id).where(Manager.name == 'test')
session.connection().execute(stmt)
stmt = delete(Employee.__table__).where(Employee.name == 'test')
session.connection().execute(stmt) |
Beta Was this translation helpful? Give feedback.
-
|
SQLAlchemy also doesnt support the MySQL DELETE..USING variant where there are multiple target tables, we can only render additional FROMs. no other database supports this concept. the most portable way to delete from all the tables is to use ON DELETE CASCADE on the foreign keys, then just DELETE from the |
Beta Was this translation helpful? Give feedback.
-
|
Then maybe it would be reasonable to force |
Beta Was this translation helpful? Give feedback.
-
|
I would have to research all the possibilities here for what kinds of mapped tables are and are not appropriate for DELETE on what drivers, it's not clear what works and what doesn't. We usually have to do something more sophisticated than "if with_polymorphic is not None: raise", mappings can be against any kinds of selectables which may or may not work. The database itself is the best authority on what SQL works and what doesn't so in the absense of a really well thought out plan for everything that will definitely not work and everything that might work, we just let the database do it. |
Beta Was this translation helpful? Give feedback.
-
Describe the bug
Query.delete()generates incorrect multi-tableDELETE..USINGSQL query when deleting parent object declared by mapper with__mapper_args__ = {'with_polymorphic': '*'}in Joined Inheritance Relationship.Incorrect SQL query:
Expected behavior
Expected to get SQL statement rather like this:
To Reproduce
https://docs.sqlalchemy.org/en/14/orm/inheritance.html#relationships-with-joined-inheritance
'with_polymorphic': '*'to__mapper_args__of parent class as shown here https://docs.sqlalchemy.org/en/14/orm/inheritance_loading.html?highlight=with_polymorphic#setting-with-polymorphic-at-mapper-configuration-timeError
Versions.
Additional context
SQLAlchemy 1.3 has no such error as it produces single-table DELETE statement.
Thank you for your attention.
Have a nice day!
Beta Was this translation helpful? Give feedback.
All reactions