mysql - SQLAlchemy - 删除孙子

Question

我有 3 个具有一对多关系的表。

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

我需要从 Table3 中删除与 Table1 中特定行相关的所有行

适合我的行 SQL 请求:

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

使用 SQLAlchemy 我试过这个:

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

对于这次尝试我有异常(exception):

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

我又试了一次:

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')

为此我有一个非常奇怪的异常(它包含 SELETC 请求，但应该包含 DELETE 请求):

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,)}]

我尝试过其他请求组合，但没有成功。如何删除孙子？

Answer 1

使用如下定义的模型和关系:

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)

以下两个选项应该可以解决问题:

版本-1

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

产生:

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

版本 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)

产生:

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 = ?
        )