如何从 SQLAlchemy 表达式中获取原始的、编译的 SQL 查询?

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

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

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

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

我不太清楚什么时候我的参数被混入以创建查询；在检查查询对象时，它们始终是一个空字典(尽管查询执行良好并且引擎在您打开回显日志时将其打印出来).

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

解决方案

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

引自博文，这是建议并为我工作.

<预><代码>>>>从 sqlalchemy.dialects 导入 postgresql>>>打印 str(q.statement.compile(dialect=postgresql.dialect()))

其中 q 定义为:

<预><代码>>>>q = DBSession.query(model.Name).distinct(model.Name.value) \.order_by(model.Name.value)

或者只是任何类型的session.query().

感谢 Nicolas Cadou 的回答！我希望它可以帮助到这里搜索的其他人.

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).

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

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

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.

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).

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()))

Where q is defined as:

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

Or just any kind of session.query().

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