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. 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.
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.