CODEWITHSUNDEEP
pythonsqlalchemy
Houman
Houman

Reputation: 66320

SQLAlchemy seems to commit within unittests

def fixture(self, name):
        ...
        user = User(123)
        user.first_name = 'Houman'
        db.session.add(user)
        db.session.commit()
        return news_list, user

def test_update(self):
        with app.app_context():
            news_list, user = self.fixture('/topnews_id.json')
            user.first_name = 'John'
            user2 = db.session.query(User).get(user.id)

user2 displays the firstname == 'John', which doesn't make any sense as I haven't done a db.session.commit() I was expecting user2.first_name to be 'Houman'.

I believe I have to clear the app_context before trying to retrieve the user. Is this correct? How can this be achieved please?

Upvotes: 0

Views: 633

Answers (2)

exhuma
exhuma

Reputation: 21697

The reason for this is that you are using one session for both operations (the fixture and the unit-test). Sessions are meant to be short-lived and should represent one logical operation in your application. Here you're doing two.

As shackra's answer correctly states: SQLAlchemy tries to be intelligent and avoid unnecessary round-trips to the DB which could potentially be costly.

Each object in the session has an identity (the PK) and an "instance-state". This can be inspected using sqlalchemy.inspect(). A very interesting document to read is the State Management which goes into much more detail.

The crux here is that your user-object has the id 123 and sits in the session. When you run the query with .get() you explicitly state that you want to make use of the identity-map of the session. This is documented in Query.get() as (emphasis mine):

Query.get() is special in that it provides direct access to the identity map of the owning Session. If the given primary key identifier is present in the local identity map, the object is returned directly from this collection and no SQL is emitted, unless the object has been marked fully expired. If not present, a SELECT is performed in order to locate the object.

Another section of the docs rephrases this as follows:

[...] This means, if you say session.query(Foo).filter_by(name='bar'), even if Foo(name='bar') is right there, in the identity map, the session has no idea about that. It has to issue SQL to the database, get the rows back, and then when it sees the primary key in the row, then it can look in the local identity map and see that the object is already there. It’s only when you say query.get({some primary key}) that the Session doesn’t have to issue a query.

So the result you are seeing is in accordance with the docs.

You have two options to get the result you want:

  • Run Session.expire() on user before calling .get()
  • Use a real query, like db.session.query(User).filter_by(id=user.id).one() instead of .get()

Upvotes: 0

shackra
shackra

Reputation: 366

This is expected as SQLAlchemy will return any value stored on memory, SQLAlchemy is intelligent in that don't do a commit to the database every time you do something.

Upvotes: 2

Related Questions