On Sat, Dec 1, 2018 at 9:55 PM Mike Bayer <[email protected]> wrote: > > On Sat, Dec 1, 2018 at 9:21 PM Alex Rothberg <[email protected]> wrote: > > > > I set up the DB: > > from sqlalchemy import * > > from sqlalchemy.orm import * > > from sqlalchemy.ext.declarative import declarative_base > > > > > > Base = declarative_base() > > > > class Employee(Base): > > __tablename__ = 'employee' > > id = Column(Integer, primary_key=True) > > > > class EmployeeRecord(Base): > > __tablename__ = 'employee_record' > > > > employee_id = Column(Integer, ForeignKey(Employee.id), primary_key=True) > > > > > > employee = relationship( > > Employee, > > viewonly=True, > > backref=backref("records", passive_deletes="all",), > > passive_deletes="all", > > ) > > > > e = create_engine("postgresql://localhost/test_issue2", echo=True) > > > > Base.metadata.drop_all(e) > > Base.metadata.create_all(e) > > > > and then: > > s = Session(e) > > > > e = Employee(id=1) > > s.add(e) > > > > s.flush() > > > > print("xxxx") > > e.records.clear() > > > > and I see: > > > > xxxx 2018-12-01 21:16:13,608 INFO sqlalchemy.engine.base.Engine SELECT > > employee_record.employee_id AS employee_record_employee_id FROM > > employee_record WHERE %(param_1)s = employee_record.employee_id > > > > I don't understand why that SELECT is needed given the passive_deletes > > being set. > > passive_deletes=all is not used here because you have no cascade > delete set on Employee.records. passive_deletes only takes effect for > a cascaded delete when you were to mark the parent Employee as deleted > - this is because databases have ON DELETE CASCADE features that can > do the delete for us.
slight correction, with passive_deletes=all, you don't have to have any other "cascade" settings on the relationship - the "all" setting refers to the nulling out of foreign key columns that would normally occur when you deleted the Employee without specifying any cascade to the child objects. but this is still a flag that only applies to the case of the parent object being deleted. an actual access to a collection, even to remove all the items from it, is always going to need to know what objects were in that collection, at the very least to handle backref events. > > In this case, you are directly removing the records from the Employee, > which means you would like to emit UPDATE statements for each of > those records marking their foreign key attribute to NULL. SQLAlchemy > needs to know all the identities for this operation so the list is > loaded. As it turns out, there are no records, but the ORM didn't > know that because the attribute was not initialized (they may have > been persisted separately, such as, if you added individual > EmployeeRecord() objects with the foreign key of that Employee. > > Otherwise it seems like you are expecting that "e.records.clear()" > would do absolutely nothing, in which case, why are you calling > "e.records.clear()". If you want to avoid the SELECT in this very > specific case, set the list to [] when you first persist the object. > > > > > > > -- > > 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 [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
