Can't delete row from SQLAlchemy due to wrong session

Question

I am trying to delete an entry from my table. This is my code for the delete function.

@app.route("/delete_link/<link_id>", methods=['GET', 'POST'])
def delete_link(link_id):
    link = models.Link.query.filter(models.Link.l_id == link_id).first()
    db.session.delete(link)
    db.session.commit()
    return flask.redirect(flask.url_for('links'))

the line: db.session.delete(link) returns me this error:

InvalidRequestError: Object '' is already attached to session '1' (this is '2')

I've tried this code as well:

@app.route("/delete_link/<link_id>", methods=['GET', 'POST'])
def delete_link(link_id):
    link = models.Link.query.filter(models.Link.l_id == link_id)
    link.delete()
    db.session.commit()
    return flask.redirect(flask.url_for('links'))

which does not update the database. Link must not be in the session I guess, but I don't know how to check that, and how to fix it. I am new to sqlalchemy.

EDIT:

I use this to create my db variable which probably creates the session at this stage (this is at the top of the code). It comes from the flask documentation

from yourapplication import db

Answer 1

You are creating 2 instances of the db object, inherently creating 2 different sessions.

In models.py:

...
5.  from config import app
6.
7.  db = SQLAlchemy(app)

In erika.py:

...
16. from config import app
    ...
23. db = SQLAlchemy(app)

then when you try to delete the element:

link = models.Link.query.filter(models.Link.l_id == link_id).first()
db.session.delete(link)
db.session.commit()

the following happens:

• models.Link.query uses the database session created by models.py to get the record.
• db.session.delete uses the session created by erika.py.

• link is attached to the models.py session and you can't use another session (erikas.py) to delete it. Hence:

  InvalidRequestError: Object '' is already attached to session '1' (this is '2')

Solution

The solution it's simple. Have only one instance of a db object at any time and reuse that instance whenever you need db operations.

erika.py

from models import db

This way you are always using the same session that was used to fetch your records.

Answer 2

It appears to be a similar problem to the one described at http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-xvi-debugging-testing-and-profiling It's a good in-depth description of the problem and how he solved it. The author of that article made a fix that's available as a fork.

The Fix

To address this problem we need to find an alternative way of attaching Flask-WhooshAlchemy's query object to the model.

The documentation for Flask-SQLAlchemy mentions there is a model.query_class attribute that contains the class to use for queries. This is actually a much cleaner way to make Flask-SQLAlchemy use a custom query class than what Flask-WhooshAlchemy is doing. If we configure Flask-SQLAlchemy to create queries using the Whoosh enabled query class (which is already a subclass of Flask-SQLAlchemy's BaseQuery), then we should have the same result as before, but without the bug.

I have created a fork of the Flask-WhooshAlchemy project on github where I have implemented these changes. If you want to see the changes you can see the github diff for my commit, or you can also download the fixed extension and install it in place of your original flask_whooshalchemy.py file.