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
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'>
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>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto: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.
