python - Sqlalchemy - how to get raw sql from insert(), update() statements with binded params?

Question

Example:

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

Result of this is:

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

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)

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

Thanks for any help.

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.

Answer 1

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:

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

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

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

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 detailed (and hilarious) analysis of the various features is included as doctest.

The relevant code sections are:

• 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