How does SQLAlchemy track database changes?

Question

I wonder how SQLAlchemy tracks changes that are made outside of SQLAlchemy (manual change for example)?

Until now, I used to put db.session.commit() before each value that can be changed outside of SQLAlchemy. Is this a bad practice? If yes, is there a better way to make sure I'll have the latest value? I've actually created a small script below to check that and apparently, SQLAlchemy can detect external changes without db.session.commit() being called each time.

Thanks,

P.S: I really want to understand how all the magics happen behind SQLAlchemy work. Does anyone has a pointer to some docs explaining the behind-the-scenes work of SQLAlchemy?

import os

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Use SQLlite so this example can be run anywhere.
# On Mysql, the same behaviour is observed
basedir = os.path.abspath(os.path.dirname(__file__))
db_path = os.path.join(basedir, "app.db")
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///' + db_path
db = SQLAlchemy(app)


# A small class to use in the test
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))


# Create all the tables and a fake data
db.create_all()
user = User(name="old name")
db.session.add(user)
db.session.commit()


@app.route('/')
def index():
    """The scenario: the first request returns "old name" as expected.
    Then, I modify the name of User:1 to "new name" directly on the database.
    On the next request, "new name" will be returned.
    My question is: how SQLAlchemy knows that the value has been changed?
    """

    # Before, I always use db.session.commit() 
    # to make sure that the latest value is fetched.
    # Without db.session.commit(), 
    # SQLAlchemy still can track change made on User.name
    # print "refresh db"
    # db.session.commit()

    u = User.query.filter_by(id=1).first()
    return u.name


app.run(debug=True)

score 8 · Accepted Answer · edited May 23 '17 at 12:18

The "cache" of a session is a dict in its identity_map (session.identity_map.dict) that only caches objects for the time of "a single business transaction" , as answered here https://stackoverflow.com/a/5869795.

For different server requests, you have different identity_map. It is not a shared object.

In your scenario, you requested the server 2 separated times. The second time, the identity_map is a new one (you can easily check it by printing out its pointer), and has nothing in cache. Consequently the session will request the database and get you the updated answer. It does not "track change" as you might think.

So, to your question, you don't need to do session.commit() before a query if you have not done a query for the same object in the same server request.

Hope it helps.

"Records are kept in the identity map for a single business transaction. This means that no matter how your web server is configured, you probably will not hold them for longer than a request (or store them in the session)." quoted from http://stackoverflow.com/questions/5869514/sqlalchemy-identity-map-question/5869795#5869795 — Son, Nov 23 '15 at 09:52

How does SQLAlchemy track database changes?

1 Answers1