title: SQLAlchemy DELETE from many-to-many relationship
body: <p>(I am using SQLAlchemy, SQLite3, Flask-SQLAlchemy, Flask, &amp; Python)</p>
  
  <p>I am implementing a to-do list feed where a user can create a post (<code>class Post</code>) and attach tasks (<code>class Task</code>) 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:</p>
  
  <ul>
  <li>When a user deletes a task that has <strong>zero</strong> posts in it (<code>task.posts.count() == 0</code>) the delete from the database is successful</li>
  <li>When a user deletes a task that has <strong>one or more</strong> posts in it (<code>task.posts.count() &gt; 0</code>) the delete from the database throws an error. </li>
  </ul>
  
  <p>Here's the error:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Here's the Post &amp; Task Models &amp; tasks_posts Table:</p>
  
  <pre><code>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'))\
          )
  </code></pre>
  
  <p>Here's the view function:</p>
  
  <pre><code>@main.route('/edit-task/delete/&lt;int:id&gt;', 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))
  </code></pre>
  
  <p>I am assuming the issue is that I am incorrectly implementing:</p>
  
  <ul>
  <li>the 'cascade' feature of SQLAlchemy</li>
  <li>the many-to-many relationship</li>
  <li>or the view function </li>
  </ul>
  
  <p>Here's the stack trace:</p>
  
  <pre><code>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 &lt;lambda&gt;
      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.
  </code></pre>