On Mon, Aug 7, 2017 at 10:45 AM, Ruben Di Battista <rubendibatti...@gmail.com> wrote: > Hello, > I have a tables with two ForeignKeys. When I remove the relation on one > side, SQLAlchemy sets to 'NULL' the related ForeignKey, but the related row > is not considered orphaned since it hase still the other ForeignKey.
an object is considered orphaned when *any* relationships that refer to it with "delete-orphan" set are non-present as parents. There is a flag "legacy_is_orphan" which enables the pre-0.8 "orphan" concept which is what you describe: http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=legacy_is_orphan#sqlalchemy.orm.mapper.params.legacy_is_orphan Is > there a way to make SQLAlchemy fulfill the `orphan-delete' cascade when only > one of the multiple ForeignKeys are removed? If I set them as NOT NULL it > will cause an Error. in this case the specific issue is that you are making multi-state mutations to the sensor.readings collection before any of the objects are associated with the Session, leading it to miss the specific event hook that accommodates for the expunging of pending items removed from the collection; works as: s = session = Session(e) sensor = Sensor('Pressure Sensor') room = Room('bedroom') readings = sensor.read(room) session.add(sensor) accepted_readings = [] for r in readings: if r.voltage > 10: accepted_readings.append(r) sensor.readings = accepted_readings session.commit() issue https://bitbucket.org/zzzeek/sqlalchemy/issues/4040/expunge-pending-orphans-on-flush-that is added to attempt to accommodate for the specific case of late-added pending "orphan". The complexity is that we include that the following should always try to INSERT and then fail, rather than silently ignore the request: obj = Reading() session.add(obj) session.commit() so in this case we need to distinguish between the fact that the Reading was added and removed via cascade and not by explicit session.add(). orphaning is only supposed to occur when the de-association happens, not if the object was never associated. > > MWE: > > from sqlalchemy import Column, Integer, String, DateTime, Float,\ > ForeignKey > from sqlalchemy.orm import relationship, sessionmaker > from sqlalchemy.ext.declarative import declarative_base > from pyggdrasill.sql.schema import connect_db > > from datetime import datetime > > Base = declarative_base() > > > class Sensor(Base): > __tablename__ = 'sensor' > > id = Column(Integer, primary_key=True) > name = Column(String(150)) > readings = relationship("Reading", backref='sensor', > cascade='all, delete-orphan') > > def __init__(self, name): > self.name = name > > def read(self, room): > return [ > Reading( > self, > room, > datetime.now(), > 10.0), > > Reading( > self, > room, > datetime.now(), > 20.0), > > Reading( > self, > room, > datetime.now(), > 30.0) > ] > > > class Room(Base): > __tablename__ = 'room' > > id = Column(Integer, primary_key=True) > name = Column(String(150)) > readings = relationship("Reading", backref='room', > cascade='all, delete-orphan') > > def __init__(self, name): > self.name = name > > > class Reading(Base): > __tablename__ = 'reading' > > id = Column(Integer, primary_key=True) > date = Column(DateTime) > voltage = Column(Float) > > sensor_id = Column(Integer, ForeignKey('sensor.id')) > room_id = Column(Integer, ForeignKey('room.id')) > > def __init__(self, sensor, room, date, voltage): > self.sensor = sensor > self.room = room > self.date = date > self.voltage = voltage > > def __repr__(self): > return '<Read {} {}>'.format(self.date, self.voltage) > > > if __name__ == '__main__': > > db = connect_db( > username='pygg', > password='albero della vita', > db_name='pyggdrasill', > echo=False) > > Base.metadata.create_all(db) > S = sessionmaker(db) > session = S() > > sensor = Sensor('Pressure Sensor') > room = Room('bedroom') > readings = sensor.read(room) > > accepted_readings = [] > for r in readings: > if r.voltage > 10: > accepted_readings.append(r) > > sensor.readings = accepted_readings > session.add(sensor) > session.commit() > > print(len(accepted_readings)) > print(len(session.query(Reading).all())) > > > > > -- > 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 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. -- 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 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.
