On Mon, Aug 7, 2017 at 10:45 AM, Ruben Di Battista
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 ''.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.
> ---
>