mysql - SQLAlchemy 呈现的 SQL 中的语法错误

Question

我正在尝试在 SQLAlchemy 中复制此查询:

select date,
sum(case when country = 'USA' or country like '%merica' then dollars else 0 end),
sum(case when country  != 'USA' and country not like '%merica' then dollars else 0 end)
from Spend
group date;

所以，我有以下查询对象:

q = connection.session.query(Spend.date, \
        func.sum(func.case([ ( (Spend.country == 'USA') | (Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) ), \
        func.sum(func.case([ ( (Spend.country != 'USA') & (~Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) )) \
        .group_by(Spend.date)

当我运行此查询时，出现以下异常:

sqlalchemy.exc.ProgrammingError: (ProgrammingError)
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
\'))) AS sum_1, sum(case(((\\\\"Spend\\\\".country != :na\' at line 1')

因此，很明显，查询的呈现存在一些问题。但是，MySQL 不会记录具有无效语法的查询，当我在 python 中打印查询时，我只得到它:

SELECT Spend.date AS Spend_Date, sum(case(%s)) AS sum_1, sum(case(%s)) AS sum_2
FROM Spend GROUP BY Spend.date
([(<sqlalchemy.sql.expression.BooleanClauseList object at 0xff6a7dec>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xff86f1ac>)], [(<sqlalchemy.sql.expression.BooleanClauseList object at 0xff6a7fec>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xff86f1ac>)])

我的问题是双重的:我怎样才能看到呈现的查询是什么样的？ (不幸的是，这个答案 https://stackoverflow.com/a/5698357/125193 没有帮助，因为它无法呈现 BooleanClauseList)。另外，有人对我的查询可能有什么问题有任何提示吗？

谢谢!

更新

我能够对错误处理程序进行猴子修补，以更好地了解正在呈现的 SQL。

def wrap_handler(self, e, statement, parameters, cursor, context):
    # Note: MySQLdb-specific
    print(cursor._last_executed)
    self.__handle_dbapi_exception(e, statement, parameters, cursor, context)

import types
connection.session.connection().__handle_dbapi_exception = connection.session.connection()._handle_dbapi_exception
connection.session.connection()._handle_dbapi_exception = types.MethodType(wrap_handler, connection.session.connection())

这是 SQL:

SELECT `Spend`.date AS `Spend_date`,
sum(case((('\'\\"Spend\\".country = :name_1 OR \\"Spend\\".country LIKE :name_2\'', "'Spend.dollars'"),))) AS sum_1,
sum(case((('\'\\"Spend\\".country != :name_1 AND \\"Spend\\".country NOT LIKE :name_2\'', "'Spend.dollars'"),))) AS sum_2
FROM Spend
GROUP BY Spend.date

我仍然缺少实际参数，但很明显 SQL 是一团糟。我将重新阅读 func.case 的文档。

Answer 1

func 似乎有一些严重的黑魔法。您可以调用 func.anything_at_all，SQLAlchemy 会尝试将其转换为有效的 SQL。诀窍是单独导入 case，然后直接调用它。

from sqlalchemy import case, func
# ...
func.sum(case([ ( (Spend.country == 'USA') | (Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) )
# Notice that func.case is now just case

现在一切都按预期工作。