On 6/13/15 10:09 AM, SC wrote:
Hi
I'm struggling with an SQLAlchemy issue.
I have a one to many relationship like so:
|
users_tags =Table(
"users_tags",Base.metadata,
Column("user_id",Integer,ForeignKey("users.id")),
Column("tag_id",Integer,ForeignKey("tags.id"))
)
classTag(Base):
__tablename__ ="tags"
id =Column(Integer(),primary_key=True)
name =Column(String(255),nullable=False,unique=True)
classUser(Base):
__tablename__ ="users"
id =Column(Integer(),primary_key=True)
name =Column(String(255),nullable=False)
tags =relationship("Tag",secondary=users_tags,cascade="all, delete",
single_parent=True,backref=backref("user",cascade="all"),
lazy="subquery")
|
If I do the following:
|
tag =Tag()
tag.name ="C"
user =User()
user.name ="A"
user.tags.append(tag)
session.add(user)
user2 =User()
user2.name ="B"
user2.tags.append(tag)
session.add(user2)
session.commit()
|
I have two rows in users, two in users_tags and 1 in tags - as expected.
that's not possible. You have single_parent=True on User.tags. If I
run your program as is, I get:
2015-06-14 09:49:19,715 INFO sqlalchemy.engine.base.Engine COMMIT
Traceback (most recent call last):
File "test.py", line 44, in <module>
user2.tags.append(tag)
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/collections.py", line
1072, in append
item = __set(self, item, _sa_initiator)
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/collections.py", line
1044, in __set
item = executor.fire_append_event(item, _sa_initiator)
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/collections.py", line
716, in fire_append_event
item, initiator)
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
936, in fire_append_event
initiator or self._append_token or self._init_append_token())
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line
1594, in append
return _do_check(state, value, None, initiator)
File
"/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line
1589, in _do_check
(orm_util.instance_str(value), state.class_, prop)
sqlalchemy.exc.InvalidRequestError: Instance <Tag at 0x1030b9410> is
already associated with an instance of <class '__main__.User'> via its
User.tags attribute, and is only allowed a single parent.
I then do:
|
userdel =session.query(User).filter_by(name="A").first()
session.delete(userdel)
session.commit()
|
And I end up with 3 empty tables in my DB. I want to still have 1
entry in users, 1 in users_tags and 1 in tags.
lose the "cascade='delete'" and the "single_parent". Traditional
many-to-many doesn't need any of these directives. See
http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html#many-to-many
for an example.
The problem clearly comes from trying to maintain uniqueness within
the tags table. The cascading delete is removing the single entry
from the tags table which in turn is deleting the other user who is
using that tag.
How can I achieve what I want without having to have duplicate entries
in the tags table?
I get the impression maybe you want a Tag to delete itself once no User
points to it, otherwise I'm not sure what duplicate entries has to do
with deletions. There's a recipe for that here:
http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#9264556
Thanks in advance!
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
