Delete whole hierarchy with sqlalchemy polymorphic relationship

Question

I want to delete some elements in tables that have a polymorphic relationship in sqlalchemy. Here's the model:

class Employee(Base):
  __tablename__ = 'employee'
  id = Column(Integer, primary_key=True)
  name = Column(String(50))
  type = Column(String(50))

  __mapper_args__ = {
    'polymorphic_identity':'employee',
    'polymorphic_on':type
  }

class Engineer(Employee):
  __tablename__ = 'engineer'
  id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
  engineer_name = Column(String(30))

  __mapper_args__ = {
    'polymorphic_identity':'engineer',
  }

And here's how I delete it:

e = Engineer();
e.name = "John";
e.engineer_name = "Doe";
DBSession.add(e);

q = session.query(Engineer).filter(Employee.name == "John")

q.delete(False)

I get the following error, is that a bug or am I doing it the wrong way ?

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such
column: employee.name [SQL: u'DELETE FROM engineer WHERE employee.name
= ?'] [parameters: ('John',)]

I'm expecting sqlalchemy to delete the entres in the engineer and employee tables.

should it not be `q = session.query(Engineer).filter(Engineer.name == "John")`? (instead of `Employee.name`) — lrnzcig, Apr 16 '15 at 18:33

Haleemur Ali · Answer 1 · 2015-04-17T01:41:29.953

First you should define the on delete behaviour of this relationship:

id = Column(Integer, ForeignKey('employee.id', ondelete='CASCADE'), primary_key=True)

Then, using the ORM, you can delete all engineers with name "John" through a loop:

eng_list = session.query(Engineer).filter(Employee.name == "John").all()

for eng in eng_list:
   session.delete(eng)
session.commit()

This will delete the records from both the Employee and Engineer tables.

update: comment on error message:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such 
column: employee.name [SQL: u'DELETE FROM engineer WHERE employee.name
= ?'] [parameters: ('John',)]

Your attempt tries to Delete from Engineer with a join to Employee (to access the field Employee.name). But this join is missing from the query sqlalchemy is emitting to the backend.

I don't think SQLite supports deleting with joins. Perhaps you can try to run session.query(Engineer).filter(Employee.name == "John").delete() against a different backend, and sqlalchemy may be able to emit the proper SQL statement. I haven't tried it though.

update 2: On backends that respect foreign key constraints (and the onupdate constraint has been set to cascade), it should be sufficient to delete the row in the parent row, and the linked rows in the child will automatically be deleted.

I tried this example with both MySQL & Postgresql backends, and the following query deleted the row from both tables (employee & engineer):

session.query(Employee).filter(Employee.name=='John').delete()

For some reason, on Sqlite, this only deletes the record from employee.

score 0 · Answer 2 · answered Jun 27 '17 at 06:35

Because doing the joined DELETE is not supported directly, I found an easy workaround is to use your normal joined query to select the ids to delete, then pass those ids to a separate DELETE query.

One minor annoyance is that since your returned ids are integers you would likely run into this error like I did if you try to pass those ids (technically an array of tuples) directly to the DELETE query. A simple intermediate conversion to strings fixes that.

So all together:

ids_to_delete = session.query(Engineer.id). \
    filter(Employee.name == "John"). \
    all()

# Convert the resulting int tuples to simple strings:
id_strings = [str(id_[0]) for id_ in ids_to_delete]

session.query(Engineer). \
    filter(Engineer.id.in_(id_strings)). \
    delete(synchronize_session=False)

Delete whole hierarchy with sqlalchemy polymorphic relationship

2 Answers2