Commit changes for only one SQLAlchemy model instance when multiple have changed

Question

I queried and changed several instances. I only want to commit the changes to one of them. However, all the changes are committed when I call db.session.commit(). Is there a way to save an object individually, object.save(), like Rails or Django?

rule_1 = Rule.query.filter(Rule.something.like(that_thing))
rule_1.change_message = "Duplicate"

rule_2 = Rule.query.filter(Rule.something.like(that_thing))
rule_2.change_message = "This is 2nd Duplicate Message"

rule_3 = Rule.query.filter(Rule.something.like(that_thing))
rule_3.change_message = "This is the THIRD  Duplicate Message"

# What I want
rule_3.save() 

Answer 1

SQLAlchemy uses the unit of work pattern, while Django, Rails, and many other ORMs use the active record pattern. What this means is that everything that belongs to one session acts as one unit.

What your issue reveals is not an issue with SQLAlchemy, but an issue with your workflow. If you didn't want to change those values, you shouldn't have changed them. If you change something by mistake, expunge it from the session rather than leaving it around.

rule1.change_message = 'changed rule 1'
db.session.expunge(rule1)
# no longer part of the session, will not be committed
# use db.session.add(rule1) to track it again

---

If you really, actually, definitely need separate units of work (you most likely don't), create separate sessions and use them to query the separate instances.

Flask-SQLAlchemy uses one session per context, so all your queries place the instances in the same session. The query parameter uses this default session. You can create separate sessions by calling create_session. Make sure to clean these sessions up manually.

session1 = db.create_session({})
rule1 = session1.query(Rule).filter_by(name='rule1').one()
rule1.message = 'message'

session2 = db.create_session({})
rule2 = session2.query(Rule).filter_by(name='rule2').one()
rule2.message = 'message'
session2.commit()  # only commits rule2

session1.close()
session2.close()

Answer 2

Rule.query is just an alias to db.session.query(Rule), you are doing different request in same session the session will be commited as a whole. I'm not an expert but to do what you want you must create a session for every update needed.

This works, but I'm not sure if it is the best approach:

db = SQLAlchemy(app)
# new session created from your db instance with default parameters
new_session = db.create_session({})

rule_1 = Rule.query.filter(Rule.something.like(that_thing))
# or 
# rule_1 = db.session.query(Rule).filter(Rule.something.like(that_thing))
rule_1.change_message = "Duplicate"

rule_2 = new_session.query(Rule).filter(Rule.something.like(that_thing))
rule_2.change_message = "This is 2nd Duplicate Message"

# update rule_1
db.session.commit()

# update rule_2
new_session.commit()