Stack Exchange
Sqlalchemy mass deletion orm creation
How to write the express the following queries in sqlalchemy orm ?
DELETE FROM ACCESS_STRING_DETAILS WHERE MENU_ID IN(SELECT MENU_ID FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM TIME_DETAILS WHERE MENU_ID IN(SELECT MENU_ID FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM REDIRECTOR_INPUT_PARAMS WHERE COMMAND_ID IN(SELECT KEYWORD FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM REDIRECTOR_COMMANDS WHERE COMMAND_ID IN(SELECT KEYWORD FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM RANGE_DETAILS WHERE COMMAND_ID IN(SELECT KEYWORD FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM CLIENT_RSP_DETAILS WHERE KEYWORD IN(SELECT KEYWORD FROM REDIRECTOR WHERE SERVICE=122)
DELETE FROM REDIRECTOR WHERE SERVICE=122

What i basically need is to delete all the records from table which matches MENU_ID and KEYWORD for a particular SERVICE in REDIRECTOR  table

Do you already have the tables modeled in SQLAlchemy metadata?

@MartijnPieters :  Yes the tables are modeled , i donno what is the equvalent of IN statement in sqlalchemy ..

You can capture the filter on SERVICE=122 separately first; I am assuming you have models for all the tables that match the table name, using camelcase.
The menu_id and keyword selects are also stored for reuse:
service_filter = Redirector.service == 122
menu_id_select = session.query(Redirector.menu_id).filter(service_filter)
keyword_select = session.query(Redirector.keyword).filter(service_filter)

Now you can apply these selects for all your deletion queries:
(session
    .query(AccessStringDetails)
    .filter(AccessStringDetails.menu_id.in_(menu_id_select))
    .delete(False))
(session
    .query(TimeDetails)
    .filter(TimeDetails.menu_id.in_(menu_id_select))
    .delete(False))
(session
    .query(RedirectorInputParams)
    .filter(RedirectorInputParams.command_id.in_(keyword_select))
    .delete(False))
# etc.

session.query(Redirector).filter(service_filter).delete(False)

These examples are using the Column.in_ method with the select statements, turning the latter into subselects, to produce queries like:
DELETE FROM access_string_details WHERE access_string_details.menu_id IN (SELECT redirector.menu_id AS redirector_menu_id 
FROM redirector 
WHERE redirector.service = :service_1)

with the :service_1 parameter set to 122.

@Martijin Pieters : delete(False) , y this false is passed to delete ?

@AkhilThayyil: See http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.delete; False means no cleaning up of the session object cache. Other options are 'fetch' and 'evaluate'. Only needed if any of the deleted objects might be cached in the session and you need to do more in this transaction after the deletion.
