In my case, table 'tags' is editable by users, so I want to avoid them deleting a tag that's in use by any product. As it is now, they can delete the tag and the system just erases all traces of it from all products.
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Saturday, December 07, 2013 11:51 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Many2many, referential integrity and introspection On Dec 7, 2013, at 2:22 PM, Alexey Vihorev <viho...@gmail.com> wrote: Here is the full code: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import * from sqlalchemy.orm import relationship, sessionmaker Base = declarative_base() product_tags = Table( 'product_tags', Base.metadata, Column('product_id', Integer, ForeignKey('products.id')), Column('tag_id', Integer, ForeignKey('tags.id')) ) class Tag(Base): __tablename__ = 'tags' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) products = relationship("Product", secondary=product_tags) class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) name = Column(Unicode(20)) tags = relationship("Tag", secondary=product_tags) engine = create_engine(' <postgresql://user:password@localhost/example'> postgresql://user:password@localhost/example') Session = sessionmaker(bind=engine) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) p1 = Product(name = 'Milk') p2 = Product(name = 'Water') tag1 = Tag(name='Liquid') p1.tags.append(tag1) p2.tags.append(tag1) s = Session() s.add_all([p1, p2, tag1]) s.commit() s.close() s = Session() s.delete(s.query(Tag).first()) s.commit() s.close() After it runs, the product_tags table is completely empty. that's the right behavior, if you remove the Tag, then the row in "tags" is gone. It would be a referential integrity violation if rows remained in product_tags. if you want to remove rows from product_tags specifically, just alter the collections as needed: p1.tags.remove(tag1) this is a common misunderstanding so a documentation section exists for it: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#deleting-rows-f rom-the-many-to-many-table From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Jonathan Vanasco Sent: Friday, December 06, 2013 6:07 PM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Many2many, referential integrity and introspection can you share your existing schema for these relations ? -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/groups/opt_out> https://groups.google.com/groups/opt_out. -- 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/groups/opt_out> https://groups.google.com/groups/opt_out. -- 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/groups/opt_out.
