CODEWITHSUNDEEP
pythonsqlsqlalchemy
Akhil Thayyil
Akhil Thayyil

Reputation: 9413

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

Upvotes: 0

Views: 134

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123480

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.

Upvotes: 1

Related Questions