如何从SQLAlchemy表达式中获取原始的，已编译的SQL查询? [英] How do I get a raw, compiled SQL query from a SQLAlchemy expression?

问题描述

我有一个SQLAlchemy查询对象，想获取已编译的SQL语句的文本，并绑定了所有参数(例如，没有%s或其他等待由语句编译器或MySQLdb方言引擎绑定的变量，等等)

I have a SQLAlchemy query object and want to get the text of the compiled SQL statement, with all its parameters bound (e.g. no %s or other variables waiting to be bound by the statement compiler or MySQLdb dialect engine, etc).

在查询中调用str()会显示以下内容:

Calling str() on the query reveals something like this:

SELECT id WHERE date_added <= %s AND date_added >= %s ORDER BY count DESC

我尝试在query._params中查找，但这是一个空字典.我使用此sqlalchemy.ext.compiler.compiles装饰器示例编写了自己的编译器，但是甚至那里的语句仍然有%s我想要的数据.

I've tried looking in query._params but it's an empty dict. I wrote my own compiler using this example of the sqlalchemy.ext.compiler.compiles decorator but even the statement there still has %s where I want data.

我无法弄清楚何时混入参数来创建查询.在检查查询对象时，它们始终是一个空字典(尽管查询执行得很好，并且当您打开echo记录时引擎会打印出来).

I can't quite figure out when my parameters get mixed in to create the query; when examining the query object they're always an empty dictionary (though the query executes fine and the engine prints it out when you turn echo logging on).

我开始收到消息，SQLAlchemy不想让我知道底层查询，因为它破坏了表达式API接口的所有不同DB-API的一般性质.我不在乎查询是否在我发现查询之前就已经执行了；我只想知道！

I'm starting to get the message that SQLAlchemy doesn't want me to know the underlying query, as it breaks the general nature of the expression API's interface all the different DB-APIs. I don't mind if the query gets executed before I found out what it was; I just want to know!

推荐答案

此博客提供了更新的答案.

This blog provides an updated answer.

这是博客文章的引文，对我来说是建议和工作.

Quoting from the blog post, this is suggested and worked for me.

>>> from sqlalchemy.dialects import postgresql
>>> print str(q.statement.compile(dialect=postgresql.dialect()))

其中q定义为:

>>> q = DBSession.query(model.Name).distinct(model.Name.value) \
             .order_by(model.Name.value)

或者只是任何一种session.query().

Or just any kind of session.query().

感谢尼古拉斯·卡杜(Nicolas Cadou)的答案！希望对其他在这里搜索的人有所帮助.

Thanks to Nicolas Cadou for the answer! I hope it helps others who come searching here.

这篇关于如何从SQLAlchemy表达式中获取原始的，已编译的SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！