Geek asks and answers

Removing an object from a collection in SQLAlchemy

I store a bunch of patent data in a MySQL database and interact with it through SQLAlchemy. I have a collection inside the Patent class that represents a list of assignees (companies that have been granted a patent):

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

I process some objects stored in the database, and for the Patent object p, I want to remove some assignee a(company object) from the passignee list . Based on http://docs.sqlalchemy.org/en/latest/orm/session.html#deleting-from-collections , it seems that the call s.delete(a)will actually delete the Company object a. I just want to remove the assignee afrom the list of assignees for p(i.e. Delete the line in patent_company_table), DO NOT delete the Company object, because it amay be in another list of patent objects of the assignees.

I tried creating a new list new_assigneesthat includes only the assignees from pbeyond a, and then calls:

p.assignees = new_assignees
s.add(p)

This, unfortunately, does not actually mark pas dirty, so I assume that it will not affect the database.

Do you have any suggestions on removing an object from the collection, deleting a line in the patent_company_table file, and not deleting an object from the company table?

Thank.

UPDATE

Here is the code snippet:

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 passing all the patents added_patent_count = 983672, but session.dirty()no objects. Do I need to manually add a session after changing through appendor remove?

+3
source share
2 answers

SQLAlchemy collections support list add / remove operations.

p.assignees.remove(c)

This should remove cform p.assigneeswithout deleting it cfrom the database.

+7
source

script , . script, . , , - "" , , , .

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

More articles:


All Articles