这就是我的方法

# query is instance of: from sqlalchemy import select
def raw_query(query):
    q = str(query.compile())
    p = query.compile().params
    for k in p.keys():
        v = p.get(k)
        if isinstance(v, (int, float, complex)):
            q = q.replace(f":{k}", f"{v}")
        else:
            q = q.replace(f":{k}", f"'{v}'")
    print(q)

如何使用:

from sqlalchemy import select

select_query = select([
    any_model_table.c["id_account"],
    any_model_table.c["id_provider"],
    any_model_table.c["id_service"],
    func.sum(any_model_table.c["items"]).label("items"),
    # @eaf
    func.date_format(func.now(), "%Y-%m-%d").label("some_date"),
    func.date_format(func.now(), "%Y").label("as_year"),
    func.date_format(func.now(), "%m").label("as_month"),
    func.date_format(func.now(), "%d").label("as_day"),
]).group_by(
    any_model_table.c.id_account,
    any_model_table.c.id_provider,
    any_model_table.c.id_service
).where(
    any_model_table.c.id == 5

).where(
    func.date_format(any_model_table.c.dt, "%Y-%m-%d") == datetime.utcnow().strftime('%Y-%m-%d')
)

raw_query(select_query)

只是一个简单的彩色示例与ORM的查询和pydings。

import sqlparse
from pygments import highlight
from pygments.formatters.terminal import TerminalFormatter
from pygments.lexers import SqlLexer
from sqlalchemy import create_engine
from sqlalchemy.orm import Query

engine = create_engine("sqlite+pysqlite:///db.sqlite", echo=True, future=True)

def format_sql(query: Query):
    compiled = query.statement.compile(
         engine, compile_kwargs={"literal_binds": True})
    parsed = sqlparse.format(str(compiled), reindent=True, keyword_case='upper')
    print(highlight(parsed, SqlLexer(), TerminalFormatter()))

或者没有sqlparse的版本(没有sqlparse输出的新行更少)

def format_sql(query: Query):
    compiled = query.statement.compile(
        engine, compile_kwargs={"literal_binds": True})
    print(highlight(str(compiled), SqlLexer(), TerminalFormatter()))

这段代码是基于@bukzor现有的精彩答案。我刚刚为datetime添加了自定义渲染。datetime类型到Oracle的TO_DATE()。

请随时更新代码，以适应您的数据库:

import decimal
import datetime

def printquery(statement, bind=None):
    """
    print a query, with values filled in
    for debugging purposes *only*
    for security, you should always separate queries from their values
    please also note that this function is quite slow
    """
    import sqlalchemy.orm
    if isinstance(statement, sqlalchemy.orm.Query):
        if bind is None:
            bind = statement.session.get_bind(
                    statement._mapper_zero_or_none()
            )
        statement = statement.statement
    elif bind is None:
        bind = statement.bind 

    dialect = bind.dialect
    compiler = statement._compiler(dialect)
    class LiteralCompiler(compiler.__class__):
        def visit_bindparam(
                self, bindparam, within_columns_clause=False, 
                literal_binds=False, **kwargs
        ):
            return super(LiteralCompiler, self).render_literal_bindparam(
                    bindparam, within_columns_clause=within_columns_clause,
                    literal_binds=literal_binds, **kwargs
            )
        def render_literal_value(self, value, type_):
            """Render the value of a bind parameter as a quoted literal.

            This is used for statement sections that do not accept bind paramters
            on the target driver/database.

            This should be implemented by subclasses using the quoting services
            of the DBAPI.

            """
            if isinstance(value, basestring):
                value = value.replace("'", "''")
                return "'%s'" % value
            elif value is None:
                return "NULL"
            elif isinstance(value, (float, int, long)):
                return repr(value)
            elif isinstance(value, decimal.Decimal):
                return str(value)
            elif isinstance(value, datetime.datetime):
                return "TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')" % value.strftime("%Y-%m-%d %H:%M:%S")

            else:
                raise NotImplementedError(
                            "Don't know how to literal-quote value %r" % value)            

    compiler = LiteralCompiler(dialect, statement)
    print compiler.process(statement)

假定只有在调试时才有意义，可以使用echo=True启动SQLAlchemy，以记录所有SQL查询。例如:

engine = create_engine(
    "mysql://scott:tiger@hostname/dbname",
    encoding="latin1",
    echo=True,
)

这也可以修改为一个单一的请求:

echo=False -如果为True，引擎将记录所有语句以及它们参数列表的repr()到引擎记录器，默认为sys.stdout。Engine的echo属性可以在任何时候修改以打开和关闭日志。如果设置为字符串"debug"，结果行也将打印到标准输出。这个标志最终控制一个Python日志记录器;有关如何直接配置日志的信息，请参阅配置日志。 来源:SQLAlchemy Engine Configuration

如果与Flask一起使用，您可以简单地设置

app.config["SQLALCHEMY_ECHO"] = True

得到相同的行为。

我想指出，上面给出的解决方案并不“只适用于”非平凡的查询。我遇到的一个问题是更复杂的类型，比如pgsql数组会导致问题。我确实找到了一个解决方案，对我来说，只是工作，甚至与pgsql数组:

借鉴: https://gist.github.com/gsakkis/4572159

链接的代码似乎基于SQLAlchemy的旧版本。您将得到一个错误，提示属性_mapper_zero_or_none不存在。下面是一个更新的版本，它可以与更新的版本一起工作，您只需将_mapper_zero_or_none替换为bind。此外，它还支持pgsql数组:

# adapted from:
# https://gist.github.com/gsakkis/4572159
from datetime import date, timedelta
from datetime import datetime

from sqlalchemy.orm import Query


try:
    basestring
except NameError:
    basestring = str


def render_query(statement, dialect=None):
    """
    Generate an SQL expression string with bound parameters rendered inline
    for the given SQLAlchemy statement.
    WARNING: This method of escaping is insecure, incomplete, and for debugging
    purposes only. Executing SQL statements with inline-rendered user values is
    extremely insecure.
    Based on http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query
    """
    if isinstance(statement, Query):
        if dialect is None:
            dialect = statement.session.bind.dialect
        statement = statement.statement
    elif dialect is None:
        dialect = statement.bind.dialect

    class LiteralCompiler(dialect.statement_compiler):

        def visit_bindparam(self, bindparam, within_columns_clause=False,
                            literal_binds=False, **kwargs):
            return self.render_literal_value(bindparam.value, bindparam.type)

        def render_array_value(self, val, item_type):
            if isinstance(val, list):
                return "{%s}" % ",".join([self.render_array_value(x, item_type) for x in val])
            return self.render_literal_value(val, item_type)

        def render_literal_value(self, value, type_):
            if isinstance(value, long):
                return str(value)
            elif isinstance(value, (basestring, date, datetime, timedelta)):
                return "'%s'" % str(value).replace("'", "''")
            elif isinstance(value, list):
                return "'{%s}'" % (",".join([self.render_array_value(x, type_.item_type) for x in value]))
            return super(LiteralCompiler, self).render_literal_value(value, type_)

    return LiteralCompiler(dialect, statement).process(statement)

测试了两层嵌套数组。

使用Python日志代替echo=True标记记录SQL查询: 导入日志 logging.basicConfig () logging.getLogger (sqlalchemy.engine) .setLevel (logging.INFO)

根据文档。