CODEWITHSUNDEEP
sqlalchemyrelationship
Amirfel
Amirfel

Reputation: 77

Update relationship in SQLAlchemy

I have this kind of model:

class A(Base):
    id = Column(UUID(as_uuid=True), primary_key=True, server_default=text("uuid_generate_v4()"))
    name = Column(String, nullable=False, unique=True)
    property = Column(String)


    parent_id = Column(UUID(as_uuid=True), ForeignKey(id, ondelete="CASCADE"))
    children = relationship(
    "A", cascade="all,delete", backref=backref("parent", remote_side=[id])
    )

In the background I run a task that periodically receives a message with id of parent and list of pairs (name, property) of children. I would like to update the parent's children in table (Defined by name). Is there a way to do so without reading all children, see which one is missing (name not present is message), need to be updated (name exists but property has changed) or new (name not present in db)? Do I need to set name to be my primary key and get rid of the UUID?

Thanks

Upvotes: 0

Views: 2046

Answers (1)

Peter
Peter

Reputation: 3495

I'd do a single query and compare the result against the message you receive. That way it's easier to handle both additions, removals and updates.

msg_parent_id = 5
msg_children = [('name', 'property'), ('name2', 'property2')]

stmt = select(A).where(A.parent_id == msg_parent_id)
children = session.execute(stmt).scalars()

# Example of determining what to change
name_map = {row.name: row for row in children}
for child_name, child_prop in msg_children:
     # Child exists
     if child_name in name_map:
         # Edit child
         if name_map[child_name].property != child_prop:
             print(child_name, 'has changed to', property)
         del name_map[child_name]

     # Add child
     else:
         print(child_name, 'was added')

# Remove child
for child in name_map.values():
    print(child, 'was removed')

Do I need to set name to be my primary key and get rid of the UUID?

Personally I'd add a unique constraint on the name, but still have a separate ID column for the sake of relationships.

Edit for a more ORM orientated way. I believe you can already use A.children = [val1, val2], which is really what you need.

In the past I have used this answer on how to intercept the call, parse the input data, and fetch the existing record from the database if it exists. As part of the that call you could update the property of that record.

Finally use a cascade on the relationship to automatically delete records when parent_id is set to None.

Upvotes: 1

Related Questions