SQLAlchemy， 如何创建上下文感知的自定义编译

Question

我希望能够以不同的方式将SQLAlchemy查询编译为SQL查询;如何以不同于count的方式编译不同的计数？

当我选择计数时

sql.select([sql.func.count(c1)]).select_from(t1)

结果应该是

select count(c1) from t1

但是当我有不同的计数时，例如

sql.select([sql.func.count(c1.distinct())]).select_from(t1)

结果应该是

select count(1) from t1 ; 

Answer 1

为了匹配COUNT(DISTINCT ...)你需要覆盖编译Function：

import copy
from sqlalchemy import func
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import operators
from sqlalchemy.sql.expression import literal, Function, UnaryExpression
@compiles(Function)
def visit_function(f, compiler, **kw):
clauses = f.clauses.clauses
if f.name.lower() == 'count' 
and len(clauses) == 1 
and isinstance(clauses[0], UnaryExpression) 
and clauses[0].operator is operators.distinct_op:
f = func.count(1)
return compiler.visit_function(f, **kw)