Deleting an Object from Collection in SQLAlchemy

Question

I am storing a bunch of patent data in a MySQL database and interacting with it via SQLAlchemy. I have a collection inside the Patent class that represents the list of assignees (the companies that were assigned the patent):

assignees = relationship('Company', secondary=patent_company_table, backref='patents')

I am processing some of the objects stored in the database and for a Patent object p, I want to delete some assignee a (a Company object) from p's assignee list. Based off of http://docs.sqlalchemy.org/en/latest/orm/session.html#deleting-from-collections , it seems that calling s.delete(a) will actually delete the Company object a. I simply want to remove assignee a from the list of assignees for p (i.e. remove a row in the patent_company_table), NOT actually delete the Company object, because a might be in another Patent object's list of assignees.

I tried creating a new list new_assignees that only includes the assignees from p besides a and then called:

p.assignees = new_assignees
s.add(p)

This unfortunately does not actually mark p as dirty, so I assume it would not affect the database.

Do you have any suggestions for how to remove an object from the collection, deleting the row in the patent_company_table as opposed to deleting the object from the Company table?

Thank you.

UPDATE

Here is a snippet of the code:

assignees = patent.assignees
for assignee in assignees:
    if assignee in duplicate_company_to_default:
        patent.assignees.remove(assignee)
        default_company = duplicate_company_to_default[assignee]
        if default_company not in assignees:
            added_patent_count += 1
            patent.assignees.append(default_company)

After looping through all of the patents, added_patent_count = 983672 but there are no objects in session.dirty(). Do I need to add manually to the session after modifying via append or remove?

Would you be able to extract and post the working sample script for this issue (model definition, sample duplicate dict etc)? Also wondering what type of PrimaryKey identifiers are you using? — van
– van, Commented May 2, 2012 at 9:22

Imran · Accepted Answer · 2012-04-30 06:31:56Z

14

SQLAlchemy collections support list-like append/remove operations.

p.assignees.remove(c)

This should remove c form p.assignees without deleting c from database.

answered Apr 30, 2012 at 6:31

Imran

91.7k26 gold badges101 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gwintrob Over a year ago

Thanks for the suggestion, but this does not solve the problem. I included a code snippet above.

gwintrob Over a year ago

This works correctly. The length of s.dirty is 0, but s.commit() still makes the necessary changes to the assignees relationship. Thanks a lot.

ThatAintWorking Over a year ago

Also, this will remove the object from the database if you have cascade set to all, delete, delete-orphan, etc.

zzzeek · Accepted Answer · 2012-05-07 21:41:28Z

a working sample script means, we can run it fully. Here's a script generated from the snippets you've given. The one thing that helps is to evaluate "assignees" as a list, since you are removing from it, it's likely you're not iterating correctly.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

patent_company_table = Table('pct', Base.metadata,
    Column('patent_id', Integer, ForeignKey('patent.id')),
    Column('company_id', Integer, ForeignKey('company.id'))
)

class Patent(Base):
    __tablename__ = "patent"
    id = Column(Integer, primary_key=True)

    assignees = relationship('Company', secondary=patent_company_table, backref='patents')

class Company(Base):
    __tablename__ = "company"
    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://")
Base.metadata.create_all(e)

s = Session(e)
p = Patent()
c1, c2, c3, c4, c5 = Company(), Company(), Company(), Company(), Company()
d1, d2 = Company(), Company()
duplicate_company_to_default = {c1:d1, c2:d2, c3:d1, c4:d2}

new_assignees = [c1, c2, c3, c4, c5]
p.assignees = new_assignees
s.add(p)
s.commit()

patent = s.query(Patent).first()
assignees = patent.assignees
added_patent_count = 0
for assignee in list(assignees):
    if assignee in duplicate_company_to_default:
        patent.assignees.remove(assignee)
        default_company = duplicate_company_to_default[assignee]
        if default_company not in assignees:
            added_patent_count += 1
            patent.assignees.append(default_company)

assert p in s.dirty

s.commit()

assert set(p.assignees) == set([d1, d2, c5])

Thanks for writing this up zzzeek. It illustrates the relationship well. I appreciate your help.

Collectives™ on Stack Overflow

Deleting an Object from Collection in SQLAlchemy

UPDATE

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related