Updating an object in a for loop using SqlAlchemy, should this work in theory?

Question

So first I am fetching the rows:

q = session.query(products)

for p in q:
    p.someproperty = 23

    session.commit()

Should the above work in theory? Or is that the wrong pattern?

I am getting an error saying can't modify the property, which is strange so I figured I was doing something fundamentally wrong.

Answer 1

2 things:

Number one, you shouldn't have to commit() after every change. You should be able to:

for p in session.query (query):
    p.someproperty = somevalue
session.commit()

and number two, see this thread here: http://stackoverflow.com/questions/270879/efficiently-updating-database-using-sqlalchemy-orm. This gives another example of the syntax, and also the accepted answer suggests a better, more efficient way to perform this mass update.

Answer 2

You have to fetch the objects explicitly to be able to modify them; you have to iterate over session.query(query_string).all() . And, of course, a single commit after the loop is finished would be more efficient.