SQLAlchemy still able to get object from session after deletion

Question

I have an endpoint to delete an object from my database. I delete it with the following code:

my_object = Object.query.get(id)
db.session.delete(my_object)
db.session.commit()
return json.dumps({'success': True}

I have an API test to test the endpoint where I create an object and then use the endpoint to delete it. I am trying to assert after the deletion happens it isn't in the database.

my_object = MyObject()
db.session.add(my_object)
db.session.commit()
response = requests.delete('{}/my-object/{}'.format(
    os.environ.get('MY_URL'),
    my_object.id
))
self.assertTrue(response.json()['success']) // this passes
self.assertEqual(200, response.status_code) // this passes
result = db.session.query(MyObject).get(my_object.id)
print(result.id) // prints the id of the job even though it is deleted from the database

I think this is related to some SQLAlchemy session caching. I have tried db.session.flush(), db.session.expire_all() to no avail. The object is actually being deleted from the database. So I would expect the query result to be None.

I see this in the docs but haven't full wrapped my head around it. when to commit and close a session

Thanks for any help.

Answer 1

So in your test code, you add the object to a session and commit it. It gets saved to the db, and is your session's identity map.

Then you hit your app, it has it's own session. It deletes the object and commits, now it's gone from the db. But...

Your previous session doesn't know anything about this, and when you use a .get(), it will give back what's in its identity map: a Python object with an ID. It won't refetch unless you close the session or force a refresh from the DB (I can't remember OTOH how to do this, but you can, it's in the docs somewhere). If you used a clean third session, it would have a fresh identity map and would not be holding onto a reference to the python object so you'd get what you expect, ie. no result. This is by design because the Identity Map allows SQLAlchemy to chain a bunch of changes together into one optimal SQL query that is only fired when you commit.

So yeah, you're seeing the fetch from the Identity Map which is still alive. (You can even pop it open in the interactive interpreter and poke around) And it makes sense, because say you have two threads of different web requests and one is part way doing some longer lived stuff with an object when another request deletes the object. The first thread shouldn't barf on the Python code working with the object, because that would just trigger random exceptions wherever you were in the code. It should just think that it can do its thing, and then fail on commit, triggering a rollback.

HTH

Answer 2

db.session.expunge_all()

"Remove all object instances from this Session..."

http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.expunge_all

Or simple trigger after each request db.session.remove()

For example in Flask with SQLAlchemy scoped_session:

@app.teardown_appcontext
def shutdown_session(exception=None):
   db.session.remove()

"The scoped_session.remove() method, as always, removes the current Session associated with the thread, if any. However, one advantage of the threading.local() object is that if the application thread itself ends, the “storage” for that thread is also garbage collected. So it is in fact “safe” to use thread local scope with an application that spawns and tears down threads, without the need to call scoped_session.remove(). However, the scope of transactions themselves, i.e. ending them via Session.commit() or Session.rollback(), will usually still be something that must be explicitly arranged for at the appropriate time, unless the application actually ties the lifespan of a thread to the lifespan of a transaction."

http://docs.sqlalchemy.org/en/latest/orm/contextual.html#thread-local-scope http://docs.sqlalchemy.org/en/latest/orm/contextual.html#using-thread-local-scope-with-web-applications