Unable to delete row from SQLAlchemy due to invalid session

Question

I am trying to delete a record 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')) 

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

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

I tried this code too:

 @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. The link should not be in the session, I think, but I do not know how to check it and how to fix it. I am new to sqlalchemy.

EDIT

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

 from yourapplication import db 

Answer 1

You create 2 instances of the db object, initially 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 trying to delete an item:

 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 retrieve the record.
• db.session.delete uses the session created by erika.py.

• link attaches to the models.py session, and you cannot use another session (erikas.py) to delete it. Hence:

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

Decision

The solution is simple. Have only one instance of the db object at any time and reuse that instance whenever you need db operations.

erika.py

 from models import db 

This way you always use the same session that was used to retrieve your records.

Answer 2

This is similar to the problem described at http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-xvi-debugging-testing-and-profiling This is a good detailed description of the problem and how he solved it . The author of this article made a fix available as a plug.

Correction

To solve this problem, we need to find an alternative way to bind the Flask-WhooshAlchemy request object to the model.

The documentation for Flask-SQLAlchemy mentions the model.query_class attribute, which contains the class to use for queries. This is actually a much cleaner way to force Flask-SQLAlchemy to use a custom query class than Flask-WhooshAlchemy does. If we configure Flask-SQLAlchemy to create queries using the Whoosh query class, which is already a subclass of Flask-SQLAlchemy BaseQuery, then we should have the same result as before, but without an error.

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