CODEWITHSUNDEEP
pythonsqlalchemy
user12181582
user12181582

Reputation:

Delete and re-add database childs on editing

I have a database that has a one-many relationship. I will call it parent-child. I have a GUI to manipulate the values on the database. The GUI allows to add or delete children or update the information of the parent. Children are represented in a table on the GUI. When the user wants to create a new parent-child relationship, the form is easy. I create the parent, and for every child, the user adds to the table, I add it to a list like:

parent.children.append(child)

If the user, wants to delete, I execute:

parent.children.remove(child)

If the user wants to save commit the information he entered in the form, he clicks save and I execute using sqlalchemy:

import db 
db.session.add(parent)
db.session.commit() # Also sends inserts for children

The problem is when I need to let the user later update this information. What I currently do (I think it must be a clever way or elegant or leveraging SQLAlchemy) is loading the childs in to memory, setting an auxiliry variable called initial_children like:

import copy 
children = db.session.query(Child).all() 
self.children = db.session.query(Child).all()
self.inital_children = copy.deepcopy(self.children)

Then, when a user adds or deletes children, I execute the corresponding operation on self.children variable, and later when user clicks save, I execute a method like:

def save(self):
    if self.inital_children != self.children and \: 
        prompt('save changes ?') == prompt.YES:
             self.deleteInitialChildren()
             self.addNewChildren()
    self.closeGUI()

I suspect that SQLAlchemy should provide structures for this use case, for example, to have only one list self.children, and when committed, cleverly delete old children and insert new ones, but could not find a solution in the docs ( I don't have a deep understanding of the orm yet).

Upvotes: 2

Views: 271

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55834

Setting the delete-orphan cascade on the relationship in the parent class will ensure that children are deleted when unassigned from their parent.

If you have these models

class Parent(Base):
    __tablename__ = 'parent'
    id = sa.Column(sa.Integer, primary_key=True)
    children = orm.relationship("Child", cascade='all, delete-orphan')


class Child(Base):
    __tablename__ = 'child'
    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer, sa.ForeignKey('parent.id'))

then doing

parent.children = some_new_children

will delete any existing children of parent which are not in some_new_children.

Upvotes: 2

Related Questions