Sqlalchemy delete subquery

Question

I am trying to delete some child rows using a filtered query without result:

sl = DBSession.query(Puesto.id).filter(Puesto.locales_id == id).subquery()
DBSession.query(Servicio).filter(Servicio.puestos_id.in_(sl)).delete()

I am getting InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter. as error.

Full stack trace:

Traceback (most recent call last):
  File "/usr/src/tg2env/ceaf/ceaf/controllers/root.py", line 1673, in delete_local
    DBSession.query(Servicio).filter(Servicio.puestos_id.in_(sl)).delete()
  File "/usr/src/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.6.6-py2.4.egg/sqlalchemy/orm/query.py", line 2126, in delete
    raise sa_exc.InvalidRequestError(
InvalidRequestError: Could not evaluate current criteria in Python.  Specify 'fetch' or False for the synchronize_session parameter.

I am not be able to find where the problem is...

Any idea?

Regards

wberry · Accepted Answer · 2011-10-31 13:50:47Z

101

After looking in the source where your exception occurs I suggest trying this:

sl = DBSession.query(Puesto.id).filter(Puesto.locales_id == id).subquery()
DBSession.query(Servicio).filter(Servicio.puestos_id.in_(sl)) \
.delete(synchronize_session='fetch')

See the documentation of the delete method for what this means. Passing the fetch argument will basically run the query twice, once as a select and once as a delete.

If running two queries is not desired, pass synchronize_session=False instead and then call session.expire_all() immediately after the delete to avoid having inconsistent state within the MetaData store.

answered Oct 31, 2011 at 13:50

wberry

19.5k9 gold badges59 silver badges89 bronze badges

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

6 Comments

LooPer Over a year ago

First approach works great. Why now working with synchronize_session='evaluate'?

wberry Over a year ago

Because the delete uses in_ with a subquery, a select query is necessary for the MetaData to decide which of its in-memory objects to expire. Passing this option gives it permission to do that. Doing this unconditionally would risk poor performance in certain situations.

Shafiul Over a year ago

This doc also says if we commit immediately, we do not need to call session.expire_all(), with autocommit=False.

wberry Over a year ago

@Shafiul Are you sure? I read it as, you do not need to call expire_all if this deletion occurs just after a commit, not just before. If you have done other things before this deletion in the same DB transaction, there could be objects in the session that no longer have any corresponding row in the tables!

Hosein Aqajani Over a year ago

what is DBSession and session exactly ?

|

Collectives™ on Stack Overflow

Sqlalchemy delete subquery

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related