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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
