Python - SQLAlchemy 子查询

Python - SQLAlchemy 子查询

Max.Bai

2020-08

1. 子查询需要先执行subquery

2. 子查询的列需要通过c来访问  比如： sub_query.c.alarm

    base_sub_query = (
        db.query(
            AlarmRule.alarm,
            AlarmRule.condition,
            AlarmRule.compare_float
        )
        .filter(
            AlarmRule.alarm == Alarm.id,
            Alarm.module == MonitorModule.id,
            AlarmRule.is_valid == 1,
            AlarmRule.enable == 1,
        )
        .order_by(
            AlarmRule.alarm,
            AlarmRule.condition,
        )
        .having(1 == 1)
    )


    sub_query = (
        base_sub_query.filter(AlarmRule.condition == "LT")
        .order_by(AlarmRule.compare_float)
        .subquery()
    )

    query = db.query(sub_query).group_by(
        sub_query.c.alarm,
        sub_query.c.condition,
    )
    
    result = [d._asdict() for d in query.all()]