Sqlalchemy - 如何从带有绑定参数的 insert()、update() 语句中获取原始 sql? [英] Sqlalchemy - how to get raw sql from insert(), update() statements with binded params?

问题描述

示例:

from sqlalchemy.dialects import mysql
from sqlalchemy import Integer, Column, update, insert
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = "test"

    a = Column(Integer, primary_key=True)
    b = Column(Integer)


update_stmt = update(Test).where(Test.a == 1).values(b=2)
print update_stmt.compile(dialect=mysql.dialect(), compile_kwargs=  {"literal_binds": True})

insert_stmt = insert(Test).values(a=1, b=1)
print insert_stmt.compile(dialect=mysql.dialect())

结果是:

UPDATE test SET b=%s WHERE test.a = %s
INSERT INTO test (a, b) VALUES (%s, %s)

问题是如何让sqlalchemy像这样生成smth:

The question is how to make sqlalchemy generate smth like this:

UPDATE test SET b=2 WHERE test.a = 1
INSERT INTO test (a, b) VALUES (1, 1)

对于select，compile_kwargs= {"literal_binds": True} 解决了这个问题，但是对于update 不起作用，<代码>插入.

For select, compile_kwargs= {"literal_binds": True} solves the issue, but it doesn't work for update, insert.

感谢您的帮助.

附言我需要从 orm 构建原始 sql 查询，因此欢迎任何其他 orms 的建议，这些建议可以轻松生成原始 sql.

P.S. I need to build raw sql queries from orm, so any suggestions of any other orms, that have easy way to generate raw sql, are welcome.

推荐答案

使用 compile_kwargs 的解决方案——正如在别处宣传的那样——只能部分工作，即对于简单的数据类型，如整数或字符串(如SQLAlchemy v1.1.6).

The solution using compile_kwargs -- as advertised elsewhere -- works only partially, i.e. for simple data types like Integer or String (as of SQLAlchemy v1.1.6).

+-------------+----------------+--------+---------------+----------+
| SQA version | compile_kwargs | SELECT | INSERT/UPDATE | datetime |
+=============+================+========+===============+==========+
| 0.7.9       |       --       |    --  |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 0.9.4       |       √        |   1/2  |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.0.11      |       √        |    √   |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.0.13      |       √        |    √   |       1/2     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.1.6       |       √        |    √   |       1/2     |    --    |
+-------------+----------------+--------+---------------+----------+

根据要求和限制，以下解决方案可以胜任:

Depending on requirements and restrictions, the following solutions will do the job:

• 如果您可以使用 SQLAlchemy v1.0.13 或更高版本，中提供了最简单的解决方案这个答案.LiteralDialect 类仍然需要提供正确的数据类型引用，如日期时间.虽然不完整，但添加缺失的数据类型非常简单.不幸的是，您必须将 None 修复为 null() 以进行插入/更新.

• If you can use SQLAlchemy v1.0.13 or above, the simplest solution is provided in this answer. The LiteralDialect class presented there is still needed to provide correct quoting of data types like datetime. Although it is not complete, adding missing data types is very straightforward. And unfortunately you have to fix None to null() for INSERT/UPDATE.

如果您可以使用 SQLAlchemy v1.0.11 并且只需要 SELECT 语句(不是 INSERT/UPDATE)，您也可以使用上述答案中的解决方案.SQLAlchemy v1.0.11 随 ubuntu 16.04 xenial 一起分发，这是当前的 LTS 版本(截至 2017 年 3 月).

If you can use SQLAlchemy v1.0.11 and need only SELECT statements (not INSERT/UPDATE), you can also use the solution from the above answer. SQLAlchemy v1.0.11 is distributed with ubuntu 16.04 xenial, the current LTS version (as of Mar 2017).

如果您坚持使用某些较旧版本的 SQLAlchemy，则没有解决此问题的简便方法.

If you are stuck with some older version of SQLAlchemy, there is no sweet and simple approach to the problem.

所以我想出了一些支持 SQL 编译的代码查询 (SELECT) 以及 INSERT 和 UPDATE 语句.该代码适用于 SQLAlchemy v0.7.9 - v1.1.6 和 Python2/Python3.

So I came up with some code supporting SQL compilation of queries (SELECT) as well as INSERT and UPDATE statements. The code works with SQLAlchemy v0.7.9 - v1.1.6 and Python2/Python3.

A 对各种功能的详细(有趣)分析 包含在 doctest 中.

A detailed (and hilarious) analysis of the various features is included as doctest.

相关的代码部分是:

• 函数 sql_statement
• class LiteralDialect
• 函数 fix_null_params、fix_null_values, null_if_none
• 变量 COMPILE_KWARGS
• 作为奖励table_sql_dump

这篇关于Sqlalchemy - 如何从带有绑定参数的 insert()、update() 语句中获取原始 sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！