One-to-one delete for SQLAlchemy classes with inheritance

Question

I have a class inheritance scheme as layed out in http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {'polymorphic_on': type}


class Child(Parent):
    __tablename__ = 'child'

    id = Column(Integer, ForeignKey('parent.id'), primary_key=True)

    __mapper_args__ = {'polymorphic_identity': 'child'}

Having created an instance of Child, I'd like to be able to delete either the Child or the Parent reference without the other hanging around, but that's not what happens. When I fire up IPython...

In [1]: from stackoverflow.question import Parent, Child

In [2]: from sqlalchemy import create_engine

In [3]: from sqlalchemy.orm import sessionmaker

In [4]: session = sessionmaker(bind=create_engine(...), autocommit=True)()

In [5]: with session.begin():
   ...:     session.add(Child())
   ...:     

In [6]: session.query(Parent).all()
Out[6]: [<stackoverflow.question.Child at 0x7fe408030240>]

In [7]: session.query(Parent).delete()
---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
/.../lib/python3.4/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1062                         parameters,
-> 1063                         context)
   1064         except Exception as e:

...
--> Big traceback here...
...

IntegrityError: (IntegrityError) update or delete on table "parent" violates foreign key constraint "child_id_fkey" on table "child"
DETAIL:  Key (id)=(1) is still referenced from table "child".
 'DELETE FROM parent' {}

In [8]: session.query(Child).delete()
Out[8]: 1

In [9]: session.query(Parent).all()
Out[9]: [<stackoverflow.question.Child at 0x7fe408030240>]

Deleting the Parent instance doesn't work, and deleting the Child instance leaves the Parent instance alive and well...

After experimenting a bit, setting the keyword argument ondelete='CASCADE' in the ForeignKey in Child allows me to delete the Parent (which cascades to the Child) but the reverse doesn't happen. Is there any way to configure these two classes so that deleting the Child also deletes the Parent?

Answer 1

I think you are confusing few things.

Lets leave the inheritance aside for a moment.

1. session.query(MyTable).delete():

When you call session.query(MyTable).delete(), sqlalchemy will basically generate following SQL DELETE FROM my_table.
This means: delete all entries from table my_table

2. session.delete(my_instance); ... session.commit()

When you perform the following:

my_instance = session.query(MyTable).filter(MyTable.name == some_value).first()
session.delete(my_instance)  # !!!
# ...
session.commit()

sqlalchemy will generate following SQL: DELETE FROM my_table WHERE id = ?.
This means: delete a row from table my_table for given id (primary key)`.

Good thing is that in this case sqlalchemy is smart enough to delete rows from multiple tables in case the joined inheritance is used.

(Attempted) Answer

Now, the code you show is basically the first case. I do not think that you care about any references if you really want to delete all rows from some tables. But I really think that you want to simple remove just one instance (which might be few data rows from different tables). In this case, you should simply do the following:

# add a child
child = Child()
session.add(child)
session.commit()

# find the Child you want to delete by its primary key
child = session.query(Child).get(child_id)
# OR: find it by other filters
child = session.query(Child).filter(...).first()

# mark it for deletion
session.delete(child)
# SA will delete all properly
session.commit()