Thanks.
I didn't use association proxy.
> "because you can't add Tool to Person.tools". This is real code and I can
directly append the tool instance.
class Association(db.Model):
...
id = db.Column(Integer, primary_key=True, index=True)
tool_id = db.Column(Integer, ForeignKey('tools.id'))
user_id = db.Column(Integer, ForeignKey(users.id'))
user = db.relationship('User', back_populates='tool')
tool = db.relationship('Tool', back_populates='user')
On Friday, March 8, 2019 at 8:34:53 AM UTC-5, Mike Bayer wrote:
>
> On Thu, Mar 7, 2019 at 4:46 PM Conferency <best...@gmail.com <javascript:>>
> wrote:
> >
> > Hi, I have 3 classes: two have many to many relationship between them,
> one is association class.
> >
> > class Person:
> > ...
> > tools = relationship('Association', back_populates='user',
> lazy=True, cascade='all, delete-orphan')
> >
> > class Tool:
> > ...
> > users = relationship('Association', back_populates='tool', lazy=True,
> cascade='all, delete-orphan')
> >
> >
> > One person object has 3 tools [<Tool id=1>, <Tool id=2>, <Tool id=3>].
> When I update the tool of the person I did
> >
> > person.tools = []
> > for tool_id in [2, 4, 5]:
> > tool = Tool.query.get(tool_id)
> > if tool:
> > person.tools.append(tool)
> >
> >
> > This operation fails, since when I set tools to an empty list, tool
> 1,2,3 are deleted, so only tool 4,5 are added into the list. If I remove
> the delete-orphan in the cascade, I will have some redundant data in the
> database. I can remove the unwanted tools from person.tools first instead
> of setting it to empty list. I'd like to know if there is a simple way to
> achieve same result. Thank you.
>
> that's not the real code because you can't add Tool to Person.tools,
> it refers to an "Association" object which is not pictured. Are you
> using the association proxy?
>
> If I take the example from the docs at
>
> https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects,
>
>
> I can run the same code you have and at the end the state is correct.
> It does delete the association objects that are removed, but it then
> recreates them on the append pass. If you want it to maintain the
> association objects that are already there, you need to manipulate the
> association objects directly. Try out the script below.
>
> from sqlalchemy import Column, Integer, String, ForeignKey
> from sqlalchemy.orm import relationship, backref
>
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import create_engine
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = "user"
> id = Column(Integer, primary_key=True)
> name = Column(String(64))
>
> # association proxy of "user_keywords" collection
> # to "keyword" attribute
> keywords = association_proxy("user_keywords", "keyword")
>
> def __init__(self, name):
> self.name = name
>
>
> class UserKeyword(Base):
> __tablename__ = "user_keyword"
> user_id = Column(Integer, ForeignKey("user.id"), primary_key=True)
> keyword_id = Column(Integer, ForeignKey("keyword.id"),
> primary_key=True)
> special_key = Column(String(50))
>
> # bidirectional attribute/collection of "user"/"user_keywords"
> user = relationship(
> User, backref=backref("user_keywords", cascade="all,
> delete-orphan")
> )
>
> # reference to the "Keyword" object
> keyword = relationship("Keyword")
>
> def __init__(self, keyword=None, user=None, special_key=None):
> self.user = user
> self.keyword = keyword
> self.special_key = special_key
>
>
> class Keyword(Base):
> __tablename__ = "keyword"
> id = Column(Integer, primary_key=True)
> keyword = Column("keyword", String(64))
>
> def __init__(self, keyword):
> self.keyword = keyword
>
> def __repr__(self):
> return "Keyword(%s)" % repr(self.keyword)
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> u1 = User("foo")
> k1, k2, k3, k4, k5 = (
> Keyword("k1"),
> Keyword("k2"),
> Keyword("k3"),
> Keyword("k4"),
> Keyword("k5"),
> )
>
> u1.keywords = [k1, k2, k3]
> s.add_all([u1, k1, k2, k3, k4, k5])
> s.commit()
>
>
> u1.keywords = []
> for kname in ["k2", "k4", "k5"]:
> k = s.query(Keyword).filter_by(keyword=kname).one()
> u1.keywords.append(k)
>
> s.commit()
>
> print(u1.keywords)
>
>
> output at the end:
>
> [Keyword('k2'), Keyword('k4'), Keyword('k5')]
>
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
