CODEWITHSUNDEEP
pythonmysqlsqlalchemy
TommyLike
TommyLike

Reputation: 1018

Generic solution to delete records which not used as a foreign key

Generic solution to delete records which not used as a foreign key

Here are the presets, there are several models: 'ParentA', 'ParentB','ChildAA', 'ChildBA' and so on.
the relationship between ParentX and ChildXY is the ChildXY has a foreign key of ParentX, for example:

#this is ParentA
class ParentA(Base):
    __tablename__ = 'parenta'
    id = Column(Integer, primary_key=True)
    name = Column(String(12))
    need_delete = Column(Integer)
    children = relationship("ChildAA",
                             back_populates="parent")
#this is ChildAA
class ChildAA(Base):
    __tablename__ = 'childaa'
    name = Column(String(12))
    id = Column(Integer, primary_key=True)
    need_delete = Column(Integer)
    parenta_id = Column(Integer, ForeignKey('parenta.id'))
    parenta = relationship("ParentA")

#this is ParentB
........

And I wanna delete all the records(all the childx, parentx included) whose attribute 'need_delete' is 1 and record itself havn't been used as a foreign key by child table. I found a direct but complicated way:

I can firstly go through all the childx tables and safely removd records, and then to the parentx tables and delete records with the code block one by one:

#deletion is for ParentA
for parent in session.query(ParentA).join(ParentA.children).group_by(ParentA).having(func.count(ChildAA.id) == 0):    
    if parent.need_delete == 1
        session.delete(parent)

#deletion is for ParentB
......
#deletion is for ParentC
.....
session.commit()

And this is hard coded, Is there any generic way to delete records which is used as a foreign key at the present?

Upvotes: 0

Views: 132

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

You could use NOT EXISTS, an antijoin, to query those parents which have no children and need delete:

from sqlalchemy import inspect

# After you've cleaned up the child tables:
# (Replace the triple dot with the rest of your parent types)
for parent_type in [ParentA, ParentB, ...]:
    # Query for `parent_type` rows that need delete
    q = session.query(parent_type).filter(parent_type.need_delete == 1)
    # Go through all the relationships
    for rel in inspect(parent_type).relationships:
        # Add a NOT EXISTS(...) to the query predicates (the antijoin)
        q = q.filter(~getattr(parent_type, rel.key).any())

    # Issue a bulk delete. Replace `False` with 'fetch',
    # if you do need to synchronize the deletions with the ongoing
    # SQLA session. In your example you commit after the deletions,
    # which expires instances in session, so no synchronization is
    # required.
    q.delete(synchronize_session=False)

...

session.commit()

Instead of first querying all the instances to the session and marking for deletion one by one use a bulk delete.

Do note that you must be explicit about your relationships and the parent side must be defined. If you have foreign keys referring to parent tables not defined as an SQLAlchemy relationship on the parent you'll probably get unwanted deletions of children (depends on how the foreign key constraints have been configured).

Another approach could be to configure your foreign key constraints to restrict deletions and handle the raised errors in a subtransaction (savepoint), but I suppose you've already set your schema up and that'd require altering the existing foreign key constraints.

Upvotes: 2

Related Questions