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生成如下内容:

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，insert.

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

感谢您的帮助.

P.S.我需要从orm构建原始sql查询，因此，欢迎对其他任何具有易于生成原始sql的orm的建议.

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 的解决方案(如在其他地方宣传的那样)仅部分起作用，即，对于简单数据类型，例如Integer或String(如的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 类来提供对数据类型(如datetime)的正确引用.尽管还不完整，但是添加丢失的数据类型非常简单.不幸的是，您必须将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与当前的LTS版本(截至2017年3月)的ubuntu 16.04 xenial一起分发.

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.

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

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

相关代码部分为:

• 功能 sql_statement
• class LiteralDialect
• 功能 fix_null_params ， fix_null_values ， COMPILE_KWARGS
• 并作为奖励 table_sql_dump

• function sql_statement
• class LiteralDialect
• functions fix_null_params, fix_null_values, null_if_none
• variable COMPILE_KWARGS
• and as a bonus table_sql_dump

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