sqlalchemy: why can't I update to func.now(), but can use 'now()'?

Question

...when I try to do a query that looks like this:

Session().query(MyMappedClass).update({MyMappedClass.time:func.now()})

I get:

InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter.

But if I do:

Session().query(MyMappedClass).update({MyMappedClass.time:'now()'})

...it works. Anyone know why?

Answer 1

this is explained in the documentation for update()

synchronize_session –

chooses the strategy to update the attributes on objects in the session. Valid values are:

False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, updated objects may still remain in the session with stale values on their attributes, which can lead to confusing results.

'fetch' - performs a select query before the update to find objects that are matched by the update query. The updated attributes are expired on matched objects.

'evaluate' - Evaluate the Query’s criteria in Python straight on the objects in the session. If evaluation of the criteria isn’t implemented, an exception is raised.

update() by default would like to refresh those objects cached in the Session without doing a database round trip. func.now() is a SQL function that requires this round trip to proceed. Sending in the string "now()" is not what you want, as this will set the value of the field to the string "now()", and will not actually use the SQL time function. You should instead pass synchronize_session=False to update().