检索最终的 sql 查询语句(用值代替任何“?") [英] Retrieving ultimate sql query sentence (with the values in place of any '?')

问题描述

由于将有缺陷的 sql 查询直接粘贴到诸如 phpmyadmin 之类的数据库管理工具中以便对其进行处理直到返回预期结果可能是有效的，有什么方法可以以准备执行的形式检索 Sqlalchemy Core 据称传递给 MySql 数据库的最终 sql 语句?

Since it may be efficient to paste a flawed sql query directly into a database administration tool such as phpmyadmin in order to work on it until it returns the expected result, Is there any way to retrieve the ultimate sql sentence Sqlalchemy Core supposedly passes to the MySql database, in a ready-to-execute shape ?

推荐答案

这通常意味着您希望内联呈现绑定参数.对此自动支持有限(从 SQLA 0.9 开始，这将起作用):

This typically means that you want the bound parameters to be rendered inline. There is limited support for this automatically (as of SQLA 0.9 this will work):

from sqlalchemy.sql import table, column, select

t = table('x', column('a'), column('b'))

stmt = select([t.c.a, t.c.b]).where(t.c.a > 5).where(t.c.b == 10)

print(stmt.compile(compile_kwargs={"literal_binds": True}))

此外，您可能希望查询是特定于 MySQL 的，因此如果您周围已经有一个引擎，您也可以将其传入:

also you'd probably want the query to be MySQL specific, so if you already have an engine lying around you can pass that in too:

from sqlalchemy import create_engine
engine = create_engine("mysql://")

print(stmt.compile(engine, compile_kwargs={"literal_binds": True}))

然后打印:

SELECT x.a, x.b 
FROM x 
WHERE x.a > 5 AND x.b = 10

现在，如果您在参数中有更详细的值，例如日期，SQLAlchemy 可能会抛出错误，它只有非常有限数量的类型的文字绑定"渲染器.一种绕过该系统并为您提供将这些参数转换为字符串的直接方法的方法是对语句对象进行搜索和替换"，用文字字符串替换绑定参数:

now, if you have more elaborate values in the parameters, like dates, SQLAlchemy might throw an error, it only has "literal binds" renderers for a very limited number of types. An approach that bypasses that system instead and gives you a pretty direct shot at turning those parameters into strings is then do to a "search and replace" on the statement object, replacing the bound parameters with literal strings:

from sqlalchemy.sql import visitors, literal_column
from sqlalchemy.sql.expression import BindParameter

def _replace(arg):
    if isinstance(arg, BindParameter):
        return literal_column(
                repr(arg.effective_value)  # <- do any fancier conversion here
            )
stmt = visitors.replacement_traverse(stmt, {}, _replace)

一旦你这样做了，你就可以打印出来:

once you do that you can just print it:

print(stmt)

或 MySQL 版本:

or the MySQL version:

print(stmt.compile(engine))

这篇关于检索最终的 sql 查询语句(用值代替任何“?")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！