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_?

Kane Blueriver · Accepted Answer · 2015-11-14 12:41:25Z

35

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.

answered Nov 14, 2015 at 12:41

Kane Blueriver

4,2984 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Larry Martell Over a year ago

Thank you so much. This works. But can you please tell me why this code also works without the synchronize_session

session.query(ApplicationInstallation).filter_by(control_hub_id=hub_id).filter(ApplicationInstallation.removal_date == None).update({'removal_date': utc_now})

Kane Blueriver Over a year ago

@LarryMartell When you use in_ whether distinctly or not, a select query is necessary for the MetaData to decide which of its in-memory objects to expire. You can see the select query if echo mode is activated, but I don't know why it's designed like this.

Son Over a year ago

Can anyone explain "If evaluation of the criteria isn’t implemented, an exception is raised" please? The fetch is quite straight forward but the evaluate one seems a bit mystery to me ...

Collectives™ on Stack Overflow

Using sqlalchemy result set for update

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related