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?

Answer 1

SQLAlchemy collections support list-like append/remove operations.

p.assignees.remove(c)

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

Answer 2

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])