SQLAlchemy DELETE from many-to-many relationship

Question

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

I am implementing a to-do list feed where a user can create 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

Here's the stack trace:

File "...venv/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1473, in full_dispatch_request
    rv = self.preprocess_request()
  File ".../venv/lib/python2.7/site-packages/flask/app.py", line 1666, in preprocess_request
    rv = func()
  File ".../app/auth/views.py", line 12, in before_request
    if current_user.is_authenticated:
  File ".../venv/lib/python2.7/site-packages/werkzeug/local.py", line 342, in __getattr__
    return getattr(self._get_current_object(), name)
  File ".../venv/lib/python2.7/site-packages/werkzeug/local.py", line 301, in _get_current_object
    return self.__local()
  File ".../venv/lib/python2.7/site-packages/flask_login.py", line 47, in <lambda>
    current_user = LocalProxy(lambda: _get_user())
  File ".../venv/lib/python2.7/site-packages/flask_login.py", line 858, in _get_user
    current_app.login_manager._load_user()
  File ".../venv/lib/python2.7/site-packages/flask_login.py", line 389, in _load_user
    return self.reload_user()
  File ".../venv/lib/python2.7/site-packages/flask_login.py", line 351, in reload_user
    user = self.user_callback(user_id)
  File ".../app/models.py", line 235, in load_user
    return User.query.get(int(user_id))
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 829, in get
    return self._get_impl(ident, loading.load_on_ident)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 853, in _get_impl
    self.session, key, attributes.PASSIVE_OFF)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 152, in get_from_identity
    state._load_expired(state, passive)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 474, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 664, in load_scalar_attributes
    only_load_props=attribute_names)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 219, in load_on_ident
    return q.one()
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2528, in one
    ret = list(self)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2571, in __iter__
    return self._execute_and_instances(context)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2584, in _execute_and_instances
    close_with_result=True)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2575, in _connection_from_session
    **kw)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 893, in connection
    execution_options=execution_options)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 898, in _connection_for_bind
    engine, execution_options)
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 313, in _connection_for_bind
    self._assert_active()
  File ".../venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 214, in _assert_active
    % self._rollback_exception
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.

Solution 1

Ok, so I think there are a few things going on here that might be causing your issue. The first thing is the error message itself. This is implying that the database thinks it should be deleting something but it's not there. I believe this is caused by your delete-all orphan and single_parent=True.

This is telling sqlalchemy that both Post and Task have a single_parent which is confusing! So what I believe you need to do to get this to work is

1. Define the relationship on only one model. The way you have it setup now with both classes defining the relationship is convulting your code. I would suggest something like this:

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'))

2. Figure out how you want the data model to work. Any task can be in any post and any post can have any number of tasks? I think that you should maybe rethink the data model by having Post own Task. You can still share Tasks in different boths, but you need a clear understanding of the data model going forward.

3. Be explicit about what you are deleting. I know that it might make sense to you that every post that a task is in should be deleted when a task is deleted, but to me that doesn't make sense. Loop through the correct posts and tasks to be deleted. This way you will have a better understanding of the deletion and cleaner code.

Update:

From the documentation:

There are several possibilities here:

• If there is a relationship() from Parent to Child, but there is not a reverse-relationship that links a particular Child to each Parent, SQLAlchemy will not have any awareness that when deleting this particular Child object, it needs to maintain the “secondary” table that links it to the Parent. No delete of the “secondary” table will occur.

• If there is a relationship that links a particular Child to each Parent, suppose it’s called Child.parents, SQLAlchemy by default will load in the Child.parents collection to locate all Parent objects, and remove each row from the “secondary” table which establishes this link. Note that this relationship does not need to be bidrectional; SQLAlchemy is strictly looking at every relationship() associated with the Child object being deleted.

• A higher performing option here is to use ON DELETE CASCADE directives with the foreign keys used by the database. Assuming the database supports this feature, the database itself can be made to automatically delete rows in the “secondary” table as referencing rows in “child” are deleted. SQLAlchemy can be instructed to forego actively loading in the Child.parents collection in this case using the passive_deletes directive on relationship(); see Using Passive Deletes for more details on this. Note again, these behaviors are only relevant to the secondary option used with relationship(). If dealing with association tables that are mapped explicitly and are not present in the secondary option of a relevant relationship(), cascade rules can be used instead to automatically delete entities in reaction to a related entity being deleted - see Cascades for information on this feature.

Solution 2

Thanks to everyones help I seem to have figured it out. The idea that I was trying to achieve is a single post can have zero to many tasks in it (a user can complete multiple tasks at a time). The user can view all the posts in a single task. If a user decides to delete a task, the posts in that task remain untouched.

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

class Task(db.Model):
    __tablename__ = 'tasks'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(24))
    description = db.Column(String(64))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))

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'))
        )