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 <[email protected]> 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 <[email protected]>
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
