Using sqlalchemy result set for update

Question

Really 2 questions here.

If I run a sqlalchemy query like this:

sensors = session.query(Sensor).filter(Sensor.serial_number.in_(data['sensor_serial_numbers'])).all()

Can I then use the result set (sensors) to update a column in all those rows? I couldn't figure out the syntax for that, so I tried doing this:

session.query(Sensor).filter(Sensor.serial_number.in_(data['sensor_serial_numbers'])).update({'system_id': system.id})

But that fails way down in the bowls of sqlalchemy:

  File "/home/ecovent/pyenv0.3/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 949, in _do_pre_synchronize
    "Could not evaluate current criteria in Python. "
InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter.

I think that must be because of the in_ clause, as I've done updates before using that same construct, but they did not have an in_ clause. How would I do an update like this with an in_?

Answer 1

Try this:

session.query(Sensor)\
    .filter(Sensor.serial_number.in_(data['sensor_serial_numbers']))\
    .update({'system_id': system.id}, synchronize_session='fetch')

It's documented here: doc

The default value is evaluate which

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.