SQLAlchemy: ProgrammingError，但是打印出来的SQL语句有效吗？

Question

我正在尝试使用SQLAlchemy对每个月在表中找到的行数进行分组和计数。

其职能如下：

def query_testing(
    session: scoped_session,
    from_date: datetime,
    to_date: datetime,
):
    qry = (
        session.query(
            func.count(U4.index).label("count"),
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            ).label("received"),
        )
        .filter(
            cast(U4.arrival, Date) >= from_date,
            cast(U4.arrival, Date) <= to_date,
        )
        .group_by(
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            ),
        )
        .order_by(
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            )
        )
    )

同时，使用qry.all()执行查询会导致ProgrammingError：

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'u4erp.received' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

当我print(qry)语句并尝试手动执行生成的SQL时，该语句可以工作(在手动将?更改为其预期值之后)。打印的声明如下：

SELECT
    count(u4erp.[index]) AS count,
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0) AS received
FROM
    u4erp
WHERE
    CAST(
        u4erp.received AS DATE
    ) >= '2020-01-01'
    AND CAST(
        u4erp.received AS DATE
    ) <= '2021-12-31'
GROUP BY
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0)
ORDER BY
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0)

是什么原因导致打印的SQL语句工作，但在使用ProgrammingError时引发SQLAlchemy？我该怎么解决呢？

Answer 1

我使用text为我的特定用途找到了一个解决方案。

def query_testing(
    session: scoped_session,
    from_date: datetime,
    to_date: datetime,
):
    qry = (
        session.query(
            func.count(U4.index).label("count"),
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0) AS received"
            ),
        )
        .filter(
            cast(U4.arrival, Date) >= from_date,
            cast(U4.arrival, Date) <= to_date,
        )
        .group_by(
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0)"
            ),
        )
        .order_by(
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0)"
            ),
        )
    )