python - Flask SQLAlchemy Many-to-Many StaleDataError -

i'm using flask-sqlalchemy. have 2 classes share relationship through association table. when try delete row in 1 of tables. see following error:

sqlalchemy.orm.exc.staledataerror staledataerror: delete statement on table 'tags' expected delete 1 row(s); 2 matched. 

here models:

tags = db.table('tags',     db.column('tag_id', db.integer, db.foreignkey('tag.id')),     db.column('post_id', db.integer, db.foreignkey('posts.id')) )  class post(db.model):     __tablename__ = 'posts'     id = db.column(db.integer, primary_key=true)     title = db.column(db.string(255))     tags = db.relationship('tag', secondary=tags,                        backref=db.backref('posts', lazy='dynamic'))      def __init__(self, **kwargs):         super(post, self).__init__(**kwargs)      def __repr__(self):         return '<title %r>' % self.title   class tag(db.model):     __tablename__ = 'tag'     id = db.column(db.integer, primary_key=true)     name = db.column(db.string(120), unique=true) 

an here code run delete:

def remove_tag(tag_id):     tag = tag.query.get(tag_id)     post in tag.posts:         p = post.query.get(post.id)         p.tags.remove(tag) db.session.delete(tag) db.session.commit() 

i have tried striped-down version of code, outside of app, in standalone test, db , environment. and, works expected removing tag , associated table row.

what know, if possible:

• how did in situation of staledataerror ?
• why match more 1 row, when query .all() doesn't show multiple uses?
• how can prevent happening?

thanks in advance assistance.

best, edward

had epiphany!

how did in situation of staledataerror ?

when testing edit post page did many post of same post database.

why match more 1 row, when query .all() doesn't show multiple uses?

my testing created multiple entries in association table. in other words had (tag.id= 1, post.id=1) multiple times.

how can prevent happening?

add unique constraint association table stop multiple entries db. added checks in view stop multiple entries.

tags = db.table(     'tags',     db.column('tag_id', db.integer, db.foreignkey('tag.id')),     db.column('post_id', db.integer, db.foreignkey('posts.id')),     db.uniqueconstraint('tag_id', 'post_id', name='uc_tag_id_post_id') ) 

migrate db.

if using sqlite , alembic encounter error: "no support alter of constraints in sqlite dialect". more on issue can found here.

in short, if development database easier delete db. if in production environment, perhaps should consider data migration , switch engine.