Thanks very much for the quick reply! Perhaps I’m missing something, but in this example there is only one table (‘Bird’). The derived class doesn’t have a join table, it only differs in its polymorphic identity. Does your reasoning still apply in this case?
Thanks, Tom > On 12 Sep 2016, at 23:03, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > > On 09/12/2016 05:30 PM, 'Thomas Gillam' via sqlalchemy wrote: >> Hello all, >> >> I ran into a problem today when running delete() on a query created on a >> derived polymorphic class. In a nutshell, the SQL produced from the >> batch delete didn't condition on the polymorphic type, resulting in much >> broader deletion than was intended. I'm running SQLAlchemy 1.0.9. >> >> Here's a minimal example which reproduces the problem in a temporary >> SQLite database, although I also observe the undesired behaviour on >> Postgres. > > unfortunately, the warning listed for query.update() at > http://docs.sqlalchemy.org/en/latest/orm/query.html?sqlalchemy.orm.query.Query.update > > <http://docs.sqlalchemy.org/en/latest/orm/query.html?sqlalchemy.orm.query.Query.update> > failed to be adapted for query.delete(), it is: > > The method supports multiple table updates, as detailed in Multiple Table > Updates, and this behavior does extend to support updates of > joined-inheritance and other multiple table mappings. However, the join > condition of an inheritance mapper is not automatically rendered. Care must > be taken in any multiple-table update to explicitly include the joining > condition between those tables, even in mappings where this is normally > automatic. E.g. if a class Engineer subclasses Employee, an UPDATE of the > Engineer local table using criteria against the Employee local table might > look like: > > this is not accurate for delete(), because multiple table deletes are not > supported. I will add this right now. > > > > > > >> >> | >> >> importlogging >> >> fromsqlalchemy importColumn,Float,Integer,String,create_engine >> >> fromsqlalchemy.ext.declarative importdeclarative_base >> >> fromsqlalchemy.orm importSession >> >> >> >> Base=declarative_base() >> >> >> >> classBird(Base): >> >> __tablename__ ="bird" >> >> id=Column(Integer,primary_key=True) >> >> wing_length =Column(Float) >> >> bird_type =Column(String) >> >> >> >> __mapper_args__ ={"polymorphic_on":bird_type, >> >> "polymorphic_identity":'Bird' >> >> } >> >> >> >> classPigeon(Bird): >> >> __mapper_args__ ={"polymorphic_on":Bird.bird_type, >> >> "polymorphic_identity":'Pigeon' >> >> } >> >> >> >> db_path ='C:\\Temp\\testsqllitedb3.db' <smb://Temp//testsqllitedb3.db'> >> <smb://Temp//testsqllitedb3.db' <smb://Temp//testsqllitedb3.db'>> >> >> engine =create_engine('sqlite:///'+db_path,echo=True) >> >> Base.metadata.create_all(engine) >> >> >> # Turn on logging >> >> logging.basicConfig() >> >> logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) >> >> >> session =Session(bind=engine) >> >> query =session.query(Pigeon).filter(Pigeon.wing_length >5) >> >> >> # First query for everything >> >> query.all() >> >> >> # Then query for deletion >> >> query.delete() >> >> | >> >> When running the all() query, the SQL that is logged to stdout is as >> expected -- it includes conditioning both on wing_length, and bird_type >> >> | >> 2016-09-12 18:26:22,578 INFO sqlalchemy.engine.base.Engine SELECT >> bird.id AS bird_id, bird.wing_length AS bird_wing_length, bird.bird_type >> AS bird_bird_type >> FROM bird >> WHERE bird.wing_length > ? AND bird.bird_type IN (?) >> 2016-09-12 18:26:22,579 INFO sqlalchemy.engine.base.Engine (5, 'Bird') >> | >> >> >> however, the conditioning on bird_type is not present when calling delete(): >> >> | >> 2016-09-12 18:26:22,584 INFO sqlalchemy.engine.base.Engine DELETE FROM >> bird WHERE bird.wing_length > ? >> 2016-09-12 18:26:22,584 INFO sqlalchemy.engine.base.Engine (5,) >> | >> >> >> The documentation includes some caveats about using the bulk delete with >> ORM, but from my reading these don't include the behaviour observed >> above. Would someone be able to confirm that this is indeed a bug >> please? If so, my guess as to the fix is that a few more thinks need to >> be added to the QueryContext which is constructed in >> orm.persistence.BulkUD._do_pre -- I'm not at all familiar with the code >> though, so it's just a hypothesis :) >> >> Thoughts and help would be much appreciated please! >> >> Thanks very much, >> Tom >> >> >> -- >> 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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com> >> <mailto:sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>>. >> Visit this group at https://groups.google.com/group/sqlalchemy >> <https://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. > > -- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/xusa7fjWnh0/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
