How to remove records from a many-to-many table (secondary) in SQLAlchemy?

Question

I'm having trouble deleting records from a PostTag table containing Post and Tag columns. This is my relationship 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 relationships are deleted, as well as entries from the Tag table.

I just need to delete entries from the PostTag table for some condition (e.g. Post=1 )

I searched the Internet, but I did not find anything convincing. I don't need a cascade for many-to-many relationships.

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 line 297 Query DELETE FROM Tag WHERE Tag . Id = 14 should not be there.

UPDATE / possible solution

I kind of solved this:

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

But this is not just an ORM path. The same goes for insertion. I will try to get this allowed ORM path. I will send an answer if I miss this problem.

Answer 1

Try the following:

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

Here we redefine the post.tags collection into an empty array and post.tags changes. To explain this, I will refer to SQLAlchemy docs :

Collections in SQLAlchemy are transparently equipped with tools. The toolkit means that normal operations with the collection are tracked and cause changes to be written to the database at different times.

So, SQLAlchemy tracks the changes we make to the post.tags collection and updates it when committed.

If we had only one tag (say sometag ), we could use the remove method as follows:

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

Answer 2

Try to remove objects from the collection:

 post[0].tags.clear() 

You can also delete individual items:

 post[0].tags.remove(posttag)