如何从 SQLAlchemy 中的多对多(辅助)表中删除记录? [英] How to delete records from many-to-many (secondary) table in SQLAlchemy?

问题描述

我在从包含 Post 和 Tag 列的 PostTag 表中删除记录时遇到问题.这是我的关系表:

tags = db.Table('PostTag',db.Column('Tag', db.Integer, db.ForeignKey('Tag.Id')),db.Column('Post', db.Integer, db.ForeignKey('Post.Id')))

和

tags = db.relationship(Tag, secondary=tags, backref=db.backref('Post', lazy='dynamic'))

当我这样做时:

from models.Post import Post帖子 = Post.query.join(Post.tags).filter(Post.Id == id).all()如果(帖子):回帖返回错误

然后

 用于 post[0].tags 中的 posttag:db.session.delete(posttag)db.session.flush()

多对多关系中的行被删除，但Tag表中的记录也被删除.

我只需要从PostTag表中删除某些条件的记录(例如Post=1)

我搜索了互联网，但没有找到任何结论.我不需要多对多关系的级联.

这是sql日志:

 297 Query DELETE FROM `PostTag` WHERE `PostTag`.`Tag` = 14 AND `PostTag`.`Post` = 3297 Query DELETE FROM `PostTag` WHERE `PostTag`.`Tag` = 14 AND `PostTag`.`Post` = 18第297回

最后一行 297 Query DELETE FROMTagWHERETag.Id=14 不应该在那里.

更新/也许解决方案

我解决了这个问题:

sql = 'DELETE FROM PostTag WHERE Post=%s'db.engine.execute(sql, post, ())

但这不仅仅是 ORM 方式.插入也一样.我将尝试以这种方式解决 ORM.如果我让这个问题消失，我会发布答案.

解决方案

试试这个:

post = db.session.query(Post).get(1)post.tags = []db.session.commit()

这里我们将集合 post.tags 重新定义为空数组并提交更改.为了解释这一点，我将参考 SQLAlchemy 文档::><块引用>

SQLAlchemy 中的集合是透明检测的.检测意味着对集合的正常操作进行跟踪，并导致在刷新时将更改写入数据库.

所以 SQLAlchemy 会跟踪我们对 post.tags 集合所做的更改，并在提交时更新它.

如果我们只有一个标签(比如 sometag)，我们可以像这样使用 remove 方法:

post = db.session.query(Post).get(1)post.tags.remove(sometag)db.session.commit()

I am having problems deleting records from PostTag table that contains Post and Tag column. This is my relation table:

tags = db.Table('PostTag',
    db.Column('Tag', db.Integer, db.ForeignKey('Tag.Id')),
    db.Column('Post', db.Integer, db.ForeignKey('Post.Id'))
)

and

tags = db.relationship(Tag, secondary=tags, backref=db.backref('Post', lazy='dynamic'))

When I do this:

from models.Post import Post

posts = Post.query.join(Post.tags).filter(Post.Id == id).all()
if(posts):
   return posts
return False

and then

for posttag in post[0].tags:
    db.session.delete(posttag)
    db.session.flush()

Rows from many-to-many relation are deleted but also records from Tag table.

I just need to delete records from PostTag table for some condition (Post=1 for example)

I searched the internet but I did not find anything conclusive. I do not need cascade on many-to-many relationship.

This is sql log:

 297 Query  DELETE FROM `PostTag` WHERE `PostTag`.`Tag` = 14 AND `PostTag`.`Post` = 3
 297 Query  DELETE FROM `PostTag` WHERE `PostTag`.`Tag` = 14 AND `PostTag`.`Post` = 18
 297 Query  DELETE FROM `Tag` WHERE `Tag`.`Id` = 14

Last row 297 Query DELETE FROMTagWHERETag.Id= 14 should not be there.

UPDATE / maybe solution

I kind of solved this with:

sql = 'DELETE FROM PostTag WHERE Post=%s'
db.engine.execute(sql, post, ())

But that is not just ORM way. Same goes for insert. I will try to get this resolved ORM way. I will post answer if I make this problem go away.

解决方案

Try this:

post = db.session.query(Post).get(1)
post.tags = []
db.session.commit()

Here we are redefining the collection post.tags to the empty array and committing the changes. To explain this I'll refer to the SQLAlchemy docs:

Collections in SQLAlchemy are transparently instrumented. Instrumentation means that normal operations on the collection are tracked and result in changes being written to the database at flush time.

So SQLAlchemy keeps track of the changes we make to the collection post.tags and updates it on commit.

If we had only one tag (say sometag) we could use the remove method like this:

post = db.session.query(Post).get(1)
post.tags.remove(sometag)
db.session.commit()

这篇关于如何从 SQLAlchemy 中的多对多(辅助)表中删除记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！