CODEWITHSUNDEEP
mysqlpython-3.xsqlalchemy
Victor Mezrin
Victor Mezrin

Reputation: 2847

SQLAlchemy - delete grandchildren

I have 3 tables with relations one-to-many.

Table1 -one-to-many- Table2 -one-to-many- Table3

I need to delete all rows from Table3 that relate to a particular row in Table1

Row SQL request that works for me:

DELETE `Table3`
FROM `Table3`
JOIN `Table2` ON `Table2`.`ID` = `Table3`.`Table2_ID`
WHERE `Table2`.`Table1_ID` = 4567

Using SQLAlchemy I tried this:

table1Entity = ...(fetching entity 4567)
getSession().query(Table3). \
    join(Table2). \
    filter(Table2.table1 == table1Entity). \
    delete(synchronize_session='fetch')

For this try I've got exception:

sqlalchemy.exc.InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

I've made another try:

table1Entity = ...(fetching entity 4567)
table3IDs = getSession().query(Table3.id). \
    join(Table2). \
    filter(Table2.table1 == table1Entity).all()
getSession().query(Table3). \
    filter(Table3.id.in_(table3IDs)).\
    delete(synchronize_session='fetch')

For this I've got very strange exception (it cointains SELETC request, but should contain DELETE request):

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) Failed processing pyformat-parameters; 'MySQLConverter' object has no attribute '_result_to_mysql' [SQL: 'SELECT `Table3`.`ID` AS `Table3_ID` \nFROM `Table3` \nWHERE `Table3`.`ID` IN (%(ID_1)s)'] [parameters: {'ID_1': (14855,)}]

I have tried other combinations of requests, but was not successful. How to delete grandchildren?

Upvotes: 4

Views: 1466

Answers (1)

van
van

Reputation: 76992

With the model and relationships as defined below:

class Table1(Base):
    __tablename__ = 'table1'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    rel_table2 = relationship('Table2', backref='table1')


class Table2(Base):
    __tablename__ = 'table2'
    id = Column(Integer, primary_key=True)
    table1_id = Column(ForeignKey(Table1.id))
    name = Column(String)

    rel_table3 = relationship('Table3', backref='table2')


class Table3(Base):
    __tablename__ = 'table3'
    id = Column(Integer, primary_key=True)
    table2_id = Column(ForeignKey(Table2.id))
    name = Column(String)

Following two options should solve the problem:

version-1

filter_id = 4567
q = (session.query(Table3)
    .filter(Table3.table2.has(Table2.table1_id == filter_id))
    )
q.delete(synchronize_session=False)

produces:

DELETE FROM table3 
WHERE EXISTS (
    SELECT  1
    FROM    table2
    WHERE   table2.id = table3.table2_id 
        AND table2.table1_id = ?
)

version-2

filter_id = 4567
q = (session.query(Table3.id.label("t3_id"))
    .join(Table2)
    .filter(Table2.table1_id == filter_id)
    )
sq = q.subquery()
q = (session.query(Table3)
    .filter(Table3.id.in_(sq))
    )
q.delete(synchronize_session=False)

produces:

DELETE FROM table3 
WHERE  table3.id IN (
            SELECT  table3.id AS t3_id
            FROM    table3
            JOIN    table2 
                ON  table2.id = table3.table2_id
            WHERE   table2.table1_id = ?
        )

Upvotes: 4

Related Questions