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+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.
