On Fri, Mar 8, 2019 at 3:05 PM Conferency <best....@gmail.com> wrote:
>
> 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.
that will cause this error:
sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type
<class '__main__.Keyword'> as a member of collection
"User.user_keywords". Expected an object of type <class
'__main__.UserKeyword'> or a polymorphic subclass of this type.
Feel free to send a complete working example along to show what you
are doing in full.
>
> 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> 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.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > 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.
--
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.
