jeudi 21 janvier 2016

SQLAlchemy DELETE from many-to-many relationship

(I am using SQLAlchemy, SQLite3, Flask-SQLAlchemy, Flask, & Python)

I am implementing a to-do list feed where a user can crete a post (class Post) and attach tasks (class Task) to each post. Each task can have many posts. Each post can have many tasks. I am having issues with SQLAlchemy and deleting from a table. Here's what is interesting:

  • When a user deletes a task that has zero posts in it (task.posts.count() == 0) the delete from the database is successful
  • When a user deletes a task that has one or more posts in it (task.posts.count() > 0) the delete from the database throws an error.

Here's the error:

sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. 
To begin a new transaction with this Session, first issue Session.rollback().
Original exception was: DELETE statement on table 'tasks_posts' expected to delete 1 row(s); Only 0 were matched.

Here's the Post & Task Models & tasks_posts Table:

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)
    tasks = db.relationship('Task', secondary='tasks_posts', \
            backref=db.backref('post', lazy='joined'), \
            lazy='dynamic', cascade='all, delete-orphan', \
            single_parent=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

class Task(db.Model):
    __tablename__ = 'tasks'
    id = db.Column(db.Integer, primary_key=True) 
    title = db.Column(db.String(24))
    description = db.Column(db.String(64))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    posts = db.relationship('Post', secondary='tasks_posts', \
            backref=db.backref('task', lazy='joined'), \
            lazy='dynamic', cascade='all, delete-orphan', \
            single_parent=True)

tasks_posts = db.Table('tasks_posts',\
        db.Column('task_id', db.Integer, db.ForeignKey('tasks.id')),\
        db.Column('post_id', db.Integer, db.ForeignKey('posts.id'))\
        )

Here's the view function:

@main.route('/edit-task/delete/<int:id>', methods=['GET', 'POST'])
def delete_task(id):
    task = Task.query.get_or_404(id)
    db.session.delete(task)
    db.session.commit()
    return redirect(url_for('.user', username=current_user.username))

I am assuming the issue is that I am incorrectly implementing:

  • the 'cascade' feature of SQLAlchemy
  • the many-to-many relationship
  • or the view function

Aucun commentaire:

Enregistrer un commentaire