How do I cascade deletes to multiple tables in SqlAlchemy?

Question

I have a table with several dependent tables that I want cascade delete. I'm having problems with it cascading too far. Some code will help explain.

class Map(Base):
    ....
    #One to many relationship between the Map and Tile.
    #Each Map is made up of many tiles
    tiles = relationship('Tile', lazy='joined', backref='map',
                          cascade="all, delete")

class Tile(Base):
    ....
    #Reference to Map class.
    map_id = Column(Integer,
                ForeignKey('maps.id'),
                nullable=False)

    #Reference to graphics for this tile
    #This is a many to one relationship, each Graphic is used by many Tiles
    graphics_id = Column(Integer,
                     ForeignKey("graphics.id"),
                     nullable=False)

    graphics = relationship("Graphic", uselist=False)

class Graphic(Base):
    ....
    #Nothing special here

The problem is that when I delete Map Graphics is also deleted which is not what I want. I assume this is to do with cascade.

How do I fix this so that deleting the Map class will delete the Tiles but not the Graphics?

Answer 1

Your code (unless some details were omitted) should work as you expect it to:
Graphics should not be deleted. As can be seen in relationship, the default cascade parameter is save-update, merge, which should not trigger the delete if you were to delete a Map.

To test, please create a routine that creates a Map, a Tile and a Graphic; then delete a Map and check if the Graphic is deleted - I would not expect this to happen. If the conjecture is correct, then your Graphic object must be deleted because of some other relationship.

I am referring to SA version 0.64, but I believe that the default configuration was not different also in earlier version.

Answer 2

I got it working by changing

graphics = relationship("Graphic", uselist=False)

to

graphics = relationship("Graphic", uselist=False, lazy='joined',
        backref=backref('tiles', cascade="all, delete, delete-orphan"))

I'm not sure that this is perfect answer but it works.