0

I have a simple parent-child table relationship setup with SQL Alchemy (using SQLite). I am trying to get the children automatically deleted when the parent is deleted.

I have read several posts here and Googled but am still stumped what I am missing.

class Portfolio(Base):
    __tablename__ = 'portfolio'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    holdings = relationship("Portfolio_Holdings", cascade='all,delete')

class Portfolio_Holdings(Base):
    __tablename__ = "portfolio_holdings"
    id = Column(Integer, primary_key=True)
    portfolio_id = Column(Integer, ForeignKey('portfolio.id'))
    ticker = Column(String)

When I delete a record in Portfolio, nothing related in Portfolio Holdings is deleted.

Creating records:

portfolio=Portfolio()
portfolio.name = "Some Name"

tickers = ['ABCD','EFGH','JKLI']
holdings_to_db = []
for ticker in tickers:
    holding=Portfolio_Holding()
    holding.ticker = ticker
    holdings_to_db.append(holding)
portfolio.holdings = holding_to_db
Session.merge(portfolio)

All the records make it in the the database; however, when I delete Portfolio, nothing is deleted from Portfolio Holdings using the following code:

session = Session()
session.query(Portfolio).delete()
session.commit()

However, this works, as someone pointed out:

portfolio = session.query(Portfolio).filter(Portfolio.id==1).one()
session.delete(portfolio)

As mature as SQL Alchemey is, I wouldn't think one would work but the other wouldn't. Still makes me think I am missing something.

A36
  • 51
  • 1
  • 6
  • Does changing the cascade to [`'all,delete-orphan'`](https://docs.sqlalchemy.org/en/13/orm/cascades.html#delete-orphan) do the trick? – SuperShoot Oct 24 '19 at 18:22
  • 1
    According to [this answer](https://stackoverflow.com/a/12801654/510937) the `cascade` only works when you delete objects using `session.delete` if oyu do stuff like `session.query().filter().delete()` then it wont work. Can you show us a minimal *reproducible* example where you show us how you create and then delete the objects and that reproduces your issue? – Bakuriu Oct 24 '19 at 18:28
  • I tried getting the portfolio object then session.delete(found object) and it works. Is a library as mature as SQL Alchemy this fragile? One method of delete works fine but another does not? To the other poster - no, 'all, delete-orphan' has no effect / still doesn't work with query().filter().delete() – A36 Oct 24 '19 at 19:59
  • There’s a big difference between fragility and flexibility. Bulk operations such as `Query.delete()` exist to provide a performant alternative to ORM methods that perform changes tracked by the session. These differences are well documented, for example: https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.delete – SuperShoot Oct 24 '19 at 21:39

0 Answers0