python - 新建/覆盖 SQLAlchemy 运算符编译器输出

Question

编译 in_ 表达式的默认 SQLAlchemy 行为对于非常大的列表来说是病态的，我想为运算符创建一个自定义的、更快的编译器。如果解决方案是一个新的运算符(即:in_list_)或者它是否覆盖了 in_ 的默认编译器，对应用程序来说并不重要。但是，我还没有找到任何关于如何具体执行此操作的文档。

subclassing guidelines for compilation extension不要包含任何关于运算符的内容，这表明这不是开始的地方。 documentation on redefining and creating new operators专注于更改或创建新的运算符行为，但运算符的行为不是问题，编译器才是问题所在。

这是我正在尝试完成的一个非常无效的示例:

from sqlalchemy.types import TypeEngine

class in_list_(TypeEngine.Comparator):
  pass

@compiles(in_list_)
def in_list_impl(element, compiler, **kwargs):
  return "IN ('Now', 'I', 'can', 'inline', 'the', 'list')"

然后在表达式中:

select([mytable.c.x, mytable.c.y]).where(mytable.c.x.in_list_(long_list))

Answer 1

对非常 的大列表使用IN 确实是病态的，您可能会得到更好的服务using a temporary table和 IN 针对子查询或连接。但问题是“如何覆盖特定运算符的编译器输出”。对于 IN 和 NOT IN 等二元运算符，您需要重写的是 SQLAlchemy 如何处理编译 BinaryExpression:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.elements import BinaryExpression
from sqlalchemy.sql.operators import in_op, notin_op

def visit_in_op_binary(compiler, binary, operator, **kw):
    return "%s IN %s" % (
        compiler.process(binary.left, **kw),
        compiler.process(binary.right, **{**kw, "literal_binds": True}))

def visit_notin_op_binary(compiler, binary, operator, **kw):
    return "%s NOT IN %s" % (
        compiler.process(binary.left, **kw),
        compiler.process(binary.right, **{**kw, "literal_binds": True}))

@compiles(BinaryExpression)
def compile_binary(binary, compiler, override_operator=None, **kw):
    operator = override_operator or binary.operator

    if operator is in_op:
        return visit_in_op_binary(
            compiler, binary, operator, override_operator=override_operator,
            **kw)

    if operator is notin_op:
        return visit_notin_op_binary(
            compiler, binary, operator, override_operator=override_operator,
            **kw)

    return compiler.visit_binary(binary, override_operator=override_operator, **kw)

请注意，对于非常大的列表，简单地生成包含绑定(bind)参数的分组和子句列表的二进制表达式会花费大量时间，更不用说即使使用文字绑定(bind)也要编译所有这些，因此您可能不会观察到显着的性能提升.另一方面，许多实现对您可以在语句中使用的占位符/参数的数量有限制，因此内联绑定(bind)允许此类查询完全运行。

另一方面，如果您的列表确实符合您的实现设置的限制(Postgresql 似乎仅受可用 RAM 的限制)，您可能不需要任何编译器变通办法和足够新的 SQLAlchemy； use expanding bind parameters instead :

In [15]: %%time
    ...: session.query(Foo).\
    ...:     filter(Foo.data.in_(range(250000))).\
    ...:     all()
    ...: 
CPU times: user 5.09 s, sys: 91.9 ms, total: 5.18 s
Wall time: 5.18 s
Out[15]: []

In [16]: %%time
    ...: session.query(Foo).\
    ...:     filter(Foo.data.in_(bindparam('xs', range(250000), expanding=True))).\
    ...:     all()
    ...: 
CPU times: user 310 ms, sys: 8.05 ms, total: 318 ms
Wall time: 317 ms
Out[16]: []

如评论中所述，在 1.4 版中，扩展的 bindparam 将支持开箱即用的文字执行:

In [4]: session.query(Foo).\
   ...:     filter(Foo.data.in_(
   ...:         bindparam('xs', range(10), expanding=True, literal_execute=True))).\
   ...:     all()
2019-09-07 20:35:04,560 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-09-07 20:35:04,561 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.data AS foo_data 
FROM foo 
WHERE foo.data IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
2019-09-07 20:35:04,561 INFO sqlalchemy.engine.base.Engine ()
Out[4]: []