I think it was missing a key in foreign_keys: mapper(Sensor, sensors, properties={ 'data': relationship(Data, backref='sensor', foreign_keys=[data.c.id_meas, acquisitions.c.id_cu], primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, data.c.id_acq==acquisitions.c.id, acquisitions.c.id_cu==sensors.c.id_cu), cascade='all, delete-orphan', single_parent=True) })
Now it seems work as expected, thanks. On Feb 28, 1:03 pm, neurino <neur...@gmail.com> wrote: > Sorry if I resume this after two months but I think there's a bug in > cascade deletion of the relationship you suggested me: > > mapper(Sensor, sensors, > properties={ > 'data': relationship(Data, backref='sensor', > foreign_keys=[data.c.id_meas], > primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, > data.c.id_acq==acquisitions.c.id, > acquisitions.c.id_cu==sensors.c.id_cu), > cascade='all, delete-orphan', single_parent=True) > }) > > since, on a cascade delete of a Sensor sqlalchemy issues this query: > > SELECT data.id AS data_id, data.id_acq AS data_id_acq, data.id_meas AS > data_id_meas, data.id_elab AS data_id_elab, data.value AS data_value > FROM data, acquisitions, sensors > WHERE ? = data.id_meas AND data.id_acq = acquisitions.id AND > acquisitions.id_cu = sensors.id_cu > (1,) > > DELETE FROM data WHERE data.id = ? > > that's going to delete all data with id_meas = 1 while it should be > > SELECT data.id AS data_id, data.id_acq AS data_id_acq, data.id_meas AS > data_id_meas, data.id_elab AS data_id_elab, data.value AS data_value > FROM data, acquisitions, sensors > WHERE ? = data.id_meas AND ? = acquisitions.id_cu AND data.id_acq = > acquisitions.id AND acquisitions.id_cu = sensors.id_cu > (1, 3) > > DELETE FROM data WHERE data.id = ? > > with the `AND ? = acquisitions.id_cu` part added because Sensor has a > composite primary key (id_cu, id_meas). > > I know it's a rare situation so I have no problems in removing cascade > and doing deletions on my own but I'd like to be sure it's not a fault > of mine but a bug. > > Thanks for your support. > > On Dec 30 2010, 5:45 pm, Michael Bayer <mike...@zzzcomputing.com> > wrote: > > > > > > > > > this is again my error messages not telling the whole story, ill see if i > > can get the term "foreign_keys" back in there: > > > mapper(Sensor, sensors, > > properties={ > > 'data': relationship(Data, backref='sensor', > > foreign_keys=[data.c.id_meas], > > primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, > > data.c.id_acq==acquisitions.c.id, > > acquisitions.c.id_cu==sensors.c.id_cu), > > cascade='all, delete-orphan', single_parent=True) > > }) > > > or > > > mapper(Sensor, sensors, > > properties={ > > 'data': relationship(Data, backref='sensor', > > foreign_keys=[sensors.id_meas], > > primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, > > data.c.id_acq==acquisitions.c.id, > > acquisitions.c.id_cu==sensors.c.id_cu), > > cascade='all, delete-orphan', single_parent=True) > > }) > > > depending on if this is one-to-many or many-to-one. A relationship like > > this is really better off as a viewonly=True since populating it is not > > going to add rows to the "acquisitions" table. > > > On Dec 30, 2010, at 10:15 AM,neurinowrote: > > > > data = Table('data', metadata, > > > Column('id', Integer, primary_key=True), > > > Column('id_acq', Integer, ForeignKey('acquisitions.id'), > > > nullable=False), > > > Column('id_meas', Integer, nullable=False), > > > Column('value', Float, nullable=True), > > > ) > > > > acquisitions = Table('acquisitions', metadata, > > > Column('id', Integer, primary_key=True), > > > Column('id_cu', Integer, ForeignKey('ctrl_units.id'), > > > nullable=False), > > > Column('datetime', DateTime, nullable=False), > > > ) > > > > sensors = Table('sensors', metadata, > > > Column('id_cu', Integer, ForeignKey('ctrl_units.id'), > > > primary_key=True, > > > autoincrement=False), > > > Column('id_meas', Integer, primary_key=True, autoincrement=False), > > > Column('name', Unicode(20), nullable=False), > > > Column('desc', Unicode(40), nullable=False), > > > ) > > > > ctrl_units = Table('ctrl_units', metadata, > > > Column('id', Integer, primary_key=True, autoincrement=False), > > > Column('desc', Unicode(40), nullable=False) > > > ) > > > > and this mapping: > > > > ... > > > orm.mapper(Sensor, sensors, > > > properties={ > > > 'data': orm.relationship(Data, backref='sensor', > > > primaryjoin=and_(sensors.c.id_meas==data.c.id_meas, > > > data.c.id_acq==acquisitions.c.id, > > > acquisitions.c.id_cu==sensors.c.id_cu), > > > cascade='all, delete-orphan', single_parent=True) > > > }) > > > ... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.